kbios
asked on
ADO VB6 Error 3021; Even with 1 record in recordset?
VB6 and Access using ADO to manipulate records. In a select statement I am tyring to retrieve 1 record. BOF and EOF = true and therefore I get error 3021. However if I insert a message box the record pointer works and no 3021 error is generated. I don't want the msgbox code in the program. How can I get the 3021 error to go away?
can you show more details like the table design you query and the test code to reproduce the problem=
ASKER
dbWhereCondition = "SELECT * FROM order_line WHERE order_num = '" & ordnum & "' AND line_num =" & ctrvar
Dim oldb As ADODB.Connection
Set oldbRecordset = New ADODB.Recordset
Set oldb = New Connection
oldb.Open "PROVIDER=Microsoft.Jet.OL EDB.4.0;Da ta Source=N:\databases\gwi.md b;Jet OLEDB:Database Password=xxxxx;"
oldbRecordset.Open dbWhereCondition, oldb, _
adOpenDynamic, adLockOptimistic, adCmdTableDirect
'reccountvar = 0
'Do Until oldbRecordset.EOF
' reccountvar = reccountvar + 1
'Loop
'If reccountvar = 0 Then
' MsgBox "FATAL ERROR: CANNOT FIND THE LINE ITEM IN THE ORDER LINE TABLE"
' Exit Sub
'End If
IF I REFERENCE oldbRecordset AT THIS POINT I GET THE 3021 ERROR.
Dim oldb As ADODB.Connection
Set oldbRecordset = New ADODB.Recordset
Set oldb = New Connection
oldb.Open "PROVIDER=Microsoft.Jet.OL
oldbRecordset.Open dbWhereCondition, oldb, _
adOpenDynamic, adLockOptimistic, adCmdTableDirect
'reccountvar = 0
'Do Until oldbRecordset.EOF
' reccountvar = reccountvar + 1
'Loop
'If reccountvar = 0 Then
' MsgBox "FATAL ERROR: CANNOT FIND THE LINE ITEM IN THE ORDER LINE TABLE"
' Exit Sub
'End If
IF I REFERENCE oldbRecordset AT THIS POINT I GET THE 3021 ERROR.
remove the parameter adCmdTableDirect. as you run a query, that one is wrong. you might use adCmdText instead, which is the default.
now, what exactly do you reference?
now, what exactly do you reference?
dbWhereCondition = "SELECT * FROM order_line WHERE order_num = '" & ordnum & "' AND line_num =" & ctrvar
Dim oldb As ADODB.Connection
Set oldbRecordset = New ADODB.Recordset
Set oldb = New Connection
oldb.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=N:\databases\gwi.mdb;Jet OLEDB:Database Password=xxxxx;"
oldbRecordset.Open dbWhereCondition, oldb, _
adOpenDynamic, adLockOptimistic
if oldbRecordset.eof and oldbRecordset.bof then
'recordset is empty
else
while not oldbRecordset.eof
'you should be able to access the oldbRecordset.fields(...) collection here without error
oldbRecordset.movenext
wend
end if
oldbRecordset.close
ASKER
I modified the code per the posting but I'm still getting the same error. BOF and EOF are TRUE. BUT if I insert a msgbox, and then continue with the process I can access the 1 record in the oldbRecordset.
dbWhereCondition = "SELECT * FROM order_line WHERE order_num = '" & ordnum & "' AND line_num =" & ctrvar
Dim oldb As ADODB.Connection
Set oldbRecordset = New ADODB.Recordset
Set oldb = New Connection
oldb.Open "PROVIDER=Microsoft.Jet.OL EDB.4.0;Da ta Source=N:\databases\gwi.md b;Jet OLEDB:Database Password=xxxxx;"
oldbRecordset.Open dbWhereCondition, oldb, _
adOpenDynamic, adLockOptimistic
If oldbRecordset.EOF And oldbRecordset.BOF Then
'recordset is empty
Else
While Not oldbRecordset.EOF
'you should be able to access the oldbRecordset.fields(...) collection here without error
oldbRecordset.MoveNext
Wend
End If
'reccountvar = 0
'Do Until oldbRecordset.EOF
' reccountvar = reccountvar + 1
'Loop
'If reccountvar = 0 Then
' MsgBox "FATAL ERROR: CANNOT FIND THE LINE ITEM IN THE ORDER LINE TABLE"
' Exit Sub
'End If
dataline1 = ""
dataline2 = ""
ditemdisc = ""
qandp = ""
ditemqty = ""
ditemprice = ""
ditemcat = oldbRecordset!item_categor y
***** ERROR 3021 when I try to reference oldbRecordset *****
dbWhereCondition = "SELECT * FROM order_line WHERE order_num = '" & ordnum & "' AND line_num =" & ctrvar
Dim oldb As ADODB.Connection
Set oldbRecordset = New ADODB.Recordset
Set oldb = New Connection
oldb.Open "PROVIDER=Microsoft.Jet.OL
oldbRecordset.Open dbWhereCondition, oldb, _
adOpenDynamic, adLockOptimistic
If oldbRecordset.EOF And oldbRecordset.BOF Then
'recordset is empty
Else
While Not oldbRecordset.EOF
'you should be able to access the oldbRecordset.fields(...) collection here without error
oldbRecordset.MoveNext
Wend
End If
'reccountvar = 0
'Do Until oldbRecordset.EOF
' reccountvar = reccountvar + 1
'Loop
'If reccountvar = 0 Then
' MsgBox "FATAL ERROR: CANNOT FIND THE LINE ITEM IN THE ORDER LINE TABLE"
' Exit Sub
'End If
dataline1 = ""
dataline2 = ""
ditemdisc = ""
qandp = ""
ditemqty = ""
ditemprice = ""
ditemcat = oldbRecordset!item_categor
***** ERROR 3021 when I try to reference oldbRecordset *****
can you upload the ms access db?
ASKER
The recordset is appearing empty (e.g. BOF and EOF are the same) BUT, if I simply insert a msgbox to display ANYTHING, the recordset works. Remember there is only 1 record that will be retreived. Once I find the record and process the fields I will not need to do .MoveNext or any other navigation.
ASKER
database file sent
gwi.mdb
gwi.mdb
coool.... without the password will be difficult :)
ASKER
that's why i put the password in the description line of gwi.mdb :) $kbios2
silly me :)
ok, I tried this:
dbWhereCondition = "SELECT * FROM order_line WHERE order_num = 'ST01010000225' AND line_num = 1 "
and the code worked immediately (eof and bof returned false).
please tell me with what values you have a problem.
ok, I tried this:
dbWhereCondition = "SELECT * FROM order_line WHERE order_num = 'ST01010000225' AND line_num = 1 "
and the code worked immediately (eof and bof returned false).
please tell me with what values you have a problem.
ASKER
When I run the program eof and bof return true (they shouldn't). But if I simply use the select statement to create the recordset I get the 3021 error when I attempt to access the recordset. When I used your logic: While Not oldbRecordset.EOF the recordset comes back with an EOF and BOF as true. I can't get to the single record.
well, as posted above, I tried with a specific set of variables, and it worked perfectly.
so, again, what are the values for order_num and line_num that you are using? ...
so, again, what are the values for order_num and line_num that you are using? ...
ASKER
order_num and line_num are generated during the program. any order_num value that you found in the database was an order_num that was used and generated the error. although there are multiple lines per order the error occurs on the first line. so I too would have used ST01010000225 as an order_num and 1 as the line_num.
perhaps there is a reference or component that I'm missing that explains the reason yours works and mine doesn't?
perhaps there is a reference or component that I'm missing that explains the reason yours works and mine doesn't?
ASKER
one my end the code works when i add a msgbox (?) so the order_num and line_num data elements can be found in the database. it's just that when the code is ran without the msgbox the error occurs. what could the msgbox be doing that would allow it to work? a delay perhaps ? I'm going to add some delay process to see if this works. i'll be back.
ASKER
i fixed it!!! i added a 500millisecond delay and the code works great! I started with a delay of 2000milliseconds the program ran fine, i reduced to 1000 everything is fine, i reduced to 500 still ok, i changed to 100milliseconds and the 3021 error occured. on my end i have to wait before processing.
i set back to 500 and all is well. can you shed any light as to WHY this may be happening? is there another (better) way to accomplish what i'm trying to do without having to 'sleep' for 500milliseconds?
i set back to 500 and all is well. can you shed any light as to WHY this may be happening? is there another (better) way to accomplish what i'm trying to do without having to 'sleep' for 500milliseconds?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
not immediately before the select but pretty close. i create a tmp_line table to contain the line item data. once the order is complete i go through each line of the tmp_line and create the order_line table. the creation of the order_line table is done just prior to the actual receipt printing (the above code snipets is part of the receipt printing). so the creation of the row in the table just may need a little more time before I reference it.
I'm closing this question. Thanks for your help and expertise.
I'm closing this question. Thanks for your help and expertise.
ASKER
Thanks again. I enjoyed the exchange of ideas back and forth. I appreciate your help.
Use the DoEvents statement ALWAYS AFTER the ADODB objects.
oldb.Open "PROVIDER=Microsoft.Jet.OL EDB.4.0;Da ta Source=N:\databases\gwi.md b;Jet OLEDB:Database Password=xxxxx;"
DoEvents
oldbRecordset.Open dbWhereCondition, oldb, _
adOpenDynamic, adLockOptimistic, adCmdTableDirect
DoEvents
oldb.Open "PROVIDER=Microsoft.Jet.OL
DoEvents
oldbRecordset.Open dbWhereCondition, oldb, _
adOpenDynamic, adLockOptimistic, adCmdTableDirect
DoEvents