none
exception in sqlcommand reader! RRS feed

  • Question

  • Hi! it is generating the following exception:

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

     

     

     

    Code Block

    public class dbResult

    {

    public dbResult()

    {

    }

     

    public DataTable GetStdBySubjClass(string subjectClass,ConModule subModule)

         {

              string strSql = "select classId from Classes where className=@subjectClass" ;

              DataSet myDataSet = new DataSet();

    SqlCommand cmd = new SqlCommand(strSql, subModule.objCon, subModule.trans);

    cmd.Parameters.Add("@subjectClass", SqlDbType.VarChar, 50).Value = subjectClass;

    cmd.ExecuteReader();

     

     

    SqlDataAdapter objDA = new SqlDataAdapter(strSql, subModule.objCon);

     

    ///it gives the exception when it reaches the next statement!

     

    objDA.Fill(myDataSet,"Classes");

    DataTable myTable=myDataSet.Tables["Classes"];

    return myTable;

          }

    }

     

     

    Friday, October 19, 2007 8:26 PM

Answers

  •  NRJ88 wrote:

     

    l its not giving the previous error but another one.....after removing the cmd.ExecuteReader()

     

    exception...

          Must declare the scalar variable "@subjectClass".

     

     

     

    My apologies,

     

    You also need to replace

    SqlDataAdapter objDA = new SqlDataAdapter(strSql, subModule.objCon);

     

    with

     

    SqlDataAdapter objDA = new SqlDataAdapter(cmd);

     

    The SqlCommand is already prepared to request the data and it will be used to fill the data in the Fill method of the Adapter.

    Saturday, October 20, 2007 4:04 AM

All replies

  • Why do you execute "cmd.ExecuteReader()"?

     

    cmd.ExecuteReader() will send the query to the database and prepare the connection to receive the resultset as a forward only read only resultset. You have a resultset on subModule.objCon. ExecuteReader() returns a DataReader object but since you do not use it it will be lost.

     

    You then create a SqlDataAdapter using the same connection. When you fill the DataSet using the SqlDataAdapter it needs to request a resultset to do it from. Though since you already have a resultset opened by calling cmd.ExecuteReader() this call will fail as you already see.

     

    You should only have to remove "cmd.ExecuteReader()" to make your code work.

     

    .NET 2.0 with SQL Server 2005 can use multiple resultssets on the same connections but this is not what you want to do, not at least in this code snippet. More about MARS.
    http://msdn2.microsoft.com/en-us/library/yf1a7f4f(VS.80).aspx

    Saturday, October 20, 2007 1:14 AM
  •  

    l its not giving the previous error but another one.....after removing the cmd.ExecuteReader()

     

    exception...

          Must declare the scalar variable "@subjectClass".

     

     

    Saturday, October 20, 2007 3:31 AM
  • Code Block

    public DataTable GetStdBySubjClass(string subjectClass,ConModule subModule) {

     

    string strSql;

    DataSet myDataSet;

    SqlCommand cmd;

    SqlDataAdapter objDA;

    DataTable myTable;

    SqlDataReader reader;

     

    strSql = "select classId from Classes where className=@subjectClass";

    cmd = new SqlCommand(strSql,subModule.objCon,subModule.Trans);

    cmd.Parameters.Add("@subjectClass",SqlDbType.VarChar,50).Value = subjectClass;

    reader = cmd.ExecuteReader();

    reader.Close(); // Close the reader after you have instantiated it with the call to ExecuteReader

     

    objDa = new SqlDataAdapter(cmd); // Use existing command; otherwise add parameter again

    myDataSet = new DataSet();

    objDA.Fill(myDataSet,"Classes");

    myTable = myDataSet.Tables["Classes"];

     

    return myTable;

     

    }

     

     

    Saturday, October 20, 2007 3:57 AM
  •  NRJ88 wrote:

     

    l its not giving the previous error but another one.....after removing the cmd.ExecuteReader()

     

    exception...

          Must declare the scalar variable "@subjectClass".

     

     

     

    My apologies,

     

    You also need to replace

    SqlDataAdapter objDA = new SqlDataAdapter(strSql, subModule.objCon);

     

    with

     

    SqlDataAdapter objDA = new SqlDataAdapter(cmd);

     

    The SqlCommand is already prepared to request the data and it will be used to fill the data in the Fill method of the Adapter.

    Saturday, October 20, 2007 4:04 AM