查询表达式“”中的语法错误（缺少运算符）

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:

我在删除(删除)行时遇到问题。每次我在我的字符串sql中添加列名时,它会在查询表达式中显示错误“语法错误(缺少运算符)”。这是我的代码:

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

例如我的字符串sql值是

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

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

It would produce this error:

它会产生这个错误:

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

查询表达式“_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. Still practicing interop please bare with me

在我的excel文件中插入数据时没有问题,但是当它被删除时,它说这个错误。仍然练习互操作请与我裸露

3 个解决方案

#1

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

尝试使用它,即删除*,DELETE语句不需要它:

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.

此外,您在full_date中获得的值似乎没有正确的格式。请使用表格中的格式检查full_date中的值。

On a side note:

旁注:

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

您的代码很容易出现SQL注入。您需要使用预准备语句来避免这种情况。

#2

You have syntax error in your query, please remove * from your query, hence your query may look like the following: you can check the syntax here:

您的查询中存在语法错误,请从查询中删除*,因此您的查询可能如下所示:您可以在此检查语法:

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

And the query you are using will opens a wide way for sql injection, so better approach is to use parameterized queries instead.

您正在使用的查询将为sql注入开辟一条广泛的方式,因此更好的方法是使用参数化查询。

#3

you should try

你应该试试

 string strQuery= "DELETE FROM @TableName where _date = '@date'";
 usin (SqlCommand cmd = new SqlCommand(strQuery)){

       cmd.Parameters.AddWithValue("@TableName", tablename+"$" );
       cmd.Parameters.AddWithValue("@date", full_date );

       myCon.Open();

       cmd.ExecuteNonQuery();

 }

#1