Question

asp.net网站系统中，一个数据层的方法总是报错“where 附近有语法错误”

public DataTable Select_StockBySTBrandandSTNum(string stockBrand,decimal num)
{
SqlConnection conn = new SqlConnection(connHelper.CONNECTION);
conn.Open();
string sql = "select * from StockTable_A where ";

SqlCommand cmd = new SqlCommand(sql, conn);

if (stockBrand.Trim()!="")
{
sql += "STBrand=@STBrand and STNum<=@STNum";

SqlParameter parbrand = new SqlParameter("@STBrand", SqlDbType.NVarChar, 50);
parbrand.Value = stockBrand;
cmd.Parameters.Add(parbrand);
SqlParameter parnum = new SqlParameter("@STNum", SqlDbType.Decimal);
parnum.Value = num;
cmd.Parameters.Add(parnum);
}
else
{
sql += "STNum<=@STNum";
SqlParameter parnum = new SqlParameter("@STNum", SqlDbType.Decimal);
parnum.Value = num;
cmd.Parameters.Add(parnum);
}

DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);

sda.Dispose();
conn.Close();
conn.Dispose();

return ds.Tables[0];
}

}

Answer 1

SqlCommand cmd = new SqlCommand(sql, conn);
这句放错地方了

追问

我放到if else之后还是报同样的错。。。

追答

你仔细读你的程序 就会发现 ,你if~~~else 里面追加的sql 串 并没有加到 cmd 里面去
你仅仅追加了parnum参数
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
if~~~~~~~~~~~~~~~~
{
~~~~~~~~~~~~~~~~~~~
cmd.CommandText = sql;
cmd.Parameters.Add(parnum);
}
else~~~~~~~~~~~~~~~~~
{
~~~~~~~~~~~~~~~~~~~~~~
cmd.CommandText = sql;
cmd.Parameters.Add(parnum);
}

Answer 2

string sql = "select * from StockTable_A where ";
where后面加个 1=1
即：
string sql = "select * from StockTable_A where 1=1 ";

sql += "STBrand=@STBrand and STNum<=@STNum";前面加个 and 即
sql += "and STBrand=@STBrand and STNum<=@STNum";

sql += "STNum<=@STNum";这个也是 前面加 and。

Answer 3

我想问一下你把数据放在一个什么容器里？
sda.Fill(ds);
这个很有可能是空

Answer 4

string sql = "select * from StockTable_A where "；
你的sql语句有问题了啊，是不是where这拼接完没有空格啊，在sql这打个断点，看看你的sql语句， public DataTable Select_StockBySTBrandandSTNum(string stockBrand,decimal num)
{
SqlConnection conn = new SqlConnection(connHelper.CONNECTION);
conn.Open();
string sql = "select * from StockTable_A where 1=1 ";

if (stockBrand.Trim()!="")
{
sql += " and STBrand=@STBrand and STNum<=@STNum";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter parbrand = new SqlParameter("@STBrand", SqlDbType.NVarChar, 50);
parbrand.Value = stockBrand;
cmd.Parameters.Add(parbrand);

SqlParameter parnum = new SqlParameter("@STNum", SqlDbType.Decimal);
parnum.Value = num;
cmd.Parameters.Add(parnum);

}
else
{
sql += " and STNum<=@STNum";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter parnum = new SqlParameter("@STNum", SqlDbType.Decimal);
parnum.Value = num;
cmd.Parameters.Add(parnum);
}

DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);

sda.Dispose();
conn.Close();
conn.Dispose();

return ds.Tables[0];
}

}

追问

where后我专门打了个空格。。。
设了断点，进来也有值，sql语句也没错，一到sda.Fill(ds);就报错...

追答

QQ1784597379加我帮你看看