what should I do in "Data type mismatch in criteria expression"

Question

Why am I getting this error? "OledbException was unhandled" "Data type mismatch in criteria expression."

There is no problem when I am querying a string data type, but when I am querying a integer data type, I am always getting this problem..

I am using microsoft access 2007

here is my source code:

Public Function searchMemberId(ByVal userId As String) As DataSet
    sqlStr = "Select Member_ID From tblMemberInfo Where Member_ID = '" & _
        Val(userId) & "'"
    ds.Clear()
    da = New OleDbDataAdapter(sqlStr, con.ConnectionString)
    da.Fill(ds, "john")

    Return ds
End Function

the data type of Member_ID is autonumber, the error was always pointing to da.Fill(ds, "john")

"ds" is a Dataset

Solution 1

If you query a numeric type, don't use quotes

SELECT Member_ID 
FROM tblMemberInfo
WHERE Member_ID = 17

If you query a string type, do use quotes

SELECT Member_ID 
FROM tblMemberInfo
WHERE Member_ID = 'john'

---

UPDATE

The Val function will not help in this case. Simply write

sqlStr = "SELECT ... WHERE Member_ID = " & userId

If the ID was a string it could contain single quotes that you must escape with double quotes in a SQL string

ID = "John's record"
sqlStr = "SELECT ... WHERE Member_ID = '" & ID.Replace("'", "''") & "'"
' ==> "SELECT ... WHERE Member_ID = 'John''s record'"

This also helps preventing SQL injections (Wikipedia). A more professional approach, however, is to use parameters. See Steve's answer and DataAdapter Parameters (ADO.NET) on MSDN (especially the "OleDb Parameter Placeholders" and "OleDb Example" sections.

Solution 2

You should use parameters and this type of problems do not occur.

sqlStr = "Select Member_ID From tblMemberInfo Where Member_ID = ?" 
ds.Clear() 
da = New OleDbDataAdapter(sqlStr, con.ConnectionString) 
da.SelectCommand.Parameters.AddWithValue("@id", Convert.ToInt32(userID))
da.Fill(ds, "john") 
Return ds