CODEWITHSUNDEEP
sqlvb.net
ConnorL
ConnorL

Reputation: 227

Search AutoNumber field in Access via SQL (VB)

I'm trying to allow the 'user' to search for 'members' by searching for their member ID. Here is a screenshot of the database (design view).

https://drive.google.com/file/d/0B7pMpT1WtgKDVU5MVkFYNXJjcTA/edit?usp=sharing

If in VB I search for the ID as an Integer it produces a datatype mismatch error (see below)

https://drive.google.com/file/d/0B7pMpT1WtgKDMFVtYlFiWlpES0E/edit?usp=sharing

Sorry for asking another probably pointless question, thank you though - mean's a lot!

Upvotes: 0

Views: 888

Answers (2)

SoftSan
SoftSan

Reputation: 2472

The error lies in this line:

sqlstatement = "Select * from Members where ID = '" +  MemberID + "';"

It should be:

sqlstatement = "Select * from Members where ID = " +  MemberID + ";"

Since your "ID" field is Autonumber, you're checking condition with a string which is wrong.

Upvotes: 2

peter.petrov
peter.petrov

Reputation: 39457

You're doing 

"WHERE ID = '" + MemberID + "';"

in your VB code. I think this might be your problem. I guess it thinks the ID is string, and not int.

I am not very familiar with VB but try it without the '' i.e. like this:

"WHERE ID = " + MemberID + ";"

Upvotes: 0

Related Questions