'，'附近的语法不正确。 [英] Incorrect syntax near ', '.

问题描述

这是错误的代码'，'附近的语法不正确。



我尝试了什么：

  public   partial   class  CryReport：System.Web.UI.Page 
 {
  public   string  UserName; 
 SqlConnection con =  new  SqlConnection（ @ 数据源= METHOUN;初始目录= ITReportDb;集成安全性=真）; 
 ReportDocument crypt =  new  ReportDocument（）; 
 受保护  void  Page_Load（ object  sender，EventArgs e）
 {
 DateTime strDate = Convert.ToDateTime（Request.QueryString [  DateFrom]）; 
 DateTime endDate = Convert.ToDateTime（Request.QueryString [  DateTo]）; 
  string  txtUserName = Request.QueryString [ 用户名]; 
 GenerateReport（strDate，endDate）; 
} 
 
 受保护  void  GenerateReport（DateTime strDate，DateTime endDate ）
 {
 con.Open（）; 
  //   SqlCommand cmd = new SqlCommand（SELECT * FROM tblReport WHERE Date at'+ strDate + '和'+ endDate +'，con）;  
 SqlCommand cmd =  new  SqlCommand（  SELECT * FROM tblReport WHERE' + strDate +  '，' + endDate +  '，' + UserName +  '，con）; 
 SqlDataAdapter da =  new  SqlDataAdapter（）; 
 da.SelectCommand = cmd; 
 DataTable datatable =  new  DataTable（）; 
 da.Fill（datatable）;  //  根据imageID和fill数据集获取值 
 con.Close（）; 
 ReportDocument crystalReport =  new  ReportDocument（）;  //  创建水晶报表对象 
 crystalReport.Load（Server.MapPath（  CrystalReport1.rpt））;  //  报告路径 
 crystalReport.DataDefinition.FormulaFields [  DateForm]。文本=  ' + strDate.ToString（）+  '; 
 crystalReport.DataDefinition.FormulaFields [  DateTo]。Text =  ' + endDate.ToString（）+  '; 
 crystalReport.DataDefinition.FormulaFields [  UserName]。Text =  ' + UserName.ToString（）+  '; 
 crystalReport.SetDataSource（datatable）;  //   binding datatable  
 CrystalReportViewer1.ReportSource = crystalReport; 
 
} 
} 

解决方案

看起来你第一次几乎是对的。使用第一个查询（您注释掉的那个）但在单引号之前/之后放置空格。 （日期分隔符）第二个查询完全搞砸了。

两件事：

1）永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。因此，将日期字符串转换为DateTime值，向用户报告任何问题，并通过参数将DateTime值直接传递给SQL。

2）这是您注意到的：BETWEEN具有特定语法，正如您在第一次查询中使用的那样：

  SELECT  ...  WHERE 值 BETWEEN  start  AND  结束 

您的第二个版本用逗号替换AND并在最后添加更多垃圾（就SQL而言）：

  WHERE  日期   ' + strDate +'，' < span class =code-string>+ endDate +'，' + UserName + '    

< blockquote>永远不要通过连接用户输入来构建SQL查询，它被命名为SQL注入，它对您的数据库很危险并且容易出错。

名称中的单引号和程序崩溃。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

What,s wrong this codes Incorrect syntax near ','.

What I have tried:

public partial class CryReport : System.Web.UI.Page
    {
        public string UserName;
        SqlConnection con = new SqlConnection(@"Data Source=METHOUN;Initial Catalog=ITReportDb;Integrated Security=True");
        ReportDocument crypt = new ReportDocument();
        protected void Page_Load(object sender, EventArgs e)
        {
            DateTime strDate = Convert.ToDateTime(Request.QueryString["DateFrom"]);
            DateTime endDate = Convert.ToDateTime(Request.QueryString["DateTo"]);
            string txtUserName = Request.QueryString["UserName"];
            GenerateReport(strDate,endDate); 
        }

        protected void GenerateReport(DateTime strDate, DateTime endDate)
        {
            con.Open();
            //SqlCommand cmd = new SqlCommand("SELECT * FROM tblReport WHERE Date between'" + strDate + "'and'" + endDate + "'", con);
            SqlCommand cmd = new SqlCommand("SELECT * FROM tblReport WHERE Date between' " + strDate + "','" + endDate + "','"+UserName+" ' " , con);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataTable datatable = new DataTable();
            da.Fill(datatable); // getting value according to imageID and fill dataset
            con.Close();
            ReportDocument crystalReport = new ReportDocument(); // creating object of crystal report
            crystalReport.Load(Server.MapPath("CrystalReport1.rpt")); // path of report 
            crystalReport.DataDefinition.FormulaFields["DateForm"].Text = "'" + strDate.ToString() + "'";
            crystalReport.DataDefinition.FormulaFields["DateTo"].Text = "'" + endDate.ToString() + "'";
            crystalReport.DataDefinition.FormulaFields["UserName"].Text = "'" + UserName.ToString() + "'";
            crystalReport.SetDataSource(datatable); // binding datatable
            CrystalReportViewer1.ReportSource = crystalReport;
            
        }        
    }

解决方案

It looks like you were almost right the first time. Use the first query (the one you commented out) but put spaces before/after your single quotes. (date delimiters) That second query is just totally messed up.

Two things:
1) Never 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. So convert your date strings to DateTime values, report any problems to the user, and pass DateTime values directly to SQL via parameters.
2) This is the one you've noticed: BETWEEN has a specific syntax, as you have used in teh first query:

SELECT ... WHERE value BETWEEN start AND end

Your second version replaces AND with a comma and adds more rubbish (as far as SQL is concerned) at the end:

WHERE Date between' " + strDate + "','" + endDate + "','"+UserName+" ' "

Never build an SQL query by concatenating with user inputs, it is named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash.
SQL injection - Wikipedia[^]
SQL Injection[^]

这篇关于'，'附近的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！