C# – There is already an open DataReader associated with this Command which must be closed first

cdatareader

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;
}

Best Answer

Your problem is that you apparently have one instance of m_ConStr; if the method is called concurrently only one of them will be able to use the connection and the other one will fail with the exception you are receiving.

Use this pattern instead:

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

In other words, don't define connection as a global variable to the class.

Related Topic