Syntax error (missing operator) in query expression ""

Question

I'm having trouble deleting(DELETE) rows. Everytime I add column names in my string sql it shows the error "Syntax error (missing operator) in query expression ". This is my code:

OleDbConnection myCon = new OleDbConnection("provider = Microsoft.Jet.OLEDB.4.0;DataSource = '" + fileLocation + "'; Extended Properties=Excel 8.0;");
OleDbCommand myCmd = new OleDbCommand();

myCmd.Connection = myCon;

string sql = "DELETE * FROM [" + tablename + "$] where _date = '" + full_date + "'";

myCmd.CommandText = sql;
myCon.Open();

myCmd.ExecuteNonQuery();

myCon.Close();

For Example my string sql value is

"DELETE * FROM [Sheet1$] where _date = '03 09 2015'"

It would produce this error:

Syntax Error (missing operator) in query expression "_date = '03 09 2015'"

I have no problems when inserting data in my excel file but when it comes to delete it says this error.

Answer 1

Try to use this i.e., remove the *, it is not required with DELETE statement:

string sql = "DELETE FROM [" + tablename + "$] where _date = '" + full_date + "'";

Also the value which you are getting in full_date doesnt seem to be in correct format. Do check the value which you are getting in full_date with the format in which you are having in your table.

On a side note:

You code is prone to SQL Injection. You need to use prepared statement to avoid that.