SQL命令没有正确结束错误，而过滤gridview的复选框列表选择

本文关键字：gridview 过滤 复选框 选择 列表 错误 命令 结束 SQL | 更新日期: 2023-09-27 18:14:00

我正在尝试根据所选择的checkboxlist过滤我的gridview。

这就是我尝试的

string constr = ConfigurationManager.ConnectionStrings["OracleConn"].ConnectionString;
    string strQuery = "select sr_no, type, stage, ref_no, ref_date, party_name, amount, remarks, exp_type, " +
                        "voucher_no, cheque_no,cheque_dt, chq_favr_name from XXCUS.XXACL_PN_EXPENSE_INFO";
    string condition = string.Empty;
    foreach (ListItem li in ddlStatus.Items)
    {
        condition += li.Selected ? string.Format("'{0}',", li.Value) : string.Empty;
        if (!string.IsNullOrEmpty(condition))
        {
            condition += string.Format(" Where type IN ({0})", condition.Substring(0, condition.Length - 1));
        }
        using (OracleConnection conn = new OracleConnection(constr))
        {
            using (OracleCommand cmd = new OracleCommand(strQuery + condition))
            {
                using (OracleDataAdapter sda = new OracleDataAdapter(cmd))
                {
                    cmd.Connection = conn;
                    using (DataTable dtcheck = new DataTable())
                    {
                        sda.Fill(dtcheck);
                        GridExpInfo.DataSource = dtcheck;
                        GridExpInfo.DataBind();
                    }
                }
            }
        }

但是我得到的错误是

ORA-00933: SQL命令没有正确结束

我调试的查询是

select sr_no, type, stage, ref_no, ref_date, party_name, amount, remarks, exp_type, voucher_no, cheque_no,cheque_dt, chq_favr_name 
from XXCUS.XXACL_PN_EXPENSE_INFO'10', 
Where type IN ('10')

我参考了这里

SQL命令没有正确结束错误，而过滤gridview的复选框列表选择

您应该使用字符串列表准备where条件，并在完成查询构建器阶段后运行查询。

// Create a list of your conditions to put inside the IN statement
List<string>conditions = new List<string>();
foreach (ListItem li in ddlStatus.Items)
{
    if(li.Selected) 
       conditions.Add($"'{li.Value}'");
} 
// Now build the where condition used
string whereCondition = string.Empty;
if (condition.Count > 0)
{
    // This produces something like "WHERE type in ('10', '11', '12')"
    // The values for the IN are directly concatenated together
    whereCondition = " Where type IN (" + string.Join(",", conditions) + ")";
}
using (OracleConnection conn = new OracleConnection(constr))
{
    using (OracleCommand cmd = new OracleCommand(strQuery + whereCondition))
    {
       ....

考虑到这种方法非常容易受到Sql注入攻击，如果选择的值是包含引号的字符串，则会出现解析问题

如果你开始使用参数，那么你的代码应该改为

int count = 1;
List<OracleParameter> parameters = new List<OracleParameter>();
List<string>conditions = new List<string>();
foreach (ListItem li in ddlStatus.Items)
{
    if(li.Selected) 
    {
       // parameters are named :p1, :p2, :p3 etc...
       conditions.Add($":p{count}");
       // Prepare a parameter with the same name of its placeholder and
       // with the exact datatype expected by the column Type, assign to
       // it the value and add the parameter to the list
       OracleParameter p = new OracleParameter($":p{count}",OracleType.NVarChar);
       p.Value = li.Value;
       parameters.Add(p);
       count ++;
    }
} 
if (condition.Count > 0)
{
    // This produces something like "WHERE type in (:p1, :p2, :p3)"
    // The values are stored before in the parameter list
    whereCondition = " Where type IN (" + string.Join(",", conditions) + ")";
}
using (OracleConnection conn = new OracleConnection(constr))
{
    using (OracleCommand cmd = new OracleCommand(strQuery + whereCondition))
    {
       // Add the parameters with the expected names, type and value.
       cmd.Parameters.AddRange(parameters.ToArray());
       ....

这是更多的行，但这可以防止Sql注入，没有更多的问题在解析字符串

相关文章：