Matt Brown Matt Brown - 23 days ago 11
Vb.net Question

Parameterized SQL UPDATE Statement

I have seen many examples of the parameterized SQL queries, but have run into a few questions.

I know I am not hashing passwords, so please disregard that.

The following is my code:

cmd.CommandText = "UPDATE users SET username=@uName, pwd=@pWord, role=@uRole, actuser=@uActive WHERE ID=" & recordID
cmd.Parameters.AddWithValue("@uName", tbUsername.Text)
cmd.Parameters.AddWithValue("@pWord", tbPassword1.Text)
cmd.Parameters.AddWithValue("@uRole", cbRole.SelectedItem)
cmd.Parameters.AddWithValue("@uActive", isActive)


The error I get is:


"Data type mismatch in criteria expression"


I don't understand why because everything in the database is "Short String" except for "actuser" which is set to Yes/No and "isActive" is set to "Yes" when the code attempts to run. I have validated the table column headers to be accurate and I don't know why this is not working.

This format seems to work with an INSERT statement, but not an UPDATE statement... but I can't confirm that right at this moment.

Any help would be appreciated.

EDIT:

Updated code with same error.

Dim isActive As Boolean = False
If cbxActive.CheckState = CheckState.Checked Then
isActive = "True"
End If

cmd.CommandText = "UPDATE users SET username=@uName, pwd=@pWord, role=@uRole, actuser=@uActive WHERE ID=@ID"
cmd.Parameters.AddWithValue("@uName", tbUsername.Text)
cmd.Parameters.AddWithValue("@pWord", tbPassword1.Text)
cmd.Parameters.AddWithValue("@uRole", cbRole.SelectedItem.ToString())
cmd.Parameters.AddWithValue("@uActive", If(isActive, "Yes", "No"))
cmd.Parameters.AddWithValue("@ID", recordID)

myConnection.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MsgBox(ex.Message)
Exit Sub
End Try`


SelectedItem is returning a string from a collection in a combobox.

Here is my DB structure.Link to Picture of Access DB structure

Here is the other structure pic. enter image description here

Answer

Of course, we can only guess what is in isActive and cbRole.SelectedItem but we know for sure that one of these if not both, causing your issue.

Specifically, you can use AddWithValue only when direct match type is used. otherwise you need to precisely set parameter data type like this

Dim p As New OleDbParameter(@pName, OleDbType.SomeType)
p.Value = [your value]
cmd.Parameters.Add(p)

In your case these 2 lines could be [scratch that] are! problematic

cmd.Parameters.AddWithValue("@uRole", cbRole.SelectedItem)
cmd.Parameters.AddWithValue("@uActive", isActive)

first line returns object that can be anything (only you know). What to do with it

' If selected item is string
cmd.Parameters.AddWithValue("@uRole", cbRole.SelectedItem.ToString())
' If selected item is integer
cmd.Parameters.AddWithValue("@uRole", CInt(cbRole.SelectedItem))
' If selected item is complex object in which you use property
Dim val As Integer = DirectCast(cbRole.SelectedItem, MyObjectType).SomeIntProperty
cmd.Parameters.AddWithValue("@uRole", val)

' If your value 'isActive' is boolean and you keep Yes/No in DB, you need this
cmd.Parameters.AddWithValue("@uActive", if(isActive, "Yes", "No"))
' and opposite
cmd.Parameters.AddWithValue("@uActive", if(isActive = "Yes", 1, 0))
' you have to be careful here. In .NET false=0, all else <> 0. so, if your business logic needs True=1, all else <> 1, you need to convert correspondingly 

And last thing - why not parameterize ...WHERE ID=" & recordID...? do exactly same thing

cmd.CommandText = ". . . . . WHERE ID= @id"
cmd.Parameters.AddWithValue("@id", recordID)