Data type mismatch in criteria expression. -Microsoft JET DATABASE ENGINE

Question

In the code below, it was a "delete" button used in OLEDB connection. My database table name is tblinformation.

Btw, the error shows:

Data type mismatch in criteria expression. `-Microsoft JET DATABASE ENGINE`, and it was in a form of msgbox..



Imports System.Data.OleDb
Imports System.String
Public Class frmbookinfo
Dim cnn As New OleDb.OleDbConnection


Dim Str As String
    If CheckId() = False Then
        MsgBox("Id : Integer Value Required!!!")
        Exit Sub
    End If
    Try
        Str = "delete from tblinformation where bcode="
        Str += txtbookcode.Text.Trim
        Con.Open()
        Cmd = New OleDbCommand(Str, Con)
        Cmd.ExecuteNonQuery()
        Dst.clear()
        Dad = New OleDbDataAdapter("SELECT * FROM tblinformation ORDER BY bcode", Con)
        Dad.Fill(Dst, "tblinformation")
        MsgBox("Record deleted successfully...")
        If CurrentRow > 0 Then
            CurrentRow -= 1
            ShowData(CurrentRow)
        End If
        Con.Close()
    Catch ex As Exception
        MessageBox.Show("Could Not delete Record!!!")
        MsgBox(ex.Message & " -  " & ex.Source)
        Con.Close()
    End Try

You should check on the datatype of the column `bcode` in your database. I suppose it is of text type — Steve, Apr 15 '13 at 08:05

score 1 · Answer 1 · edited May 23 '17 at 11:58

Probably your field bcode in the database is of type text.
You use a string concatenation to build your command text and this cannot be helped if you fail to treat your values correctly.

Instead use parametrized queries and leave the task to correctly parse your parameters to the database framework code

    Str = "delete from tblinformation where bcode=?"
    Con.Open()
    Cmd = New OleDbCommand(Str, Con)
    Cmd.Parameters.AddWithValue("@p1", txtbookcode.Text.Trim)
    Cmd.ExecuteNonQuery()

Now your sql command contains a parameter placeholder (?) and the correct parameter value is assigned in the parameter collection. The framework code handles correctly this parameter

EDIT If your bcode field is a text field, you cannot build your command in that way. You should encapsulate your value between single quotes. Something like this.

IT WORKS BUT IT IS WRONG - VERY WRONG -
Str = "delete from tblinformation where bcode='" & txtbookcode.Text.Trim & "'"

But this is wrong from the start.

First - If your txtbookcode contains a single quote, the whole command text becomes invalid and you get a Syntax Error
Second - String concatenation is bad because you can't trust your user. If it enters some malicious text you could face a Sql Injection problem

So, I really suggest you to use the parametrized query approach illustrated in the first example

If the database field is a text you can't concatenate a string in that way. See the updated answer and the big warning — Steve, Apr 15 '13 at 08:29
thank you mr. steve, you really help me alot! as a matter of fact this is the second time you helped me.. :)) — Reign De Leon, Apr 15 '13 at 08:36
Here's the correct code and the value is now a number in database — Reign De Leon, Apr 15 '13 at 08:37

Data type mismatch in criteria expression. -Microsoft JET DATABASE ENGINE

1 Answers1

But this is wrong from the start.