参数化查询 '(@iBookName varchar(30),@iAuthor varchar(20),@iPublishment varch' 需要参数 @i
using System;using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
/// <summary>
/// UserInfo 的摘要说明
/// </summary>
public class BookInfo
{
public string BookID; //图书ID
public string BookName; //书名
public string Author; //作者
public string Publishment; //出版社
public string Pubdate; //出版年份
public string PriceOriginal; //原价
public string PriceNow; //现价
public string PicturePath; //图片ID
public string OwnerID; //卖家ID
public string Deadline; //拍卖截止日期
public string Type1; //图书大类
public string Type2; //图书小类
// public string StartDate; //拍卖起始
public string Rate; //新旧率
public string Quantity; //数量
public string HPrice; //当前最高价
public string BInfo; //图书简介
public readonly static string BookIDString = "BOOKINFO";
}
public class Book
{
public int UpdatePrice(string iBookID,string iHPrice)//对于拍卖图书
{
string connectionString = "Data Source=(local);Initial Catalog=易手网;Persist Security Info=True;User ID=sa;Password=sql";
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "UPDATE [book] SET HPrice=@iHPrice WHERE BookID=@iBookID";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText, con);
///创建参数并赋值
cmd.Parameters.Add("@iBookID", SqlDbType.Int);
cmd.Parameters.Add("@iHPrice", SqlDbType.Money);
cmd.Parameters[0].Value = iBookID;
cmd.Parameters[1].Value = iHPrice;
int result = -1;
try
{ //打开连接
con.Open();
//操作数据
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ //抛出异常
throw new Exception(ex.Message, ex);
}
finally
{ //关闭连接
con.Close();
}
return result;
}
public int UpdateQuantity(string iBookID, string iQuantity)//对于拍卖图书
{
string connectionString = "Data Source=(local);Initial Catalog=易手网;Persist Security Info=True;User ID=sa;Password=sql";
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "UPDATE [book] SET Quantity=@iQuantity WHERE BookID=@iBookID";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText, con);
///创建参数并赋值
cmd.Parameters.Add("@iBookID", SqlDbType.Int);
cmd.Parameters.Add("@iQuantity", SqlDbType.Int);
cmd.Parameters[0].Value = iBookID;
cmd.Parameters[1].Value = iQuantity;
int result = -1;
try
{ //打开连接
con.Open();
//操作数据
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ //抛出异常
throw new Exception(ex.Message, ex);
}
finally
{ //关闭连接
con.Close();
}
return result;
}
public int AddBook(string iBookName, string iAuthor, string iPublishment, string iPubdate,
string iPriceOriginal, string iPriceNow,string iPicturePath, string iOwnerID, string iDeadline,
string iType1, string iType2, string iRate, string iQuantity, string iBInfo, string iHPrice)
{ ///获取连接字符串
string connectionString = "Data Source=(local);Initial Catalog=易手网;Persist Security Info=True;User ID=sa;Password=sql";
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "INSERT INTO [book](BookName,Author,Publishment,Pdate,OPrice,NPrice,PicturePath,OwnerID,Deadline,Type1,Type2,Rate,Quantity,BookInfo,HPrice)VALUES(@iBookName,@iAuthor,@iPublishment,@iPubdate,@iPriceOriginal,@iPriceNow,@iPicturePath,@iOwnerID,@iDeadline,@iType1,@iType2,@iRate,@iQuantity,@iBInfo,@iHPrice)";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText, con);
///创建参数并赋值
cmd.Parameters.Add("@iBookName", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@iAuthor", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@iPublishment", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@iPubdate", SqlDbType.VarChar, 20);
//cmd.Parameters.Add("@iPubdate", SqlDbType.DateTime);
cmd.Parameters.Add("@iPriceOriginal", SqlDbType.Money);
cmd.Parameters.Add("@iPriceNow", SqlDbType.Money);
cmd.Parameters.Add("@iPicturePath", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@iOwnerID", SqlDbType.Int);
cmd.Parameters.Add("@iDeadline", SqlDbType.DateTime);
//cmd.Parameters.Add("@iDeadline", SqlDbType.DateTime);
cmd.Parameters.Add("@iType1", SqlDbType.Int);
cmd.Parameters.Add("@iType2", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@iRate", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@iQuantity", SqlDbType.Int);
cmd.Parameters.Add("@iBInfo", SqlDbType.Text);
cmd.Parameters.Add("@iHPrice", SqlDbType.Money);
cmd.Parameters[0].Value = iBookName;
cmd.Parameters[1].Value = iAuthor;
cmd.Parameters[2].Value = iPublishment;
cmd.Parameters[3].Value = iPubdate;
cmd.Parameters[4].Value = iPriceOriginal;
cmd.Parameters[5].Value = iPriceNow;
cmd.Parameters[6].Value = iPicturePath;
cmd.Parameters[7].Value = iOwnerID;
//cmd.Parameters[8].Value = Convert.ToDateTime(iDeadline);
//cmd.Parameters[8].Value = DateTime.Now;
cmd.Parameters[8].Value = iDeadline;
cmd.Parameters[9].Value = iType1;
cmd.Parameters[10].Value = iType2;
cmd.Parameters[11].Value = iRate;
cmd.Parameters[12].Value = iQuantity;
cmd.Parameters[13].Value = iBInfo;
cmd.Parameters[14].Value = iHPrice;
int result = -1;
try
{ //打开连接
con.Open();
//操作数据
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ //抛出异常
throw new Exception(ex.Message, ex);
}
finally
{ //关闭连接
con.Close();
}
return result;
}
public int AddBook1(string iBookName, string iAuthor, string iPublishment, string iPubdate,
string iPriceOriginal, string iPriceNow, string iPicturePath, string iOwnerID, string iDeadline,
string iType1, string iType2, string iRate, string iQuantity, string iBInfo)
{ ///获取连接字符串
string connectionString = "Data Source=(local);Initial Catalog=易手网;Persist Security Info=True;User ID=sa;Password=sql";
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "INSERT INTO [book](BookName,Author,Publishment,Pdate,OPrice,NPrice,PicturePath,OwnerID,Deadline,Type1,Type2,Rate,Quantity,BookInfo)VALUES(@iBookName,@iAuthor,@iPublishment,@iPubdate,@iPriceOriginal,@iPriceNow,@iPicturePath,@iOwnerID,@iDeadline,@iType1,@iType2,@iRate,@iQuantity,@iBInfo)";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText, con);
///创建参数并赋值
cmd.Parameters.Add("@iBookName", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@iAuthor", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@iPublishment", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@iPubdate", SqlDbType.VarChar, 20);
//cmd.Parameters.Add("@iPubdate", SqlDbType.DateTime);
cmd.Parameters.Add("@iPriceOriginal", SqlDbType.Money);
cmd.Parameters.Add("@iPriceNow", SqlDbType.Money);
cmd.Parameters.Add("@iPicturePath", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@iOwnerID", SqlDbType.Int);
cmd.Parameters.Add("@iDeadline", SqlDbType.DateTime);
//cmd.Parameters.Add("@iDeadline", SqlDbType.DateTime);
cmd.Parameters.Add("@iType1", SqlDbType.Int);
cmd.Parameters.Add("@iType2", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@iRate", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@iQuantity", SqlDbType.Int);
cmd.Parameters.Add("@iBInfo", SqlDbType.Text);
cmd.Parameters[0].Value = iBookName;
cmd.Parameters[1].Value = iAuthor;
cmd.Parameters[2].Value = iPublishment;
cmd.Parameters[3].Value = iPubdate;
cmd.Parameters[4].Value = iPriceOriginal;
cmd.Parameters[5].Value = iPriceNow;
cmd.Parameters[6].Value = iPicturePath;
cmd.Parameters[7].Value = iOwnerID;
//cmd.Parameters[8].Value = Convert.ToDateTime(iDeadline);
//cmd.Parameters[8].Value = DateTime.Now;
cmd.Parameters[8].Value = iDeadline;
cmd.Parameters[9].Value = iType1;
cmd.Parameters[10].Value = iType2;
cmd.Parameters[11].Value = iRate;
cmd.Parameters[12].Value = iQuantity;
cmd.Parameters[13].Value = iBInfo;
int result = -1;
try
{ //打开连接
con.Open();
//操作数据
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ //抛出异常
throw new Exception(ex.Message, ex);
}
finally
{ //关闭连接
con.Close();
}
return result;
}
}
--------------------编程问答--------------------
要问啥。。。。
--------------------编程问答--------------------
哪里出错，单步--------------------编程问答-------------------- 你的错误信息是不是 参数化查询 需要参数 但未提供该参数？ --------------------编程问答-------------------- cmd.Parameters.AddWithValue("@userName", 值);
用这种添加参数不好吗？感觉楼主添加参数的代码太复杂，这种添加参数程序会自动识别参数的类型，很简单。
另外，
string cmdText = "UPDATE [book] SET Quantity=@iQuantity WHERE BookID=@iBookID";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText, con);
///创建参数并赋值
cmd.Parameters.Add("@iBookID", SqlDbType.Int);
cmd.Parameters.Add("@iQuantity", SqlDbType.Int);
想这种int类型的，个人觉得，直接拼接比参数化好，int类型的根本不存在sql注入问题。
--------------------编程问答-------------------- 抛异常的方法你最好写出来。 --------------------编程问答-------------------- ///创建SQL语句
string cmdText = "INSERT INTO [book](BookName,Author,Publishment,Pdate,OPrice,NPrice,PicturePath,OwnerID,Deadline,Type1,Type2,Rate,Quantity,BookInfo,HPrice)VALUES(@iBookName,@iAuthor,@iPublishment,@iPubdate,@iPriceOriginal,@iPriceNow,@iPicturePath,@iOwnerID,@iDeadline,@iType1,@iType2,@iRate,@iQuantity,@iBInfo,@iHPrice)";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText, con);
///创建参数并赋值
cmd.Parameters.Add("@iBookName", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@iAuthor", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@iPublishment", SqlDbType.VarChar, 30);
cmd.Parameters.Add("@iPubdate", SqlDbType.VarChar, 20);
//cmd.Parameters.Add("@iPubdate", SqlDbType.DateTime);
cmd.Parameters.Add("@iPriceOriginal", SqlDbType.Money);
cmd.Parameters.Add("@iPriceNow", SqlDbType.Money);
cmd.Parameters.Add("@iPicturePath", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@iOwnerID", SqlDbType.Int);
cmd.Parameters.Add("@iDeadline", SqlDbType.DateTime);
//cmd.Parameters.Add("@iDeadline", SqlDbType.DateTime);
cmd.Parameters.Add("@iType1", SqlDbType.Int);
cmd.Parameters.Add("@iType2", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@iRate", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@iQuantity", SqlDbType.Int);
cmd.Parameters.Add("@iBInfo", SqlDbType.Text);
cmd.Parameters.Add("@iHPrice", SqlDbType.Money);
--------------------------------------------------------
cmd.Parameters.Add("@iBInfo", SqlDbType.Text);
cmd.Parameters.Add("@iHPrice", SqlDbType.Money);
是多余的，删掉 --------------------编程问答-------------------- 看错行了，我在6楼说的不对。
你把错误信息贴全，或者F9+F11 调试一下
补充：.NET技术 , C#
