查询中关键字'AND'附近的语法不正确

Question

当我点击checkboxlist2项目而不选择checkboxlist1项目时，按钮搜索会出错。我认为checkboxlist2编码错误，请遵循以下代码：

protected void Page_Load(object sender, EventArgs e)
{
    if(!this.IsPostBack)
    {
        this.BindGrid();
    }
}
protected void Button1_Click(object sender, EventArgs e)
{
    this.BindGrid();
}
private void BindGrid()
{
    string conString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    string query = "SELECT * FROM Table_RegistrationInfo WHERE true";
    string condition = string.Empty;
    foreach (ListItem item in CheckBoxList1.Items)
    {
        condition += item.Selected ? string.Format("'{0}',", item.Value) : "";
    }
    if (!string.IsNullOrEmpty(condition))
    {
        condition = string.Format(" AND Nationality in ({0})", condition.Substring(0, condition.Length - 1));
    }
    string condition2 = string.Empty;
    foreach (ListItem item in CheckBoxList2.Items)
    {
        condition2 += item.Selected ? string.Format("'{0}',", item.Value) : "";
    }
    if (!string.IsNullOrEmpty(condition2))
    {
        //condition = string.Format(" where Nationality in ({0})", condition.Substring(0, condition.Length - 1));
        condition2 = string.Format(" AND GivenName in ({0})", condition2.Substring(0, condition2.Length - 1));
    }
    SqlCommand cmd = new SqlCommand(query + condition + condition2); using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con; sda.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                sda.Fill(ds);
                GridView2.DataSource = ds;
                GridView2.DataBind();
            }
        }
    }

}
protected void Select_Nationality(object sender, EventArgs e)
{
    this.BindGrid();
}
protected void Select_GivenName(object sender, EventArgs e)
{
    foreach(ListItem item in CheckBoxList1.Items)
    {
        if(item.Selected == true)
        {
            this.BindGrid();
        }
    }
}

我必须实现8个复选框列表来过滤到gridview中。如果有任何其他容易实现的技术，我必须使用从3个表中筛选出来的。

Answer 1

如果condition为null或为空，则查询变为：

SELECT ... FROM ... AND ...

这是不正确的。

你可以通过如下的"基本"查询来修复它：

SELECT ... FROM ... WHERE 1=1

然后将类似AND ...的片段附加到其中。然后，您的查询变为（当只有condition有效时：

SELECT ... FROM ... WHERE 1=1 AND condition 

相当于

SELECT ... FROM ... WHERE condition

在您的情况下，更改此：

string query = "SELECT * FROM Table_RegistrationInfo";

进入

string query = "SELECT * FROM Table_RegistrationInfo WHERE 1=1 ";

并更改此：

condition = string.Format(" where Nationality in ({0})", condition.Substring(0, condition.Length - 1));

进入

condition = string.Format(" AND Nationality in ({0})", condition.Substring(0, condition.Length - 1));

Answer 2

修改您的代码。

    private void BindGrid()
    {
        string conString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        string query = "SELECT * FROM Table_RegistrationInfo";
        string condition = string.Empty;
        foreach(ListItem item in CheckBoxList1.Items)
        {
            condition += item.Selected ? string.Format("'{0}',", item.Value) : "";
        }
        if(!string.IsNullOrEmpty(condition))
        {
            condition = string.Format(" where Nationality in ({0})", condition.Substring(0, condition.Length - 1));
        }
        string condition2 = string.Empty;
        foreach(ListItem item in CheckBoxList2.Items)
        {
            condition2 += item.Selected ? string.Format("'{0}',", item.Value) : "";
        }
        if(!string.IsNullOrEmpty(condition2))
        {
         if(!string.IsNullOrEmpty(condition))
            {
            condition+=" And ";
            }
       else {
            Condition +=" Where "
            }

  //condition = string.Format(" where Nationality in ({0})", condition.Substring(0, condition.Length - 1));
            condition2 = string.Format(" GivenName in ({0})", condition2.Substring(0, condition2.Length - 1));
        }
        SqlCommand cmd = new SqlCommand(query + condition + condition2);
        using(SqlConnection con = new SqlConnection(conString))
        {
            using(SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using(DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    GridView2.DataSource = ds;
                    GridView2.DataBind();
                }
            }
        }
    }