locked
using multiple active result sets RRS feed

  • Question


  • i tried using 

        <add key="MarsConnectionString" value="Data Source=SRIDHARRAJAN\SQLEXPRESS;Integrated Security = True;Initial Catalog = sridharsample;MultipleActiveResultSets=True"/>

    as connection string then i used connectionstring builder to construct connection string but i get this error while calling method marsdatareader

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

    what is the mistake im making ? how to use mars correctly?

    code :

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Data.SqlClient;
    using System.Data.Sql;

    namespace applicationsatatecahing
    {
        public partial class _Default : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                //Application.Lock();
                //Application["Sample"] = " hello ";
                //Application.UnLock();
                //System.Type t;            
                //Response.Write(Application["sample"].GetType().IsInterface );
                //System.String.Compare("sample", "sample");
               // simpedatareader();
                marsdatareader();

            }
            protected void marsdatareader()
            {
                SqlConnection Con = new SqlConnection();
                //Con.ConnectionString = ConfigurationManager.AppSettings["MarsConnectionString"];
                SqlCommand cmd = new SqlCommand("SELECT * FROM MARKS", Con);
                SqlCommand cmd1 = new SqlCommand("SELECT * FROM MARKS", Con);
                SqlConnectionStringBuilder constrbul = new SqlConnectionStringBuilder();
                constrbul.DataSource="SRIDHARRAJAN\\SQLEXPRESS";
                constrbul.IntegratedSecurity = true;
                constrbul.MultipleActiveResultSets = true;
                constrbul.InitialCatalog = "sridharsample";
                Con.ConnectionString = constrbul.ConnectionString;
                try
                {
                    Con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        Response.Write(rdr.GetInt32(0) + "  " + rdr.GetInt32(1) + "  " + rdr.GetInt32(2));
                    }

                    SqlDataReader rdr1 = cmd.ExecuteReader();
                    while (rdr1.Read())
                    {
                        Response.Write(rdr1.GetInt32(0) + "  " + rdr1.GetInt32(1) + "  " + rdr1.GetInt32(2));
                    }
                }
                catch
                {
                }
                finally
                {
                }
            }
            protected void simpedatareader()
            {
                //generate error if multiple active result sets are not enabled in the connection string
                //There is already an open DataReader associated with this Command which must be closed first.
                SqlConnection Con = new SqlConnection();
                Con.ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];
                SqlCommand cmd = new SqlCommand("SELECT * FROM MARKS", Con);
                SqlCommand cmd1 = new SqlCommand("SELECT * FROM MARKS", Con);
                try
                {
                    Con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        Response.Write(rdr.GetInt32(0) + "  " + rdr.GetInt32(1) + "  " + rdr.GetInt32(2));
                    }
                     //both using same command object and using new command object will generate error
                    //SqlDataReader rdr1 = cmd.ExecuteReader();
                    SqlDataReader rdr1 = cmd1.ExecuteReader();
                    while (rdr1.Read())
                    {
                        Response.Write(rdr1.GetInt32(0) + "  " + rdr1.GetInt32(1) + "  " + rdr1.GetInt32(2));
                    }
                }
                catch
                {
                }
                finally
                {
                }
            }
        }
    }

                               
    Wednesday, September 5, 2012 7:31 AM

Answers

  • You are trying to execute the same SQLCommand Your error part is highlighted in bold below

    SqlDataReader rdr = cmd.ExecuteReader();

     SqlDataReader rdr1 = cmd.ExecuteReader();

    Please change that like this and try to execute

     sqlConn.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    SqlDataReader rdr1 = cmd1.ExecuteReader();
    while (rdr.Read())
       {
         Response.Write(rdr.GetInt32(0));
    }
    while (rdr1.Read())
       {
       Response.Write(rdr1.GetInt32(0));
      }


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!


    Wednesday, September 5, 2012 8:59 AM

All replies

  • You are trying to execute the same SQLCommand Your error part is highlighted in bold below

    SqlDataReader rdr = cmd.ExecuteReader();

     SqlDataReader rdr1 = cmd.ExecuteReader();

    Please change that like this and try to execute

     sqlConn.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    SqlDataReader rdr1 = cmd1.ExecuteReader();
    while (rdr.Read())
       {
         Response.Write(rdr.GetInt32(0));
    }
    while (rdr1.Read())
       {
       Response.Write(rdr1.GetInt32(0));
      }


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!


    Wednesday, September 5, 2012 8:59 AM
  • thank you
    Wednesday, September 5, 2012 9:29 AM