SqlDataReader循环中的Update语句不起作用

Question

我正在运行以下代码。以下代码在SQL DataReader循环运行中的executeonquery行中给出错误。我得到

异常详细信息：System.ComponentModel.Win32异常：等待操作超时

我尝试过mycomm.CommandTimeout = 600;，但没有帮助。

我在其他项目中也执行过这些相同的语句，它在那里运行得非常好。

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection myconn, myconn1;
SqlCommand mycomm;
myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
myconn1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
String q = "select * from addcart where sessionid=@sessid";
mycomm = new SqlCommand(q, myconn);
mycomm.Parameters.AddWithValue("@sessid", Session["sid"].ToString());
myconn.Open();
mycomm.CommandTimeout = 600;
SqlDataReader myreader;
myreader = mycomm.ExecuteReader();
if (myreader.HasRows)
{
while (myreader.Read())
{
myconn1.Open();

String qt = myreader["quantity"].ToString();
String bookid = myreader["bookid"].ToString();
q = "update addbook set stock=stock-@st where bid=@bid";
mycomm = new SqlCommand(q, myconn1);
mycomm.Parameters.AddWithValue("@st", qt);
mycomm.Parameters.AddWithValue("@bid", bookid);
mycomm.ExecuteNonQuery();//getting error on this line
myconn1.Close();
}
}
myconn.Close();

myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
q = "select * from newpayment where sessionid=@sessionid";
mycomm = new SqlCommand(q, myconn);
mycomm.Parameters.AddWithValue("@sessionid", Session["sid"]);
myconn.Open();
myreader = mycomm.ExecuteReader();
myreader.Read();
Label2.Text = myreader["orderno"].ToString();
Label3.Text = myreader["billamount"].ToString();
Label4.Text = myreader["address"].ToString();
myreader.Close();
myconn.Close();      
}

Answer 1

尝试将值赋予命令。我认为这个代码可能会对你有所帮助。

SqlConnection myconn;
SqlCommand mycomm;
SqlCommand rdrcmd;
myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
String q = "select * from addcart where sessionid=@sessid";
mycomm = new SqlCommand(q, myconn);
mycomm.Parameters.AddWithValue("@sessid", Session["sid"].ToString());
myconn.Open();
SqlDataReader myreader;
myreader = mycomm.ExecuteReader();
if (myreader.HasRows)
{
while (myreader.Read())
{
String qt = myreader["quantity"].ToString();
String bookid = myreader["bookid"].ToString();
qt="stock-"+qt;
q = "update addbook set stock='"+qt+"' where bid=@bid";
rdrcmd = new SqlCommand(q, myconn);
rdrcmd.Parameters.AddWithValue("@bid", bookid);
rdrcmd.ExecuteNonQuery();
}
myreader.Close();
}
myconn.Close();

Answer 2

也许您的问题是因为在不同的作用域中使用了相同的变量。

不要在变量上吝啬。只要用清晰的名字定义一个新的。

你必须释放资源！这可以使用Dispose方法或通过将它们封装在using语句中来完成。

参见修改后的代码

var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (var selectConnection = new SqlConnection(connectionString))
{
selectConnection.Open();
string selectSql = "select * from addcart where sessionid=@sessid";
using (var selectCommand = new SqlCommand(selectSql, selectConnection))
{
selectCommand.Parameters.AddWithValue("@sessid", Session["sid"].ToString());
using (var reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
using (var updateConnection = new SqlConnection(connectionString))
{
updateConnection.Open();
string qt = reader["quantity"].ToString();
string bookid = reader["bookid"].ToString();
string updateSql = "update addbook set stock=stock-@st where bid=@bid";
using (var updateCommand = new SqlCommand(updateSql, updateConnection))
{
updateCommand.Parameters.AddWithValue("@st", qt);
updateCommand.Parameters.AddWithValue("@bid", bookid);
updateCommand.ExecuteNonQuery();
}
}
}
}
}
}

using (var selectConnection = new SqlConnection(connectionString))
{
selectConnection.Open();
string selectSql = "select * from newpayment where sessionid=@sessionid";
using (var selectCommand = new SqlCommand(selectSql, selectConnection))
{
selectCommand.Parameters.AddWithValue("@sessionid", Session["sid"]);
using (var reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
Label2.Text = reader["orderno"].ToString();
Label3.Text = reader["billamount"].ToString();
Label4.Text = reader["address"].ToString();
}
}
}
}

另请参阅是否可以停止使用AddWithValue((？有用的文章。