There is already an open DataReader associated with this Command which must be closed first.....what is meann of this error

Question

2.00/5 (2 votes)

See more:

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

C#

<%@ WebHandler Language="C#" Class="Handler" %>

C#

using System;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Data;

 using connectionclass;

    public class Handler : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {

            connection con = new connection();
            con.getconnectionstring();       
            string imageid = context.Request.QueryString["ImID"];
            SqlCommand command = new SqlCommand("select Images from Upload_images where Image_ID=" + imageid, connection.con);
            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            context.Response.BinaryWrite((Byte[])dr[0]);
            context.Response.End();
            dr.Close();
            connection.con.Close();
        }


        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }

Posted 4-Jul-13 17:51pm

Amrender Saini

Updated 22-Sep-19 19:29pm

Sushil Mate

v3

Add a Solution

3 solutions

Solution 1

Maybe the following link could help you
http://stackoverflow.com/questions/15875837/when-putting-multiple-sql-commands-showing-error-there-is-already-an-open-datar[^]

Posted 4-Jul-13 18:19pm

thanh_bkhn

Solution 3

Close the DataReader & then SqlConnection, if there is an exception is not getting closed.

best practices to write this code in try catch finally block.

in finally block close the objects.

Something like this.

C#

SqlConnection conn = null;
SqlCommand cmd = null;

try
{
    conn = new SqlConnection(Settings.Default.qlsdat_extensionsConnectionString)
    cmd = new SqlCommand(reportDataSource, conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@Year", SqlDbType.Char, 4).Value = year;
    cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = start;
    cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = end;

        conn.Open(); //opens connection

    DataSet dset = new DataSet();
    new SqlDataAdapter(cmd).Fill(dset);
    this.gridDataSource.DataSource = dset.Tables[0];
}
catch(Exception ex)
{
    Logger.Log(ex);
    throw;
}
finally
{
    if(conn != null)
        conn.Dispose();

        if(cmd != null)
        cmd.Dispose();
}

Posted 4-Jul-13 18:39pm

Sushil Mate

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)

Qadri Jillani · Accepted Answer · 2013-07-04T20:12:00

Try to avoid using readers like this:

C#

SqlConnection connection = new SqlConnection("connection string");
SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection);
SqlDataReader reader = cmd.ExecuteReader();
connection.Open();
if (reader != null)
{
      while (reader.Read())
      {
              //do something
      }
}
reader.Close(); // <- too easy to forget
reader.Dispose(); // <- too easy to forget
connection.Close(); // <- too easy to forget

Instead, wrap them in using statements:

C#

using(SqlConnection connection = new SqlConnection("connection string"))
{

    connection.Open();

    using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    //do something
                }
            }
        } // reader closed and disposed up here

    } // command disposed here

} //connection closed and disposed here

The using statement will ensure correct disposal of the object and freeing of resources.

If you forget then you are leaving the cleaning up to the garbage collector, which could take a while