locked
Data Access question RRS feed

  • Question

  • Hi everyone, a newbie question.

    I want to read one table and use a datareader to iterate thru its record. In the meantime I have another sqlcommand open which calls a stored procedure. I want to execute this command for each record in the data reader. I get an error "There is already an open DataReader associated witht his command..."

    Here is some of the code

            using (SqlConnection cn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("Select * from members", cn);
                cn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
               
                SqlCommand cmd2 = new SqlCommand("aspnet_Membership_CreateUser",cn);
                cmd2.CommandType = CommandType.StoredProcedure;
                SqlParameter userNameParm = cmd2.Parameters.Add("@UserName", SqlDbType.NVarChar, 255);
                SqlParameter passwordParm = cmd2.Parameters.Add("@Password", SqlDbType.NVarChar, 128);

                while (reader.Read())
                {
                    userNameParm.Value = reader["emailAddress"].ToString();
                    passwordParm.Value = reader["usrPassword"].ToString();
         
         cmd2.ExecuteReader(); ---> this is where the error occurs
      }


    }


    Thanks
    Tuesday, September 25, 2007 8:16 AM

Answers

  • Hi clekkas

     

     

    I have doubt why are u exute reader second time  ....

     

      while (reader.Read())
                {
                    userNameParm.Value = reader["emailAddress"].ToString();
                    passwordParm.Value = reader["usrPassword"].ToString();

         
         cmd2.ExecuteReader(); ---> this is where the error occurs  ///i mean here -->

     

     }

     

    this loop using for reading value so u no need to excute here 

     

     SqlDataReader reader = cmd.ExecuteReader();  at this point all data were reterived so no need to exute second time

    i mean no need this part  CMD2.EXCUTEREADER()

     

    Just while loop enough to read out value from reader   afterward u can close reader or dispose

     

     

    Its would be corrected

     

       using (SqlConnection cn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("Select * from members", cn);
                cn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
               
                SqlCommand cmd2 = new SqlCommand("aspnet_Membership_CreateUser",cn);
                cmd2.CommandType = CommandType.StoredProcedure;
                SqlParameter userNameParm = cmd2.Parameters.Add("@UserName", SqlDbType.NVarChar, 255);
                SqlParameter passwordParm = cmd2.Parameters.Add("@Password", SqlDbType.NVarChar, 128);

                while (reader.Read())
                {
                    userNameParm.Value = reader["emailAddress"].ToString();
                    passwordParm.Value = reader["usrPassword"].ToString();


              }

     

           cmd2.excuteNonquery(); 

                    reader.close();

     

      }

     

    Sql data reader use only for read value from database u cannot use to insertvalue to database

     

     

    Regards

    palan

    Tuesday, September 25, 2007 9:34 AM

All replies

  • Hi

     

               u have already opened a sql reader to excure first sql command Hence u have to close before use to another operation.

     

    it would be

     

    reader.close();

     

     

    Now u can open new datareader

     

     

    Regards

    palan

     

     

    Tuesday, September 25, 2007 8:40 AM
  • Hi,

    Thank you for your reply,

    My question is if i close the reader would I be able to iterate thru it still to read each record in order to add to the new query?

    Thanks


    Tuesday, September 25, 2007 8:43 AM
  • Hi clekkas

     

     

    I have doubt why are u exute reader second time  ....

     

      while (reader.Read())
                {
                    userNameParm.Value = reader["emailAddress"].ToString();
                    passwordParm.Value = reader["usrPassword"].ToString();

         
         cmd2.ExecuteReader(); ---> this is where the error occurs  ///i mean here -->

     

     }

     

    this loop using for reading value so u no need to excute here 

     

     SqlDataReader reader = cmd.ExecuteReader();  at this point all data were reterived so no need to exute second time

    i mean no need this part  CMD2.EXCUTEREADER()

     

    Just while loop enough to read out value from reader   afterward u can close reader or dispose

     

     

    Its would be corrected

     

       using (SqlConnection cn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("Select * from members", cn);
                cn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
               
                SqlCommand cmd2 = new SqlCommand("aspnet_Membership_CreateUser",cn);
                cmd2.CommandType = CommandType.StoredProcedure;
                SqlParameter userNameParm = cmd2.Parameters.Add("@UserName", SqlDbType.NVarChar, 255);
                SqlParameter passwordParm = cmd2.Parameters.Add("@Password", SqlDbType.NVarChar, 128);

                while (reader.Read())
                {
                    userNameParm.Value = reader["emailAddress"].ToString();
                    passwordParm.Value = reader["usrPassword"].ToString();


              }

     

           cmd2.excuteNonquery(); 

                    reader.close();

     

      }

     

    Sql data reader use only for read value from database u cannot use to insertvalue to database

     

     

    Regards

    palan

    Tuesday, September 25, 2007 9:34 AM
  • Hi palan,

    Many apologies and thank you for understanding what I am trying to do. In fact I did not have cmd2.ExecuteReader but rather cmd2.ExecuteNonQuery() command. I apologize for copying wrong code.

    Thanks for your solution.

    Best Regards,

    Chris
    Tuesday, September 25, 2007 9:41 AM