求助：把sql语句改成@的写法
把sql语句改成@的写法，我改过了但报错,可能是不正确，请大家帮我改一下吧。string sql_str = "select * from shop where 1=1";
sql_str += string.IsNullOrEmpty(Request.QueryString["area"]) ? "" : " and address like'%'+'" + Request.QueryString["area"] + "'+'%'";
sql_str += string.IsNullOrEmpty(Request.QueryString["shopType"]) ? "" : " and shopType like '%'+'" + Request.QueryString["shopType"] + "'+'%'";
if (!string.IsNullOrEmpty(Request.QueryString["value1"]) && !string.IsNullOrEmpty(Request.QueryString["value2"]))
{
sql_str += " and " + Request.QueryString["boxType"] + " between'" + Request.QueryString["value1"] + "'and '" + Request.QueryString["value2"] + "'";
}
if (Request.QueryString["order"] != null)
{
sql_str += " ORDER BY " + Request.QueryString["boxType"] + " " + Request.QueryString["order"];
}
else
{
sql_str += " ORDER BY ono ASC";
}
cmm.CommandText = sql_str;
cmm.Parameters.Add("@area", SqlDbType.NVarChar);
cmm.Parameters.Add("@shopType", SqlDbType.NVarChar);
cmm.Parameters.Add("@value1", SqlDbType.NVarChar);
cmm.Parameters.Add("@value2", SqlDbType.NVarChar);
cmm.Parameters.Add("@boxType", SqlDbType.NVarChar);
cmm.Parameters.Add("@order", SqlDbType.NVarChar);
cmm.Parameters["@area"].Value = Request.QueryString["area"];
cmm.Parameters["@shopType"].Value = Request.QueryString["shopType"];
cmm.Parameters["@value1"].Value = Request.QueryString["value1"];
cmm.Parameters["@value2"].Value = Request.QueryString["value2"];
cmm.Parameters["@boxType"].Value = Request.QueryString["boxType"];
cmm.Parameters["@order"].Value = Request.QueryString["order"];
GridView1.DataSource = cmm.ExecuteReader();
GridView1.DataBind(); --------------------编程问答-------------------- 報什麽錯啊？還有sql_str建議用stringbuilder類型 --------------------编程问答-------------------- StringBuilder sql_str = "select * from shop where 1=1";
if(string.IsNullOrEmpty(Request.QueryString["area"]))
{
sql_str.AppendFormat("and address like '%{0}%'",Request.QueryString["area"])
}
不要加来加去了 恐怖
举例
“select * from shop where ID=@ID”
SqlParameter paramID=new SqlParameter("@ID",SqlDbType.Int)
paramID.value=你传来的参数
cmm.Parameters.Add(paramID);
还有要封装
另外看看三层结构把
--------------------编程问答-------------------- 饿滴神呀。你那么繁琐的SQl语句，看是看不出来哪有问题，具体要连数据库慢慢找。 --------------------编程问答--------------------
--------------------编程问答-------------------- datareader好像做不了datasource吧. --------------------编程问答-------------------- 这个你首先要创建存储过程，然后你看一下这个博客http://blog.sina.com.cn/s/blog_6f646f760100t2r0.html里面有介绍的 --------------------编程问答-------------------- 你这是想使用参数化查询。
string sql_str = "select * from shop where 1=1";
sql_str += string.IsNullOrEmpty(Request.QueryString["area"]) ? "" : " and address like'%'@area'%'";
sql_str += string.IsNullOrEmpty(Request.QueryString["shopType"]) ? "" : " and shopType like '%'@shopType'%'";
if (!string.IsNullOrEmpty(Request.QueryString["value1"]) && !string.IsNullOrEmpty(Request.QueryString["value2"]))
{
//注意这里 boxType between ...and... ' boxType不是 值是字段名
sql_str += " and (boxType between '@value1'and '@value2')";
}
if (Request.QueryString["order"] != null)
{
//注意这里 ORDER BY boxType 的 boxType不是值是字段名
sql_str += " ORDER BY boxType ";
}
else
{
sql_str += " ORDER BY ono ASC";
}
string sql = string.Format("Select * from student where LoginID = @LoginID");
using(SqlCommand objCommand = new SqlCommand(sql,connection))
{
objCommand.Parameters.AddWithValue("@LoginID",loginID);
}
利用Add方法添加参数已经过时了，建议以后使用AddWithValue();
--------------------编程问答--------------------
虽然很方便
但不指定具体的SqlDbType类型 心里不踏实 --------------------编程问答--------------------
public DataSet GetAdvanceAccountInfo()
{
SqlParameter[] storedParams = new SqlParameter[9];
storedParams[0] = new SqlParameter("@AdvanceAccountNo", SqlDbType.VarChar, 32);
storedParams[0].Value = AdvanceAccountNo;
storedParams[1] = new SqlParameter("@CustomerCode", SqlDbType.VarChar, 32);
storedParams[1].Value = CustomerCode;
storedParams[2] = new SqlParameter("@CustomerVendorCode", SqlDbType.VarChar, 32);
storedParams[2].Value = CustomerVendorCode;
storedParams[3] = new SqlParameter("@VendorCode", SqlDbType.VarChar, 32);
storedParams[3].Value = VendorCode;
storedParams[4] = new SqlParameter("@HandBookNo", SqlDbType.VarChar, 32);
storedParams[4].Value = HandBookNo;
storedParams[5] = new SqlParameter("@GenDTFrom", SqlDbType.VarChar, 20);
storedParams[5].Value = GenDTFrom;
storedParams[6] = new SqlParameter("@GenDTTo", SqlDbType.VarChar, 20);
storedParams[6].Value = GenDTTo;
storedParams[7] = new SqlParameter("@UserAccount", SqlDbType.VarChar, 32);
storedParams[7].Value = Useraccount;
storedParams[8] = new SqlParameter("@TranType", SqlDbType.VarChar, 32);
storedParams[8].Value = TranType;
DataSet ds = SqlHelper.ExecuteDataset(HelpConstant.DBCONN_STRING, CommandType.Text, "SMAdvanceAccount_sp", storedParams);
return ds;
}
--------------------编程问答--------------------
至于 SqlHelper 你下个微软的就行了，用的很多的。网上也很多 --------------------编程问答-------------------- 参数后面还缺少具体的值。如： cmm.Parameters.Add("@area", SqlDbType.NVarChar,50);
补充：.NET技术 , C#
