Deleting record by ID

Question

I'm trying to delete a record in my database via the ID, but it says

"Data Type mismatch in criteria expression."

Why do you think so?

Private Sub testdelete()
        'THIS SAVES TO THE DEBUG ACCESS DATABASE!!!!!
        Dim conn As New OleDbConnection
        conn = New OleDbConnection
        dbprovider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        Dim databasePath = "Data Source = FULL YUGIOH ACCESS DATABASE.accdb;"
        conn.ConnectionString = dbprovider & databasePath
        Dim Stringc As String = "delete from cmon11 where ID='" & TextBox2.Text & "'"
        Dim command As OleDbCommand = New OleDbCommand(Stringc, conn)


        Try
            conn.Open()
            command.ExecuteNonQuery()
            command.Dispose()
            conn.Close()

        Catch ex As Exception
            MsgBox(ex.Message)

        Finally
            conn.Dispose()

        End Try
    End Sub

Hey, you should add more information about the database used for people to help you! — TheWildHealer, Apr 09 '19 at 10:04
Please do not create database queries by concatenating strings. You are developing a habit that will lead to SQL injection flaws in the future. Please parameterise your queries. — Dragonthoughts, Apr 09 '19 at 10:08
You should confirm if your table Id column is really a STRING or a NUMBER. The Sql you have suggests it's a STRING. And, really do learn how to use Database Parameters — JayV, Apr 09 '19 at 10:10
The only possible reason for a 'data type mismatch' here is your passing a string (TextBox2.Text) instead of a number as a value for your WHERE statement. The simple fix is to remove the single quotes around your textbox value. But this is not the correct solution because you close a bug and leave open another bigger one. Use always parameters and define the parameter type when you create an sql text — Steve, Apr 09 '19 at 10:13
yes yes it's an integer jayv see: cardPictureBox.Tag = Convert.ToInt32(cardcount) TextBox2.Text = CInt(cardPictureBox.Tag), this still doesn't work though... — user11207307, Apr 09 '19 at 10:17
Nope it is not an integer. It is a string when you put that value between single quotes in the sql command text. Again don't do it and learn how to use parameters — Steve, Apr 09 '19 at 10:17
Thanks Steve it worked!!!! Don't worry I do use parameters when saving the data!!!! — user11207307, Apr 09 '19 at 10:23
Yes, and now try to type in that textbox something that is not a number like: _'try with this'_ — Steve, Apr 09 '19 at 10:42
*"Don't worry I do use parameters when saving the data"*. What makes you think that that is a valid reason not to worry? SQL injection can occur whether you're saving data or not. Just do it properly and use parameters EVERY time. If you don't, you will have no one to blame but yourself when a malicious user deletes your entire data and you'll be scrathing your head and saying "but I wasn;t saving data". — jmcilhinney, Apr 09 '19 at 10:57

score 0 · Answer 1 · answered Apr 09 '19 at 12:19

As noted in the comments, a data type mismatch occurs because the where clause in your SQL statement is attempting to compare the value of your field ID (which you have stated is an integer) with a string value.

Following the concatenation, the SQL code might look something like this:

delete from cmon11 where ID='123'

Here, '123' is a string, not an integer - to supply an integer value, you would remove the single quotes to yield:

delete from cmon11 where ID=123

However, this does not solve the underlying issue of the potential for SQL injection when constructing SQL statements using values held by textboxes permitting arbitrary text input.

After modifying your code to remove the single quotes, consider the implications of your user typing the following into the textbox:

;drop table cmon11;--

The solution is to use parameters such that the query will fail in such circumstances, rather than performing unwanted actions. This answer from Erik is an excellent reference detailing the various ways to parameterise queries in MS Access.

score 0 · Answer 2 · answered Apr 10 '19 at 02:12

The Using...End Using ensure that your database objects are closed and disposed even if there is an error.

Always use parameters to minimize type mismatches and protect against Sql Injection. I guessed at Integer for the datatype of the Id field but you will have to check your database for the actual datatype.

Private Sub testdelete()
    Using conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source = FULL YUGIOH ACCESS DATABASE.accdb;")
        Using command As New OleDbCommand("Delete From cmon11 Where ID= @ID;", conn)
            command.Parameters.Add("@ID", OleDbType.Integer).Value = CInt(TextBox2.Text)
            conn.Open()
            command.ExecuteNonQuery()
        End Using
    End Using
End Sub

Deleting record by ID

2 Answers2