Update statement inside SqlDataReader loop not working

Question

I am running the following code. Following code is giving error on executenonquery line inside my SQL DataReader loop runs. I am getting

Exception Details: System.ComponentModel.Win32Exception: The wait operation timed out

I have tried mycomm.CommandTimeout = 600; but it not helping.

I have executed these same statements in other projects it is working perfectly fine over there.

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();      
}

You use the same `mycomm` command in two different places. Create two commands. — Alexander Petrov, Jul 21 '20 at 11:59
Do not reuse `myreader`, `myconn`. Create as many variables as needed. Also you must Dispose resources. — Alexander Petrov, Jul 21 '20 at 12:00
Thanks, everyone for the suggestions. I have tried it with multiple variables and multiple command instances also but still, it doesn't help. Any other suggestions?? — Master, Jul 22 '20 at 06:47
I have also tried converting qt variable to number, but still, nothing makes any difference — Master, Jul 22 '20 at 06:54
Oh goodness!!! .. Instead of using a SqlDataReader, une a SqlDataAdapter and put all the data into a DataSet. The DataReader will keep the connection open while you are iterating over each record, this means you will have table/row locking issues if you try to perform updates while reading. — Jason, Jul 22 '20 at 22:20
I'm not even going to ask why this is being done in a page load. The way I read this logic is that every time the page is loaded, the stock amount is reduced by the amount in the cart. I hope this page doesn't get reloaded more than once. — Jason, Jul 22 '20 at 22:28

Abdul Haseeb · Answer 1 · 2020-07-22T07:40:57.197

0

Try to give the value to the command. I think this code might help you.

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();

edited Jul 22 '20 at 07:40

answered Jul 21 '20 at 11:43

Abdul Haseeb

514
4
13

Thanks for your help, but it still not working. It is still the same. Any other suggestions? I am getting the following error Win32Exception (0x80004005): The wait operation timed out] [SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.] – Master Jul 22 '20 at 06:45
@Master now my answer get edited, try this one. I think this will solve your question – Abdul Haseeb Jul 22 '20 at 07:44

Alexander Petrov · Answer 2 · 2020-07-22T21:46:46.783

Perhaps your problem is because you use the same variables in different scopes.

Don't skimp on the variables. Just define a new one with clear names.

You must release the resources! This is done using the Dispose method or by wrapping them in the using statement.

See the modified code

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();
            }
        }
    }
}

Also see Can we stop using AddWithValue() already? useful article.

Update statement inside SqlDataReader loop not working

2 Answers2