Strange Error with a Recordset

Question

I'm trying to search for a string in an Access database via SQL.

It works fine with method 1 but method 2 is giving me a headache.

The upcoming error is

ADODB.Recordset Fehler "800a0bcd' Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I'm actually trying to avoid this error by If KassenzeichenDB.EOF Then. But the error occurs in the line VornameString = Vorname.getString.

I just don't know, what the error means. The data type in the table column is the same for Kassenzeichen and Vorname; both are almost identical.

'method 1
Set KassenzeichenDB = Conn.Execute("SELECT Kassenzeichen FROM Kassenzeichen WHERE (Kassenzeichen='" & Eingabe & "') ")

'method 2
Set KassenzeichenDB = Conn.Execute("SELECT Kassenzeichen FROM Kassenzeichen WHERE (Vorname='" & Eingabe & "') ")

If KassenzeichenDB.EOF Then 
    response.write "Empty"
Else                                    
    Do While Not KassenzeichenDB.EOF        
        KassenzeichenString = KassenzeichenDB.Fields(0) 
        Set Vorname = Conn.Execute("SELECT Vorname FROM Kassenzeichen WHERE Kassenzeichen LIKE '" & KassenzeichenString & "'")
        VornameString = Vorname.getString
        Set Nachname = Conn.Execute("SELECT Nachname FROM Kassenzeichen WHERE Kassenzeichen LIKE '" & KassenzeichenString & "'")  
        NachnameString = Nachname.getString
        response.write KassenzeichenString & " | " & NachnameString & ", " & VornameString & "</b></br>"
        KassenzeichenDB.MoveNext
    Loop
End If

Please do not build SQL like this, *ever*. This is wrong and dangerous. Read this comic https://xkcd.com/327/ to understand what the problem is and then read about [how to add parameters to SQL queries](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/append-and-createparameter-methods-example-vb) — Tomalak, Aug 23 '17 at 09:06
Yep.I will look into it. Even though Its for internal use only but it is still dangerous. Thank you for your advise :) — Tweed, Aug 23 '17 at 09:58
Dangerous does not only mean "can be attacked", it also means "can break during normal use". — Tomalak, Aug 23 '17 at 10:03
[Prepared statements in ASP Classic](https://support.microsoft.com/en-us/help/200190/how-to-call-a-parameterized-query-to-an-access-database-with-ado) — Ansgar Wiechers, Aug 23 '17 at 10:36
Is it enough to check, if the input contains keywords like drop, input etc? — Tweed, Aug 23 '17 at 12:20
No. Just use query parameters. It's not hard and it puts you on the safe side. Words like "drop", "input", etc. are part of normal language. They might not be valid in your special case here - but if you think about it "Drop" could easily be a person's last name. You can't simply *remove* them from the user's input. Don't take silly shortcuts. Use parameters for everything and move on to more interesting problems. — Tomalak, Aug 23 '17 at 21:13

BoffinBrain · Accepted Answer · 2017-08-23T11:07:58.240

1

You've checked KassenzeichenDB.EOF which looks fine, but the error occurs as a result of VornameString = Vorname.getString. That might imply that Vorname .EOF is true, so you might want to check that.

A couple of other recommendations for your code: try selecting all the columns you need. For example...

KassenzeichenString = KassenzeichenDB.Fields(0)
Set rs = Conn.Execute("SELECT Vorname, Nachname FROM Kassenzeichen WHERE Kassenzeichen LIKE '"&KassenzeichenString&"'")
If rs.EOF Then
    response.write "Vorname/Nachname Empty"
Else
    VornameString = rs("Vorname")
    NachnameString = rs("Nachname")
    response.write KassenzeichenString & " | " & NachnameString & ", " & VornameString & "</b></br>"
End If
KassenzeichenDB.MoveNext

I must also make you aware that your code is vulnerable to SQL injection attacks, so you should read up on that if your variables Eingabe or KassenzeichenString come from user input or other untrusted sources. I recommend you learn how to construct parameterized queries using ADO to avoid this issue.

edited Aug 23 '17 at 11:07

answered Aug 23 '17 at 09:11

BoffinBrain

6,337
6
33
59

I added `If Vorname.EOF Then`. Now it gives me the same error in this line. – Tweed Aug 23 '17 at 09:58
I've edited my answer to convert the entire block of code in your `Do While Not KassenzeichenDB.EOF` section. Please see if that helps at all. – BoffinBrain Aug 23 '17 at 11:09
Yep. Worked. Thank you ^^ You made my day ! – Tweed Aug 23 '17 at 12:20
Glad to help! Best of luck learning how to tackle ADO parameters next. I highly recommend it for making concise, secure database calls. – BoffinBrain Aug 23 '17 at 14:35

Strange Error with a Recordset

1 Answers1