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

Answer 1

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