Syntax Error OledbException delete statement

0
votes

I can't fugure out what my syntax error is here. Anyone spot it? Or am I going about this all wrong?

Dim myCommand As New OleDb.OleDbCommand("delete * from Team where intPlayerNo='" & txtUniformNo.Text & "'_ strFirstName='" & txtFirstName.Text & "'_ strLastName='" & txtLastName.Text & "'_ strParentName='" & txtParent.Text & "'_ strAddress='" & txtAddress.Text & "'_ strCity='" & txtCity.Text & "'_ strState='" & txtState.Text & "'_ strZipCode='" & txtZip.Text & "'_ strPhone='" & txtPhone.Text & "'_ intAge='" & txtAge.Text & "'", myConnection)
2
vb.netoledbcommand
Wrong SQL syntax (DELETE FROM...) and not using SQL Parameters. Also possibly doing some data type conversions in the SQL - Ňɏssa Pøngjǣrdenlarp
What is the database? - Steve
@Steve: Without parameterised queries, compromised. ;) - The Blue Dog
ALSO DB tables usually have something called a Primary Key which uniquely identifies each record so that you do not have to specify a dozen fields to delete one record. One typo in a textbox and your SQL fails. ALSO, there are a lot of underscores there that are in the wrong place no matter whether they are part of the column name or are supposed to be line continuation characters - Ňɏssa Pøngjǣrdenlarp
do you really have columns named strFirstName and intAge?? - Ňɏssa Pøngjǣrdenlarp

2 Answers

2
votes

A delete statement is Delete From Team Where...

You would be best advised to use Parameterized Queries to avoid SQL Injection Attacks

0
votes

Your syntax is wrong because you have multiple WHERE conditions but you don't use the correct AND/OR to connect the conditions together. Of course the DELETE * FROM table syntax is accepted only by MS-ACCESS, but the correct SQL syntax is DELETE FROM (without the *) and is accepted also by Access so it is better to use the standard.

Said that you need to use a parameterized query in your command and not a string concatenation 

Dim cmdText = "DELETE FROM Team " & _
              "WHERE intPlayerNo = @playerNo AND " & _
              "strFirstName = @firstName AND " & _ 
              ".... and so on with the other fields "
Dim myCommand As New OleDb.OleDbCommand(cmdText, myConnection)
myCommand.Parameters.Add("@playerNo", OleDbType.VarWChar).Value = txtUniformNo.Text
myCommand.Parameters.Add("@firstName", OleDbType.VarWChar).Value = txtFirstName.Text 
... continue with the other parameters required by the WHERE statement
myCommand.ExecuteNonQuery()

Using parameters will keep your code safe from Sql Injection and avoid syntax errors if some of your textbox values contains a single quote.

Keep in mind that if your table has a primary key, then your WHERE could simply reduced to only the PrimaryKeyFieldName = @Value