System.Data.OleDb.OleDbException Data type mismatch in criteria expression HResult=0x80040E07

Question

I am trying to search for an ID but I am running with some errors, I would love to know what am I doing wrong or how to fix what I am doing.

Using conn As New OleDbConnection(My.Settings.marcasConnectionString)        
  Dim cmd1 As New OleDbCommand("Select * from QATracker where ID='" & intNewPK & "'", conn)        
   cmd1.Parameters.AddWithValue("ID", TextBox2.Text)         
  Dim da As New OleDbDataAdapter           
   da.SelectCommand = cmd1      
  Dim QATracker As New DataTable          
   QATracker.Clear()            
   da.Fill(QATracker)        
   DataGridView1.DataSource = QATracker         
End Using

The error occurs at da.fill(QATracker) System.Data.OleDb.OleDbException HResult=0x80040E07 Message=Data type mismatch in criteria expression.

Answer 1

Most likely, ID is not text but number, thus:

"Select * from QATracker where ID = " & intNewPK & ""

Answer 2

If you want to use Parameter to select data from database, I recommend that you use the following sql string format with special characters "@":

"Select * from QATracker where ID=@" & intNewPK & ""


cmd1.Parameters.AddWithValue("@" & intNewPK, TextBox1.Text)

Also, you could learn it from the Microsoft Learning Examples.

 // Set the parameters.
        adapter.SelectCommand.Parameters.Add(
            "@CategoryName", OleDbType.VarChar, 80).Value = "toasters";
        adapter.SelectCommand.Parameters.Add(
            "@SerialNum", OleDbType.Integer).Value = 239;

Here is the modified code example and you could have a look:

Dim intNewPK As String = "PKID"
Using conn As New OleDbConnection(My.Settings.marcasConnectionString)        
  Dim cmd1 As New OleDbCommand("Select * from QATracker where ID=@" & intNewPK & "", conn)        
   cmd1.Parameters.AddWithValue("@" & intNewPK, TextBox2.Text)         
  Dim da As New OleDbDataAdapter           
   da.SelectCommand = cmd1      
  Dim QATracker As New DataTable          
   QATracker.Clear()            
   da.Fill(QATracker)        
   DataGridView1.DataSource = QATracker         
End Using

Hope it could help you.

Answer 3

It looks like you're trying to use a parameter but doing it wrong. The parameter name goes right into the SQL code - no concatenation.

Dim cmd1 As New OleDbCommand("SELECT * FROM QATracker WHERE ID = @ID", conn)

You then add the parameter using the same name as in the SQL code. If you're going to use AddWithValue, make sure that the value you provide is the correct type, e.g. don't provide a string if the SQL code expects a number.

cmd1.Parameters.AddWithValue("@ID", CInt(TextBox2.Text))

Don't use AddWithValue though, because there are times that it will infer the wrong data type without your realising. Use Add and specify the data type yourself:

cmd1.Parameters.Add("@ID", OleDbType.Integer).Value = CInt(TextBox2.Text)

Note that some providers will accept names for parameters but will ignore them and use the positions of the parameters to match them up. There's generally no good reason to not add the parameters in the same order as they appear in the SQL anyway - that's just good practice - but for the Jet and ACE providers for Access, for example, you have to do so. That also means that you cannot add one parameter and use it multiple times in the SQL code in those cases, which you can do for the SqlClient provider for SQL Server, for example.