Error on data mismatch doesn't stop

Question

I can keep getting 'data type mismatch on citerio expression' Because TimeOfOrder is a data/time in the access, i tried converting it to a string. It didnt work as well. So then i tried removing that field to find the source of the problem the same error still appears. This is my code so far:

Private Sub Lists_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Lists.SelectedIndexChanged
    Dim Sql As String
    Dim data As dbase.OleDbDataReader
    Dim cmd As dbase.OleDbCommand

    conn.Open() ' start the connection
    Sql = "select * from corder where OrderID = """ & Lists.Text & """"
    cmd = New dbase.OleDbCommand(Sql, conn) ' how the commad is run on this connection


    data = cmd.ExecuteReader
    data.Read()

    TextBox1.Text = data("TableNumber")
    TextBox2.Text = data("Status")
    TextBox3.Text = data("TimeOfOrder".ToString("f"))
    textbox4.text = data("Orders")


    conn.Close()
End Sub

its complaining about the *criteria* that is, the WHERE clause. If ID is a numeric, you are passing a string in the SQL. Obligatory Bobby Tables warning: you should use parameters rather than gluing bits of string together for SQL. AMong other things it will take care of the ticks and quotes for you. Also, you have some DB objects there not being disposed of. — Ňɏssa Pøngjǣrdenlarp, Jun 15 '15 at 20:41
if OrderID is a numeric column, you need to convert the value to numeric, now it is text/string — Ňɏssa Pøngjǣrdenlarp, Jun 15 '15 at 20:47
For how to use param and dispose of things, [see this answer](http://stackoverflow.com/a/29187199/1070452) — Ňɏssa Pøngjǣrdenlarp, Jun 15 '15 at 20:55
@Plutonix Sorry, i did not understand what you mean, could you please simplify it or show me the example and help me understand what you mean — Ruby, Jun 15 '15 at 21:01
If the database has `OrderID` defined as an numeric, like integer, then the mismatch is from `Lists.Text` being string. String <> Integer, therefore `data type mismatch`. Convert `Lists.Text` to an integer like shown in the link for PatientID — Ňɏssa Pøngjǣrdenlarp, Jun 15 '15 at 21:07

score 0 · Answer 1 · answered Jun 16 '15 at 06:28

You can modify like this. OrderID is read as text from Lists.Text so it is "ready" to be concatenated with the SQL string as is:

Private Sub Lists_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Lists.SelectedIndexChanged
    Dim Sql As String
    Dim data As dbase.OleDbDataReader
    Dim cmd As dbase.OleDbCommand

    conn.Open() ' start the connection
    Sql = "select * from corder where OrderID = " & Lists.Text & ""
    cmd = New dbase.OleDbCommand(Sql, conn) ' how the commad is run on this connection

    data = cmd.ExecuteReader
    data.Read()

    TextBox1.Text = data("TableNumber")
    TextBox2.Text = data("Status")
    ' Cast/format the field value, not the field name.
    TextBox3.Text = data("TimeOfOrder").ToString("f")
    textbox4.text = data("Orders")    

    conn.Close()

End Sub

Error on data mismatch doesn't stop

1 Answers1