Sql在'like'关键字附近给出错误语法错误。 [英] Sql gives error Incorrect syntax near 'like' keyword.

问题描述

我正在尝试从程序运行sql查询，在存储过程中使用查询时，它工作正常，但是当从程序使用时它会产生上述错误。我无法根据客户要求使用存储过程。我的代码如下：

 DateTime frmdate = Convert.ToDateTime（dtpfrom.Text）; 
 DateTime todate = Convert.ToDateTime（dtpto.Text）; 
  string  datetimefrm = frmdate.ToString（  yyyy-MM-dd 00：00：00.000）; 
  string  datetimeto = todate.ToString（  yyyy-MM-dd 00：00：00.000）; 
 
  string  ap =  已批准 + ' ％'; 
  string  st =  完全处理 + ' ％'; 
 
  string  query =   SELECT DISTINCT indent_no为'缩进否'， + 
   approved_date为已批准日期 ， + 
   from_store_name为From Store，to_store_name为To Store，  + 
   indent_status为'缩进状态'， + 
   processing_status as'Processing Status' + 
   FROM vw_DSInfo_indent_details + 
   WHERE [indent_status] like' + ap +   + 
   AND [approved_date] BETWEEN' + datetimefrm +  'AND' + datetimeto +  ' + 
    AND [processing_status] NOT LIKE' + st +  ' + 
   AND [from_store_name] =' + txtfromstore.SelectedItem +  ' + 
   AND [to_store_name] =' + txttostore.SelectedItem +  '; 
 
 cmd =  new  SqlCommand（）; 
 cmd.CommandType = CommandType.Text; 
 cmd.CommandText = query; 
 cmd.Connection = con; 
 table =  new  DataTable（）; 
 adapter =  new  SqlDataAdapter（cmd）; 
 adapter.Fill（table）; 

解决方案

空格。

  string  query =   SELECT DISTINCT indent_no as'缩进否'，' + 
   approved_date为'Approved Date'， + 
   from_store_name为From Store，to_store_name为To Store， + 
   indent_status为'缩进状态'， + 
   processing_status为Processing Status + 
   FROM vw_DSInfo_indent_details + 
   WHERE [indent_status] like' + ap +  ' + 

生成如下字符串：

 SELECT DISTINCT ... processing_status为'Processing Status'FROM vw_DSInfo_indent_detailsWHERE [indent_status] like ... 

你为 Status'FROM 因为报价终止了先前的数据。但

 vw_DSInfo_indent_detailsWHERE 

被视为一个单词。

请不要这样做。

Do不连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。你在这里侥幸成功，因为你自己定义了这些字符串，但这是一个危险的先例，如果代码发生变化，可能会让你对SQL注入做得很开心。养成一直使用参数化查询的习惯 - 它更容易阅读，更安全！




使用调试器检查查询变量的内容，你会看它有类似

 processing_status为'Processing Status'FROM vw_DSInfo_indent_detailsWHERE [indent_status]如'xyz'AND [approved_date] BETWEEN 

您没有添加适当的空格。

 FROM vw_DSInfo_indent_details+ 
WHERE [indent_status] like'+ ap +'+ 
AND [approved_date] BETWEEN'+ datetimefrm +'AND'+ datetimeto +'+ 

您还应该使用参数化查询，因为如果您搜索的数据包含撇号，您的代码将失败，更糟糕的是它让您打开关于SQL注入攻击。谷歌如何使用ado.net进行参数化查询。




它看起来单一引号是额外的。

只需用以下代码替换你的变量

  string  ap =   Approved％; 
  string  st =  完全处理％ ; 

I am trying to run sql query from program , the query when used in stored procedure, it works fine , but when using from program it gives above error.I cant use stored procedures as per clients requirement. my code is bellow,

DateTime frmdate = Convert.ToDateTime(dtpfrom.Text);
                    DateTime todate = Convert.ToDateTime(dtpto.Text);
                    string datetimefrm = frmdate.ToString("yyyy-MM-dd 00:00:00.000");
                    string datetimeto = todate.ToString("yyyy-MM-dd 00:00:00.000");

   string ap = "Approved"+'%';
   string st = "Fully Processed"+'%';
                        
 string query = "SELECT DISTINCT indent_no as 'Indent No' ," +
                 "approved_date as 'Approved Date'," +
                 "from_store_name as 'From Store',to_store_name as 'To Store'," +
                 "indent_status as 'Indent Status'," +
                 "processing_status as 'Processing Status'" +
                 "FROM vw_DSInfo_indent_details" +
                 "WHERE [indent_status] like '"+ap+"'" +
        "AND [approved_date] BETWEEN '" + datetimefrm + "' AND '" + datetimeto + "'" +
        "AND [processing_status] NOT LIKE '"+st+"'" +
        "AND [from_store_name] ='" + txtfromstore.SelectedItem + "'"+
        "AND [to_store_name] = '" + txttostore.SelectedItem + "'";
                          
                            cmd = new SqlCommand();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = query;
                            cmd.Connection = con;
                            table = new DataTable();
                            adapter = new SqlDataAdapter(cmd);
                            adapter.Fill(table);

解决方案

Spaces.

string query = "SELECT DISTINCT indent_no as 'Indent No' ," +
                 "approved_date as 'Approved Date'," +
                 "from_store_name as 'From Store',to_store_name as 'To Store'," +
                 "indent_status as 'Indent Status'," +
                 "processing_status as 'Processing Status'" +
                 "FROM vw_DSInfo_indent_details" +
                 "WHERE [indent_status] like '"+ap+"'" +

Generates a string like:

SELECT DISTINCT ...processing_status as 'Processing Status'FROM vw_DSInfo_indent_detailsWHERE [indent_status] like ...

You get away with it for the Status'FROM becuaset the quote terminates the prvious data. But

vw_DSInfo_indent_detailsWHERE

is taken as a single word.
And please, don't do it like that.
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. You get away with it here because you define the strings yourself, but it's a dangerous precedent and may well leave you wide open to SQL Injection if the code is ever changed. Get into the habit of using parameterised queries at all times - it's a lot easier to read, and a lot safer!

Use the debugger to examine the contents of the query variable and you'll see it has things like

processing_status as 'Processing Status'FROM vw_DSInfo_indent_detailsWHERE [indent_status] like 'xyz'AND [approved_date] BETWEEN

You're not adding the appropriate spaces.

FROM vw_DSInfo_indent_details" +
" WHERE [indent_status] like '"+ap+"'" +
" AND [approved_date] BETWEEN '" + datetimefrm + "' AND '" + datetimeto + "'" +

You should also use paramterised queries as your code will fail if the data you search for contains an apostrophe, and worse it leaves you open to SQL injection attacks. Google for how to use paramterised queries with ado.net for examples.

It looks single quotes are extra.
Simply just replace your variable with following code

string ap = "Approved%";
string st = "Fully Processed%";

这篇关于Sql在'like'关键字附近给出错误语法错误。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！