CODEWITHSUNDEEP
asp.netsql-servergridviewdatepicker
RelatedRhymes
RelatedRhymes

Reputation: 428

Incorrect syntax near the keyword 'and'

I am trying to filter the gridview with the help of a few checkboxlists and it works absolutely fine.It is all real time since i am using a update panel.Now when i try to add one more filer i.e couple of datepickers to filter the gridview depending on the two dates,it gives me the error message " Incorrect syntax near the keyword 'and'.". The entire code is given below :

     private void BindGrid()
{
    string CS = ConfigurationManager.ConnectionStrings["SportsActiveConnectionString"].ConnectionString;
    string query = "Select * from tblAllEvents";

    string condition = string.Empty;
    string conditionDisability = string.Empty;
    string conditionDates = string.Empty;
    foreach (ListItem item in cblGender.Items)
    {
        condition += item.Selected ? string.Format("'{0}',", item.Value) : string.Empty;
    }

    if (!string.IsNullOrEmpty(condition))
    {
        condition = string.Format(" Where Gender IN ({0})", condition.Substring(0, condition.Length - 1));
    }
    else
    {
        condition = string.Format(" Where Gender IN ('Male','Female','Mixed')", condition.Substring(0,Math.Max(0,condition.Length - 1)));
    }
    foreach (ListItem item in cblDisability.Items)
    {
        conditionDisability += item.Selected ? string.Format("'{0}',", item.Value) : string.Empty;
    }
    if (!string.IsNullOrEmpty(conditionDisability))
    {
        conditionDisability = string.Format(" and Disabled IN ({0})", conditionDisability.Substring(0, conditionDisability.Length - 1));
    }
    if(txtEventStart.Text == null)
    {
        txtEventStart.Text = "01/01/1900";
    }
    if(txtEventEnd.Text == null)
    {
        txtEventEnd.Text = "01/01/2050";
    }

     conditionDates = string.Format(" and EventStart between {0} and {1}",txtEventStart.Text,txtEventEnd.Text);

    using (SqlConnection con = new SqlConnection(CS))
    {
        using (SqlCommand cmd = new SqlCommand(query + condition + conditionDisability + conditionDates))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                cmd.Connection = con;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}

Please note the problem arises on when i include 'conditionDates' in the query. What can be the other ways to make the query work.

Edit : As i said earlier, the problem lies in the below code

     if(txtEventStart.Text == null)
    {
        txtEventStart.Text = "01/01/1900";
    }
    if(txtEventEnd.Text == null)
    {
        txtEventEnd.Text = "01/01/2050";
    }

     conditionDates = string.Format(" and EventStart between {0} and {1}",txtEventStart.Text,txtEventEnd.Text);

Upvotes: 0

Views: 880

Answers (2)

Guffa
Guffa

Reputation: 700800

You are missing apostrophes around the values:

conditionDates = string.Format(" and EventStart between '{0}' and '{1}'", txtEventStart.Text, txtEventEnd.Text);

Note however that code like this is wide open for SQL injection attacks. You should use parameters in the query instead:

conditionDates = " and EventStart between @EventStart and @EventEnd";

Then you add parameters to the command object parameter collection to supply the values to the query:

cmd.Parameters.Add("@EventStart", SqlDbType.DateTime).Value = txtEventStart.Text;
cmd.Parameters.Add("@EventEnd", SqlDbType.DateTime).Value = txtEventEnd.Text;

Upvotes: 3

Semih Yagcioglu
Semih Yagcioglu

Reputation: 4101

You clearly have a SQL syntax error. First debug your code and get the resulting query and run it separately in SQL Server. You will inspect it better in that way.

It's about how you are concatenating the SQL query when you add that part.

Upvotes: -1

Related Questions