Reputation: 37
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
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
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
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