There is already an open datareader associated with this command which must be closed first.

Question

0.00/5 (No votes)

See more:

I have created tournament and have got 1 image for each of the tournament somewhere else and stored them in the db. Now, when people click on "Delete", it should get their tournament details and respective image deleted in a single connection.

But it's showing error "There is already an open DataReader associated with this Command which must be closed first."

C#

protected void LVTournament_ItemDeleting(object sender, ListViewDeleteEventArgs e)
        {
            if (Session["TournId"] != null)
            {
                int ClubId = Convert.ToInt32(Request.Cookies["ClubDetails"]["ClubId"]);
                int TournId = Convert.ToInt32(Session["TournId"]);
                SqlConnection con = new SqlConnection(constr);
                ImageDeletion(TournId);
                string querydelTour = "DELETE FROM Tournaments WHERE TournamentId = @TournId";
                string querydelimg = "DELETE FROM TournamentImages WHERE TournamentId = @TournId";
                SqlCommand cmd1 = new SqlCommand(querydelTour, con);
                SqlCommand cmd2 = new SqlCommand(querydelimg, con);
                cmd1.Parameters.AddWithValue("@TournId", TournId);
                cmd2.Parameters.AddWithValue("@TournId", TournId);
                con.Open();
                cmd1.ExecuteNonQuery();
                cmd2.ExecuteNonQuery();
                con.Close();
                UpdateTourCount(ClubId);
                ClientScript.RegisterStartupScript(Page.GetType(), "Success", "<script language='javascript'>alert('Tournament Deleted...')</script>");
                Response.Redirect("TournamentView.aspx");
            }
            else
            {
                ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "SessionExpires();", true);
                return;
            }
        }

What I have tried:

I guess, there is a problem over here.

C#

con.Open();
                cmd1.ExecuteNonQuery();
                cmd2.ExecuteNonQuery();
                con.Close();

How to get rid of this?

Posted 9-Oct-16 19:36pm

Bigprey

Updated 9-Oct-16 22:11pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Answer 1 · 2016-10-09T22:11:00

C#

SqlConnection con = new SqlConnection(constr);
ImageDeletion(TournId);
string querydelTour = "DELETE FROM Tournaments WHERE TournamentId = @TournId";
string querydelimg = "DELETE FROM TournamentImages WHERE TournamentId = @TournId";
// these two commands use the same connection so you can only process
// one of the commands at a time, but you're doing both.
SqlCommand cmd1 = new SqlCommand(querydelTour, con);
SqlCommand cmd2 = new SqlCommand(querydelimg, con);

C#

SqlConnection con1 = new SqlConnection(constr);
SqlConnection con2 = new SqlConnection(constr);
ImageDeletion(TournId);
string querydelTour = "DELETE FROM Tournaments WHERE TournamentId = @TournId";
string querydelimg = "DELETE FROM TournamentImages WHERE TournamentId = @TournId";
// these two commands use the same connection so you can only process
// one of the commands at a time, but you're doing both.
SqlCommand cmd1 = new SqlCommand(querydelTour, con1);
SqlCommand cmd2 = new SqlCommand(querydelimg, con2);

You'll also need con1.Open and con2.Open etc.

However a better design would be to make a function like "DeleteTournamentImages" and "DeleteTournament" and inside those functions you'd do the SQL work related to just that task, then you'd call both of those functions.

OriginalGriff · Answer 2 · 2016-10-09T19:57:00

What has happened is that you are refusing an SQL connection, which you don't close and SqlCommand, SqlDataReader, etc. objects that you don't Dispose when you are finished with them.
So somewhere in your code you create a DataReader, and it still exists, even if it was created 'local' to a method because it is on the heap where it will remain until the Garbage Collector gets around to Disposing of it.

Don't recycle your connection, make a new one each time. Always Dispose connection, command, and all other SQL objects, or this kind of problem is not only going to get worse, it will also start to crash your website as it gets busy: these things are a scarce resources and you can't just keep creating them!

There is already an open datareader associated with this command which must be closed first.

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0