Click here to Skip to main content
15,394,327 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
What,s wrong this codes Incorrect syntax near ','.

What I have tried:

C#
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;
            
        }        
    }
Posted
Updated 19-Mar-17 7:09am
v2
Comments
[no name] 19-Mar-17 17:48pm
   
You have already been told how to avoid this error. If you do not listen to what people tell you, we are just wasting our time.
kmoorevs 21-Mar-17 9:21am
   
I came back after two days and gave this question a 1 vote. OP apparently doesn't care to respond to those trying to help.

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:
SQL
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:
SQL
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[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900