ASP.NET SqlDataReader throwing error: Invalid attempt to call Read when reader is closed.

Posted by Bugget on Stack Overflow See other posts from Stack Overflow or by Bugget
Published on 2010-03-19T22:51:04Z Indexed on 2010/03/19 23:01 UTC
Read the original article Hit count: 295

Filed under:
|

This one has me stumped. Here are the relative bits of code:

    public AgencyDetails(Guid AgencyId)
    {
        try
        {
            evgStoredProcedure Procedure = new evgStoredProcedure();
            Hashtable commandParameters = new Hashtable();
            commandParameters.Add("@AgencyId", AgencyId);
            SqlDataReader AppReader = Procedure.ExecuteReaderProcedure("evg_getAgencyDetails", commandParameters);
            commandParameters.Clear();

            //The following line is where the error is thrown. Errormessage: Invalid attempt to call Read when reader is closed.
            while (AppReader.Read())
            {
                AgencyName = AppReader.GetOrdinal("AgencyName").ToString();
                AgencyAddress = AppReader.GetOrdinal("AgencyAddress").ToString();
                AgencyCity = AppReader.GetOrdinal("AgencyCity").ToString();
                AgencyState = AppReader.GetOrdinal("AgencyState").ToString();
                AgencyZip = AppReader.GetOrdinal("AgencyZip").ToString();
                AgencyPhone = AppReader.GetOrdinal("AgencyPhone").ToString();
                AgencyFax = AppReader.GetOrdinal("AgencyFax").ToString();
            }
            AppReader.Close();
            AppReader.Dispose();
        }
        catch (Exception ex)
        {
            throw new Exception("AgencyDetails Constructor: " + ex.Message.ToString());
        }
    }

And the implementation of ExecuteReaderProcedure:

    public SqlDataReader ExecuteReaderProcedure(string ProcedureName, Hashtable Parameters)
    {
        SqlDataReader returnReader;

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                SqlParameter param = new SqlParameter();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                foreach (DictionaryEntry keyValue in Parameters)
                {
                    cmd.Parameters.AddWithValue(keyValue.Key.ToString(), keyValue.Value);
                }

                conn.Open();
                returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message.ToString());
            }
        }
        return returnReader;
    }

The connection string is working as other stored procedures in the same class run fine. The only problem seems to be when returning SqlDataReaders from this method! They throw the error message in the title. Any ideas are greatly appreciated! Thanks in advance!

© Stack Overflow or respective owner

Related posts about ADO.NET

Related posts about c#