CODEWITHSUNDEEP
c#sqlsql-server
Roman
Roman

Reputation: 37

"There is already an open DataReader associated with this Connection" error

I have this c# code:

using (SqlConnection db = Database.Connection)
{
    db.Open();
    string selectString = "SELECT id, numwave, CAST(kol_fakt AS int) AS kol_fakt FROM sorters WHERE numdoc=@numDoc AND kodprod=@kodProd AND     numpallet=@numPal";
    try
    {
        SqlCommand command = new SqlCommand(selectString, db);
        command.Parameters.AddWithValue("@numDoc", numDoc);
        command.Parameters.AddWithValue("@kodProd", kodProd);
        command.Parameters.AddWithValue("@numPal", numPal);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read() && reduseLeft > 0)
        {
            FUid = "";
            FUnumwave = "";
            FUkolfakt = 0;

            FUid = reader["id"].ToString();
            FUnumwave = reader["numwave"].ToString();
            FUkolfakt = (int)reader["kol_fakt"];
            if (FUkolfakt >= reduseLeft)
            {
                string updateString = "UPDATE sorters SET kol_fakt=@kolfakt WHERE numwave=@numwave AND id=@id";
                try
                {
                    SqlCommand upCommand = new SqlCommand(updateString, db);
                    upCommand.Parameters.AddWithValue("kol_fakt", (FUkolfakt - reduseLeft).ToString());
                    upCommand.ExecuteNonQuery();//HERE ERROR
                    reduseLeft = 0;
                }
                catch (Exception ex)
                {
                }
            }
        }
    }
    catch (Exception ex)
    {
    }
    db.Close();
}

I got this error message:

There is already an open DataReader associated with this Connection or Command which must be closed first.

I need to create SqlCommand in SqlCommand, how can I do this?

Upvotes: 0

Views: 302

Answers (3)

KennyXu
KennyXu

Reputation: 139

if your data doesn't have too many records your can use dataset or list.

if the records are too many to fit in memory. try open a new connection for the SqlCommnad. and move the SqlCommand out the while loop and call the Prepare method.

if possible, you'd better to use a stored procedure.

Upvotes: 0

user5021937
user5021937

Reputation:

You are reusing the same connection while it is still used in a reader. Try opening a new connection here

SqlCommand upCommand = new SqlCommand(updateString, db);
upCommand.Parameters.AddWithValue("kol_fakt", (FUkolfakt - reduseLeft).ToString());
upCommand.ExecuteNonQuery();//HERE ERROR
reduseLeft = 0;

Though, you better reorganize your code so that you do not need to open a new connection while still reading from the previous connection.

Upvotes: 4

Scott Chamberlain
Scott Chamberlain

Reputation: 127603

To do what you want you must either open a 2nd connection to the database (You really should not be keeping connections alive, just open them as you need them .NET will use Connection Pooling to re-use connections internally to reduce resources) or enable "Multiple Active Result Sets" in the connection string you used to create the connection in Database.Connection

Upvotes: 3

Related Questions