关键字 'and' 附近的语法不正确

Question

我试图在几个复选框的帮助下过滤gridview，它的工作绝对很好。这是所有的实时，因为我正在使用一个更新面板。现在，当我尝试添加一个更多的过滤器，即一对日期选择器来过滤网格视图取决于两个日期，它给了我错误信息"不正确的语法附近的关键字'和'。完整代码如下:

     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();
                }
            }
        }
    }
}

请注意当我在查询中包含'conditionDates'时出现的问题。还有什么其他方法可以使查询工作呢?

编辑:正如我之前所说，问题在于下面的代码

     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);

Answer 1

您的值缺少撇号:

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

但是请注意，像这样的代码对SQL注入攻击是开放的。您应该在查询中使用参数:

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

然后向命令对象参数集合添加参数，以向查询提供值:

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

Answer 2

显然有一个SQL语法错误。首先调试代码并获得结果查询，并在SQL Server中单独运行它。这样可以更好地检验它。

这是关于你如何连接SQL查询当你添加那部分。