Update Access DB Data Type Mismatch

Question

I have an access db i am trying to update via vb.net and an update statement. Everytime i try and update a field by clicking a button, it errors with "Data Type Mismatch in Criteria Expression" Below is my code:

Dim con1 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Redirection\fakename\Desktop\AccessDBs\MovieCatalog.mdb")
        Dim sqlupdate As String
        sqlupdate = "UPDATE Table1 SET Title=@Title, YearofFilm=@YearofFilm, Description=@Description, Field1=@Field1 WHERE ID='" & TextBox5.Text & "'"
          Dim cmd As New OleDbCommand(sqlupdate, con1)
        ' This assigns the values for our columns in the DataBase.   
        ' To ensure the correct values are written to the correct column  
        cmd.Parameters.Add(New OleDbParameter("@Title", TextBox1.Text))
        cmd.Parameters.Add(New OleDbParameter("@YearofFilm", TextBox2.Text))
        cmd.Parameters.Add(New OleDbParameter("@Description", TextBox3.Text))
        cmd.Parameters.Add(New OleDbParameter("@Field1", TextBox4.Text))

        con1.Open()
        cmd.ExecuteNonQuery()
        con1.Close()

you (wisely) used Params for the update fields, do that with the ID field as well. It sounds like ID is numeric, but '" & TextBox5.Text & "'" will pass text - user text which could be "cat", "dog" or "'Droptables..." — Ňɏssa Pøngjǣrdenlarp

Steve Steve · Accepted Answer · 2014-08-10T17:15:18

First thing, use a parameterized query for every value, also the ID should be passed as a parameter

Now stop a moment and think about the datatype of your Database Fields. Are they all string types? Your parameters are all of string type, if one of your fields is a numeric then you should pass a parameter with a numeric datatype

For exampe Yearoffilm and ID seems to be possible numeric fields.

So

Dim sqlupdate As String
sqlupdate = "UPDATE Table1 SET Title=@Title, YearofFilm=@YearofFilm, " & _ 
             "Description=@Description, Field1=@Field1 WHERE ID=@id"
Using con1 As New OleDbConnection("....")
Using cmd As New OleDbCommand(sqlupdate, con1)
    cmd.Parameters.AddWithValue("@Title", TextBox1.Text)
    cmd.Parameters.AddWithValue("@YearofFilm", Convert.ToInt32(TextBox2.Text))
    cmd.Parameters.AddWithValue("@Description", TextBox3.Text)
    cmd.Parameters.AddWithValue("@Field1", TextBox4.Text)
    cmd.Parameters.AddWithValue("@id", Convert.ToInt32(TextBox5.Text))    
    con1.Open()
    cmd.ExecuteNonQuery()
End Using
End Using

The Field1 is a mistery. If it is a numeric field then try to convert it also.

Update Access DB Data Type Mismatch

1 Answers