Question about VB.Net to Access

[straight_edge]

Hi

I have a VB.Net project linked to an access database, so far I can display the information from the database tables in text files, and add and delete data from the database.

Now I wish to a have a query set up so the user can search for a specific record in the databse by a field but I keep getting an error message.

The code I am using is:

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = N:\Database.mdb"
con.Open()

Dim userEnteredID As String
Dim command As String
Dim dataset As New DataSet
Dim dataAdapter As OleDb.OleDbDataAdapter

userEnteredID = InputBox("Please enter an ID")
command = "SELECT * FROM table WHERE " & "ID = " & " '" & userEnteredID & "'"
dataAdapter = New OleDb.OleDbDataAdapter(command, con)
dataAdapter.Fill(dataset , "Database")

con.Close()

The error message highlights:

dataAdapter.Fill(dataset, "Database")

and says "Data type mismatch in criteria expression"

If anyone can help it would be very much appreciated

Thanks

 

[vis781]

command = "SELECT * FROM table WHERE " & "ID = " & " '" & userEnteredID & "'"
dataAdapter = New OleDb.OleDbDataAdapter(command, con)
dataAdapter.Fill(dataset , "Database")

Three problems here.

1. Your table is called 'Table' not only can this get confusing, table may also be a reserved word in Access.

2. Your database is called 'Database' see number 1.

3. Your Dataset is called 'dataset' See number 2.

I'd suggest changing the names.

 

[straight_edge]

The names are different in the actual code, I changed the names because I thought it would be more clear.

Other than that can you see anything in the code that might cause the error because I can't!

 

[vis781]

userEnteredID = InputBox("Please enter an ID")
command = "SELECT * FROM table WHERE " & "ID = " & " '" & userEnteredID & "'"
dataAdapter = New OleDb.OleDbDataAdapter(command, con)
dataAdapter.Fill(dataset , "Database")

Change to...

userEnteredID = InputBox("Please enter an ID")
command = "SELECT * FROM table WHERE ID = ?"
Command.Parameters.AddWithValue("@userEnterID", userEnteredID)
dataAdapter = New OleDb.OleDbDataAdapter(command, con)
dataAdapter.Fill(dataset)

 

[straight_edge]

I changed the code, it doesn't like "command.Parameters" because 'Parameters' is not a member of 'string', do I have to declare command as something else?

 

[straight_edge]

Okay problem solved I just had ' ' around the userEnteredID which was making it a string when it needed to be an integer

thanks vis781 for your help