Run time error 3021 - EOF or BOF is true or the current record has been deleted

Question

0.00/5 (No votes)

See more:

I am trying to retrieve data from MS Access in vb6 and its showing error as 3021
I am converting my data into hex in database and wanted to retrieve the table according to the name but when the name matches i am getting run time 3021 error

pls help me!!
Thank You

What I have tried:

Private Sub view_Click()

Dim a1 As String
a1 = rs!nameoftest
rs.Close
rs.Open "Select * from Table1 where NameofTest='" + Label26.Caption + "'", con, adOpenDynamic, adLockPessimistic
str = a1
For x = 1 To Len(str) Step 2
str1 = str1 & Chr(Val("&H" & Mid$(str, x, 2)))
Next

If Label26.Caption = str1 Then
display
Else
MsgBox "Record not Found..!!", vbInformation
Label26.Caption = ""
End If
End Sub

Posted 16-Feb-18 1:40am

Member 13396059

Updated 16-Feb-18 2:44am

Add a Solution

Comments

Richard Deeming 16-Feb-18 13:33pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2018-02-16T02:33:00

Solution 1

Your code makes no sense: You are assigning a1 from rs, close the recordset, open it again with a new query, but never use the data from that query.

I would expect something like

VB

'rs.Close
rs.Open "Select * from Table1 where NameofTest='" + Label26.Caption + "'", con, adOpenDynamic, adLockPessimistic
If Not rs.EOF
    a1 = rs.nameoftest
    ' ...
End If
' ...
' EDIT: Close here
rs.Close

If NameofTest is not unique, you have to use a loop instead:

VB

While Not rs.EOF
    a1 = rs.nameoftest
    ' ...
    rs.MoveNext
End While

[EDIT]
Why do you hold the recordset opened?
Close it when not using it anymore. Because the code is called upon a user event (click) you can't use it in other functions where you don't know the state.
[EDIT]

Posted 16-Feb-18 2:33am

Jochen Arndt

Updated 16-Feb-18 2:47am

v2

Comments

Member 13396059 17-Feb-18 4:25am

i have modified my program but am still getting the error

Jochen Arndt 17-Feb-18 5:45am

You should not get the error when using my code example (accessing rs only after checking for not EOF). However, you may also check for not BOF.

Member 13396059 19-Feb-18 2:07am

i have modified my code as u said

rs.Open "Select * from Table1 where NameofTest='" + Label26.Caption + "'", con, adOpenDynamic, adLockPessimistic
While Not rs.EOF
a1 = rs!nameoftest
Label26.Caption = a1
str = a1
For x = 1 To Len(str) Step 2
str1 = str1 & Chr(Val("&H" & Mid$(str, x, 2)))
Next
Label26.Caption = str1
display
rs.MoveNext
Wend

but still i'm unable to retrieve the data

Jochen Arndt 19-Feb-18 2:54am

Do you get an error or just no data?
If you get no data, check your query (and if your table contains a recordset where NameOfTest has the value of your label).

Member 13396059 19-Feb-18 5:52am

Dim a1, sqlstr As String
con.Open "PROVIDER= Microsoft.Jet.OLEDB.4.0;data source=C:\Users\Raghava\Desktop\Database\artoss.mdb;"
sqlstr = "Select * from Table1"
rs.Open sqlstr, con, adOpenKeyset
a1 = rs!NameofTest
str = a1
For x = 1 To Len(str) Step 2
str1 = str1 & Chr(Val("&H" & Mid$(str, x, 2)))
Next
If str1 = Label26.Caption Then
While Not rs.EOF
display
rs.MoveNext
x = x + 1
Wend
End If

if i write like this i'm able to retrieve the data but its not checking entire database it is checking only first row of the db file
Is there any problem in my code??

Jochen Arndt 19-Feb-18 6:24am

It is processing all rows in the
While Not rs.EOF - Wend
loop. You have to put additional code into that loop to show the values.

I don't know your database design, so I can't help much. But it looks like you are passing wrong values to your WHERE clause so that no resuts are returned.

Member 13396059 19-Feb-18 6:03am

in database the data was converted into hex