VB Select command in sql

Question

I'm using VB trying to access a database in MSACCESS.

I'm doing this really dumb thing:

SSQL9 = "select Username as docuser from Doctors where TreatmentField = '"&Treatment&"'" set Rs9 = Server.CreateObject("ADODB.recordset") Rs9.open SSQL9,conn Something=Rs9("docuser") Response.Write(Something)

And for some reason I get:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

While I'm sure there is a record. What causes the error?

It's quite simple: If both the .EOF and the .BOF properties of the record set are True, then the record set is empty, even if you expect something else. — Tomalak
– Tomalak, Commented Dec 18, 2010 at 20:51
Just curious: what will happen to your code if the Treatment variable has an apostrophe in it? Make sure you think that through carefully. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 18, 2010 at 21:06
@bend_k - that catches most cases, but there are other ways to inject sql as well. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 18, 2010 at 21:10
What happens if you use a literal for treatment eg WHERE TreatmentField='OBGYN' ? Also you might like to consider cursor types when you get this problem solved : w3schools.com/ADO/met_rs_open.asp Not all cursor type support all actions. It can be useful to Response.Write(SSQL9) and run it through Access, being careful about wildcards. It can also be useful to Response.Write(rs9(0).Name) If it is any consolation, it is probably some simple problem that you will kick yourself about :) — Fionnuala
– Fionnuala, Commented Dec 19, 2010 at 13:03

stealthyninja · Accepted Answer · 2010-12-18 21:05:43Z

1

@eve: That looks more like VBScript than VB to me... Assuming conn is an ADODB.Connection object, you don't need to create a RecordSet object unless you're going to be using pagination, so you can rewrite your code sample as --

If Treatment <> "" Then SSQL9 = "SELECT Username as docuser FROM Doctors WHERE (TreatmentField = '" & Treatment & "')" Set Rs9 = con.Execute(SSQL9) If NOT Rs9.EOF Then Something = Rs9(0) Response.Write(Something) Else ' Empty recordset ' End If Rs9.Close Else Response.Write "No 'Treatment' input was sent" End If

answered Dec 18, 2010 at 21:05

stealthyninja

10.4k11 gold badges57 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

eve Over a year ago

Thanks! I think I can identify my problem, however I have no idea why it's happening. Once I execute the SELECT command, I don't get any record back, while I'm sure there is a record with that 'treatment' in the .mdb file. This is very weird.

eve Over a year ago

Another thing - if I just select all usernames (without = treatment) I only get the first record. Strange!

stealthyninja Over a year ago

@eve: You would only get the first record using that query, because you'll have to make a loop to get all the other records as well. Could you perhaps post the Treatment input you're sending and Treatment as it is stored in the database?

stealthyninja Over a year ago

@eve: Try SSQL9 = "SELECT Username as docuser FROM Doctors WHERE (TreatmentField LIKE '%" & Treatment & "%')" and if you get your record, then the Treatment in the database has a space or a tab character in there...

stealthyninja Over a year ago

@bernd_k: I should have said code instead of query, because that is what I meant in that comment. Since she's just printing out one of the records to the screen, it will appear as if only one record was found/retrieved, which is what the second part of my comment was about: creating a loop which will print out any other retrieved records.

|

bernd_k · Accepted Answer · 2010-12-18 21:49:27Z

For a similar task we use code like this:

Dim QD As adodb.Command Dim rs As adodb.Recordset Set QD = New adodb.Command QD.CommandText = sql QD.CommandType = adCmdText QD.ActiveConnection = conn Set rs = QD.Execute

To track down the error proceed as follows:

Select a value for treatment
Replace the parameter in the query with that value -- run the query in access
Try your VB Programm with the successfull tested query.
When the error still occurs its the VB else the query

To get all the records you need some kind of loop

Do While Not rs9.EOF Something=Rs9!docuser Response.Write(Something) RS9.MoveNext Loop

Changed code:

now using Something=Rs9!docuser Please try

Collectives™ on Stack Overflow

VB Select command in sql

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related