What is the missing operator in my command?

Question

My program works perfectly, but now the only problem is that the when the database has been updated, an alert box will appear saying:

Syntax error (missing operator) in query expression 'ID='.

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim choice As String
    choice = MsgBox("Do you really want to delete?", MsgBoxStyle.YesNo)
    If choice = vbYes Then
        cnn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database.accdb")
        Dim str As String = ("DELETE FROM TableName WHERE ID= " & ComboBox1.Text & "")
        command = New OleDb.OleDbCommand(str, cnn)
        da = New OleDb.OleDbDataAdapter(command)
        cnn.Open()
        command.ExecuteNonQuery()
        Form1.Timer1.Enabled = True
        clearfields()
        Form1.Timer1.Enabled = False
        Me.Close()
    End If

    cnn.Close()

End Sub

It says that the error is located here: (ID is an Autonumber.)

Dim str As String = ("DELETE FROM TableName WHERE ID= " & ComboBox1.Text & "")

Thank you so much!

http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Ňɏssa Pøngjǣrdenlarp, Dec 06 '14 at 01:38
@OneFineDay Hi, I tried it, but now it shows another error: "Data type mismatch in criteria expression." What could be the reason behind this? — Jessa Mae Sargento, Dec 06 '14 at 01:41

score 0 · Answer 1 · answered Dec 06 '14 at 01:35

0

Dim str As String = ("DELETE FROM TableName WHERE ID= '" & ComboBox1.Text & "'")

You need single quotes for the value

answered Dec 06 '14 at 01:35

sabre

207
6
18

I tried it, but now it shows another error: "Data type mismatch in criteria expression." What could be the reason? – Jessa Mae Sargento Dec 06 '14 at 01:40
can you try commenting this line da = New OleDb.OleDbDataAdapter(command) – sabre Dec 06 '14 at 01:43
Try changing combobox1.text to CInt(ComboBox1.Text) – sabre Dec 06 '14 at 02:11
It says still the error Data Type. What I did is I removed the single quotes and did the CInt(combobox1.text). Yes the Data Type Error does not show now, but the Syntax Error (missing operator) shows again. – Jessa Mae Sargento Dec 06 '14 at 02:47
Woahh! I realized that this error is not caused by the command! – Jessa Mae Sargento Dec 06 '14 at 02:59

score 0 · Answer 2 · answered Dec 06 '14 at 03:28

First, you don't encapsulate string assignments in parentheses, and you must surround the query text in quotes.

Dim str As String = "DELETE FROM TableName WHERE ID= '" & ComboBox1.Text & "'"

Be careful. Your code opens you up to SQL Injection queries. Whatever is in ComboBox1.Text will be parsed as a SQL Command. If the ComboBox ever recieves text from a user, you must sanitize the input. Imagine how much fun you'd have if the user managed to put this text in your textbox

' OR 1=1 OR 'a' =';

So the database would process the command

DELETE FROM TableName WHERE ID= '' OR 1=1 OR 'a' =''

That command will delete all of your records.

score 0 · Accepted Answer · answered Dec 06 '14 at 03:41

I fix my own problem, what I did is I changed my ID to text, so I will not have problems in the command.

Dim str As String = ("DELETE FROM TableName WHERE ID= " & ComboBox1.Text & "")

And then I had a problem in the timer, it won't start. I retyped the code and fixed some mistakes. Now my program runs perfectly and smoothly! Thank you all for your help, I really appreciate your answers.

What is the missing operator in my command?

3 Answers3