There is already an open DataReader associated with this Command, which should be closed first

Question

This is the code I have.

/// <summary> /// Method calls stored procedure and fills DataSet of contacts associated with Lead /// </summary> /// <param name="leadID">The ID associated with a Lead</param> /// <returns>contacts list as DataSet</returns> public static DataSet GetContactResultSetByLead(int leadID) { SqlCommand Sqlmd = new SqlCommand("dbo.proc_contact"); Sqlmd.CommandType = CommandType.StoredProcedure; Sqlmd.Parameters.Add("@LeadInfoID", SqlDbType.Int).Value = leadID; Sqlmd.Connection = m_ConStr; SqlDataAdapter da = new SqlDataAdapter(Sqlmd); DataSet data = new DataSet(); try { da.Fill(data); } finally { m_ConStr.Close(); } return data; } 

Answer 1

Your problem is that you apparently have one instance of m_ConStr ; if the method is called at the same time, only one of them will be able to use the connection, and the other will fail with the exception that you get.

Use this template instead:

 using (SqlConnection conn = new SqlConnection()) { conn.Open(); Sqlmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(Sqlmd); //...etc } 

In other words, do not define the connection as a global variable for the class.

Answer 2

I suggest you use a block to ensure proper sqlconnection placement.

 using (SqlConnection conn = new SqlConnection()) { conn.Open(); Sqlmd.Connection = conn; SqlDataAdapter da = new SqlDataAdapter(Sqlmd); Dataset ds = new Datasest da.Fill(ds) } 

otherwise, you can also set the MARS property in your connection if you need to.

 SqlConnection m_ConStr;= new SqlConnection("Server= serverName;Database=yourDatabase; MultipleActiveResultSets=true;"); 

Answer 3

All your short IDisposable objects are missing "use". So maybe you did something like:

 var reader = anotherCommand.ExecuteReader(); ... 

But this does not leave / close the reader. If so, add "use":

 using(var reader = anotherCommand.ExecuteReader()) { ... } 

Which closes the reader, no matter how we exit. Commands, connections, readers, and transactions are all one-time, and they all typically use "use."

Answer 4

You are trying to run many actie result sets (aka MARS ).

Two possible solutions come to mind:

• Open a new connection in your GetContractResultSetByLead
• Enable MARS on the database server (described in the link above).