Criteria expression Error

Question

I have just done a tutorial about deleting data from a datagridview with a checkbox and everything is great except I keep getting this error

Data type mismatch is criteria expression

It doesn't delete anything it highlights the result = cmd.ExecuteNonQuery line and says

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Data type mismatch in criteria expression.

Here's the code.

Private Sub btnDeleteAll_Click(sender As Object, e As EventArgs) Handles btnDeleteAll.Click
        Try
            con.Open()
            For Each row As DataGridViewRow In DataGridView1.Rows
                If row.Cells(0).FormattedValue = True Then
                    sql = "DELETE FROM tT WHERE ID = '" _
                    & CStr(row.Cells(1).FormattedValue) & "'"
                    With cmd
                        .Connection = con
                        .CommandText = sql
                    End With
                    result = cmd.ExecuteNonQuery
                End If
            Next
            If result = 0 Then
                MsgBox("nope")
            Else
                MsgBox("deleted.")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
End Sub

Error in then Sql query. I asasume `ID` columne is Int type, but you trying assign a string value. Debug code and watch value of `sql` variable before executing query. — Fabio, Nov 16 '14 at 22:18
@wwrwer .. Don't change your question with **"Question solved"** .. you may add it in comment ... :) — matzone, Nov 17 '14 at 02:26
@DerGolem rather than delete it the question should be rolled back because vandalising a question once the answer has been found is not acceptable — Flexo, Nov 17 '14 at 09:37
@Flexo Ah, OK. I didn't contemplate the "auto-vandalism" rollback action. — Phantômaxx, Nov 17 '14 at 09:46

score 0 · Accepted Answer · answered Nov 16 '14 at 22:32

0

This approach use a parameterized query and assumes that your ID field in the database is not a string but a numeric value (an integer for example)

' Prepare the command string with the parameter placeholder
' Note that you don't need to put quotes around the placeholder
sql = "DELETE FROM tT WHERE ID = ?"

' Prepare the command creating a parameter with a initial zero value
' The AddWithValue receives an integer constant so the parameter will be created
' of Integer type. Note that this shortcut is dangerous and it is better to 
' explicity create the parameter with your chosen type.
With cmd
   .Connection = con
   .CommandText = sql
   .Parameters.AddWithValue("@p1", 0)
End With

' Loop over your rows, place the correct value into the parameter and execute
' I don't like to use CInt or CStr from VB6 heritage and prefer to use the 
' framework methods Convert.ToXXXXX but I suppose that CInt works as well here.
For Each row As DataGridViewRow In DataGridView1.Rows
    If row.Cells(0).FormattedValue = True Then
        cmd.Parameters("@p1").Value = Convert.ToInt32(row.Cells(1).FormattedValue)
        result = cmd.ExecuteNonQuery
    End If
Next

answered Nov 16 '14 at 22:32

Steve

213,761
22
232
286

Hi Steve, that is amazing, thank you so much. But could you please explain more about what Parameters.AddWithValue("@p1",0) means and how I would go about creating it with my chosen type? Also, I have tried the code and it has worked however, it hasn't actually deleted it from the database for some reason? It deletes it in the form but when I run it again it reappears? – wwrwer Nov 16 '14 at 22:43
Better let the [documentation about the OleDbParameterCollection](http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparametercollection(v=vs.110).aspx) explain it. The failed deletion could be caused by your connection string. Could you post it? – Steve Nov 16 '14 at 22:55
In short. The sql command contains one parameter placeholder (?), so we need to have one [OleDbParameter](http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter(v=vs.110).aspx). This parameter could be created using its constructor or using the [AddWithValue](http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparametercollection.addwithvalue(v=vs.110).aspx) shortcut of the OleDbParameterCollection. The parameter has the name @p1 but it is not important because OleDb finds the needed parameters following the order of the placeholders in the sql text. – Steve Nov 16 '14 at 23:03
As suspected you are using `|DataDirectory|`. [See this answer](http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460) (it is for Sql Server Compact but it is the same for Access) – Steve Nov 16 '14 at 23:04

Criteria expression Error

1 Answers1