1

I'm trying to update my table with current records with actual days of the week(Strings), but my code is giving me the error:

Data type mismatch in criteria expression

I've rigorously checked my SQL statement to ensure that right data types are being used, but I couldn't identify any problems at all.Can someone help me identify where my problem lies and any potential solutions.

The SQL statement is be:

str = "Update [tblAppointments] Set [Day] ='" & day & "' WHERE [TimeID] ='" & redbutton & "'"

redbutton = 18 

day = "wednesday"

This is the database I am working with:

database

Bugs
  • 4,491
  • 9
  • 32
  • 41
Vbv123
  • 15
  • 3
  • You have single-quotes around your `TimeID` value, but in the image you show `TimeID` as being numeric. Did you check that one? – David Apr 21 '17 at 11:57
  • 1
    try this, integers usually should not have quotes around them; str = "Update [tblAppointments] Set [Day] ='" & day & "' WHERE [TimeID] =' & redbutton & '" – Intern87 Apr 21 '17 at 11:58
  • 4
    If you're going to construct SQL through string concatenation, the first and obvious thing to do is to print the complete final string (to e.g. a message box) and make sure it's sane. Then decide to *stop* building SQL queries by string concatenation because it's a **really** bad idea and go and instead read up on *parameterizing* queries. – Damien_The_Unbeliever Apr 21 '17 at 11:59
  • @David Yes I've tried re-correcting like what Intern87 suggested, but it's still giving me the same error – Vbv123 Apr 21 '17 at 13:28
  • @Vbv123: So what's the new query being executed? (Both the line of code which builds it and the actual runtime value being built.) – David Apr 21 '17 at 13:29

3 Answers3

0

Others have suggested it, I'll spell it out: Use Sql Parameterisation

Like so,

Dim day As String = "Wednesday"
Dim redbutton As Integer = 18

Dim qp As New List(Of SqlParameter)
qp.Add(New SqlParameter("@DAY", SqlDbType.VarChar) With {.Value = day})
qp.Add(New SqlParameter("@redbutton", SqlDbType.Int) With {.Value = redbutton})

var str = "Update [tblAppointments] Set [Day] = @DAY WHERE [TimeID] = @redbutton"
executeNonQuery(str,qp) 'Or however you're communicating with database.

See How to: Execute a Parameterized Query for more information.

IronAces
  • 1,857
  • 1
  • 27
  • 36
0

Firstly, use SQL Parameters. This can't be stressed enough. See How does the SQL injection from the “Bobby Tables” XKCD comic work? for a better understanding on why it's important.

Please be aware that when using OleDb parameters, it's the order in which they are added that is important, not the name. This in itself can cause problems. Ensure you are adding them as they appear in the command. In your case Day then TimeID.

Your code would look something similar to:

Using con As New OleDbConnection(connectionString),
      cmd As New OleDbCommand("UPDATE [tblAppointments] SET [Day] = ? WHERE [TimeID] = ?", con)

    cmd.Parameters.Add(New OleDbParameter("Day", OleDbType.VarChar)).Value = "wednesday"
    cmd.Parameters.Add(New OleDbParameter("TimeID", OleDbType.Integer)).Value = 18

    con.Open()

    cmd.ExecuteNonQuery()
End Using
Bugs
  • 4,491
  • 9
  • 32
  • 41
0

thanks for your help I found a solution to my problem

Dim str = "Update [tblAppointments] Set [Day] ='" & day & "' WHERE [TimeID] =" & CInt(redbutton) & " ;"

Doing this allowed my program to work, however, I do realise that it's safer that I used parametised SQL, bu I there's no clear way of me doing that.

Vbv123
  • 15
  • 3
  • "there's no clear way of me doing that" - really? I drop comments about using `?` as the parameter placeholder and also mention `OleDbParameter` on the other answer and you have **no** clear way forward? – Damien_The_Unbeliever Apr 21 '17 at 16:59