3021 error

weide
04-15-2004, 09:46 AM
Here is my code

Dim dn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()

dn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\testDatabase.mdb;Persist Security Info=False"
rs.Open "select * from hehe", dn, adOpenDynamic, adLockOptimistic

txtID = rs.fields(3).Value

End Sub

Private Sub Command1_Click()

If txtName = "" Then
MsgBox "must add name"
Else
rs.AddNew
rs.fields(0).Value = txtName.Text
rs.fields(1).Value = txtNRIC.Text
rs.fields(2).Value = txtphonenumber.Text
rs.fields(3).Value = txtID.Text
MsgBox "record added"
End If

txtName = ""
txtNRIC = ""
txtphonenumber = ""
txtID = ""

End Sub

----------------------------
Database Name: testDatabase.mdb
Table Name: hehe
Fields:
Name (text)
NRIC (text)
Phonenumber (number)
ID (AutoNumber)

I want to autoassign an ID in txtID.. so i am trying to retrieve the ID from the Database.. however.. when i add in this line "txtID = rs.fields(3).Value", it does not work.. where did i go wrong?

LaVolpe
04-15-2004, 09:52 AM
Suggestion: in the future give the description of the error code so those of us that don't live & breath DBs know what it is w/o looking it up :) Either EOF or BOF occurred?
First check to ensure .Recordcount is not zero. Then try using the .MoveFirst immediately after you make that check & before you access field data. Hopefully that does it for you

weide
04-16-2004, 12:58 PM
ok.. instead of using dim.. i put the connection string to database path and recordsource for putting the table name.. after changing data sources of the textboxes to Adodc1.. and DataField to the respective fields..

currently there is no records in the database..

and here is the code

Private Sub cmdAdd_Click()

Adodc1.Recordset.Update
MsgBox "Record Saved"
cmdclear_Click

End Sub

Private Sub cmdclear_Click()

txtName = ""
txtNRIC = ""
txtphonenumber = ""
txtID = ""

End Sub

The error occurs when there is no record in the database.. i solve the error by adding record in database manually and then enter the next record... but however, i do not want any record at start.. and add the first record through vb..



as suggested by LaVolpe..

the error number is 3021
the description is "Either EOF or BOF is true, or the current record has been deleted. Requested operation requires a current record.

This is the first problem I need help with..

second problem is.. i want to have autonumber to be retrieved from the database and display in the textbox..

thanks for any help..

LaVolpe
04-16-2004, 01:12 PM
Problem 1. Use the recordset.AddNew function & then supply the appropriate field values, with the exception of the autonumber of course. Then call .Update

Problem 2: After the .Update command requery the autonumber field to return the autonumber. It won't exist until after the record has been updated.

weide
04-16-2004, 01:17 PM
hmm.. the problem 1 and problem 2 are kind of linked... when i want to add records.. i will need to load the addform.. so the autonumber will display right there..

and do you mean it is compulsory to have a record added first?

LaVolpe
04-16-2004, 01:20 PM
you can't have an autonumber displayed for a record that doesn't exist in your database. Rework your code a little:
rs.AddNew
rs.fields(0).Value = txtName.Text
rs.fields(1).Value = txtNRIC.Text
rs.fields(2).Value = txtphonenumber.Text
rs.Update <<<<<<<<<<<<<<<<<<<<< added line here
txtID.Text=rs.fields(3).Value <<<<<<< modified
MsgBox "record added"

weide
04-16-2004, 01:24 PM
ok thanks..

LaVolpe
04-16-2004, 01:54 PM
and do you mean it is compulsory to have a record added first?

Nope, not at all. You should be able to add the 1st record to an empty table via code without priming the table with a dummy record.

weide
04-16-2004, 02:00 PM
thanks LaVolpe.. it works.. but i just wondering is it possible lets say i have ID 1 and 3.. 2 is deleted.. is it possible for it to autoassign to the missing number?

weide
04-16-2004, 02:27 PM
and sorry for being PITA.. been asking too much as i do not know database well.. i want to retrieve record.. i have to enter Customer ID (first, it will have to check if CustomerID is found in the Database, if not found, it will say Record not found, if found, it will say Record found and display Name, NRIC and phonenumber..

Private Sub cmdretrieve_Click()

If txtID = "" Then
MsgBox "Please enter CustomerID in number"
ElseIf IsNumeric(txtID) = False Then
MsgBox "Please enter CustomerID in number"
###

End If

End Sub

what should i put in between?

LaVolpe
04-16-2004, 02:39 PM
thanks LaVolpe.. it works.. but i just wondering is it possible lets say i have ID 1 and 3.. 2 is deleted.. is it possible for it to autoassign to the missing number?
Nope, not using autonumbers.

LaVolpe
04-16-2004, 02:45 PM
i want to retrieve record.. i have to enter Customer ID
There are 2 ways.
1. Create a query looking for the recordset " WHERE ([Table].[Field]=' & textvalue & " ')" This will return one record.
2. If you already have a recordset that contained something like SELECT *, then all you records are accessible & you can use code like:
.MoveFirst
.Find "[AutoNumberField]=" & textvalue
If .EOF = True Then ..... record not found
Else recordset is currently on the found record.
Personally. You wouldn't ask people to search by an autonumber, you would ask them to search by something commonplace, like names, unless of course, the autonumber is a common thing users would know.

weide
04-17-2004, 09:57 AM
sorry, i new to database, so i don't know about it well.. one of the project requirements is to retrieve record base on using

1. CustomerID(autonumber)
2. CD ID

where do i create this query command? in MSSQL? if in vb.. what is the command line for it?

as for LaVolpe number 2.. i still don't understand what he means.. sorry for a little low intelligent..

LaVolpe
04-17-2004, 10:40 AM
Please don't take offense, but if you are going to be writing code to communicate with a database, you should learn some basic SQL (the database language).
See http://www.visualbasicforum.com/showthread.php?t=39722

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum