Search per ID on access using Visual Studio 2022 VB.NET

Question

I am trying to do a search using an ID to find the information that I need. The database is Access and the ID is an autonumber value(primary key). I am using Visual Studio 2022(visual basic) to to the search an it works if I do the search using the name but if I use the ID it give me an error. Can you help me solving this problem?

Error message:

System.Data.OleDb.OleDbException: data type mismatch in criteria expression

This is my code:

Dim consulta As String 
Dim oda As New OleDbDataAdapter 
Dim ods As DataSet 
Dim registro As Byte 

If ComboBox2.Text <> " " Then 
    consulta = "select *from QAtracker where ID='" & TextBox55.Text & " '" 

    oda = New OleDbDataAdapter(consulta, Conexion1) 
    ods = New DataSet oda.Fill(ods, "QAtracker") 

    registro = ods.Tables("QAtracker").Rows.Count 

    If registro <> 0 Then 
        DataGridView1.DataSource = ods 

        DataGridView1.DataMember = "QAtracker" 

        TextBox55.Text = ods.Tables("QAtracker").Rows(0).Item("ID") 
        TextBox5.Text = ods.Tables("QAtracker").Rows(0).Item("Login") 
        TextBox6.Text = ods.Tables("QAtracker").Rows(0).Item("Logout") 
    else 
        MsgBox("Codigo no existe") 

        Conexion1.Close() 
    End If 
End If

Any ideas?

Thanks for your help

I tried doing to search with another Value and it worked. I would love to know what am I doing wrong.

Answer 1

Since the ID is a number and not a string, you should remove the quotation marks.

consulta = "select *from QAtracker where ID=" &  val(TextBox55.Text)

Please note that your code is not secure against SQL injection. While the val() function works well when searching for numbers, it always outputs a number. However, exercise caution when dealing with strings, as you need to escape special characters to enhance security.

Answer 2

Since the textbox returns text and SQL is text, there is no need to cast the textbox to a numeric which then will be casted to text.

However, the input should be validated to hold a number, thus - at least - use something like this:

If ComboBox2.Text <> " " And Val(TextBox55.Text) <> 0 Then 
    consulta = "select * from QAtracker where ID = " & TextBox55.Text 

Also, do use meaningful names for the controls.

Answer 3

Please check the data type of the ID in the database.

In your query statement, you use the '" "' structure, which treats your TextBox55.Text parameter as a string and gets an error if it doesn't match your ID data type.

You need a proper data type conversion to avoid this error.