Invalid attempt to call MetaData when reader is closed?

Question

I'm running an if else statement off of a datareader to query table data and activate/de-activate some controls on a page. I implemented a using statement to automatically close the connection and the reader when I close the block, but I still get the reader is closed error on each of my if else statements. What's missing? Code below:

string comnt = "SELECT StatusId FROM Submission WHERE SubmissionId =" + x;


    using (SqlConnection editConn = new SqlConnection(connectionString))
    {
        editConn.Open();

        using (SqlCommand statCmd = new SqlCommand(comnt, editConn))
        {
            SqlDataReader dr = statCmd.ExecuteReader();
            dr.Read();
            if (dr.GetInt32(0) > 0)
            {
                PanelComment.Visible = true;
                PanelQuote.Visible = false;
                LnbFid.Visible = false;
                LnbCrim.Visible = false;
                LnbEo.Visible = false;
                LnbEpl.Visible = false;
                LnbNot.Visible = false;
                LnbPriv.Visible = false;
                LnbPub.Visible = false;

            }
            else
            {
                PanelComment.Visible = false;
            }

        } 

Solution 1

Your query is not getting any results back. Get used to the following construct if you are not sure if your query will return any data:

while (dr.Read()) //will return true while there is data to be read.
{
    ...
}

Solution 2

Try this way:

if (dr.HasRows)
{
     while (dr.Read())
     {
         if (dr.GetInt32(0) > 0)
         {
             ...
         }
     }
}

For more info, check this page:

Retrieving Data Using a DataReader

Solution 3

I faced this problem due to mismatch of reader name. i.e.

SqlCommand sqlmd = new SqlCommand();
SqlDataReader sqldr = sqlmd.ExecuteReader();
while (sqldr.Read())
{

    idd = (int)rdr["Id"];
}

later I replaced the code

 idd = (int)sqldr["Id"];

and the error was solved.

Solution 4

It seems the select statement returns no row and when you call dr.Read() for first time datareader will be closed imidiately for using data reader we always shows use if or while like what leniel said

   using (SqlCommand statCmd = new SqlCommand(comnt, editConn))
    {
        SqlDataReader dr = statCmd.ExecuteReader();
       if( dr.Read())
        if (dr.GetInt32(0) > 0)
        {
            PanelComment.Visible = true;
            PanelQuote.Visible = false;
            LnbFid.Visible = false;
            LnbCrim.Visible = false;
            LnbEo.Visible = false;
            LnbEpl.Visible = false;
            LnbNot.Visible = false;
            LnbPriv.Visible = false;
            LnbPub.Visible = false;

        }
        else
        {
            PanelComment.Visible = false;
        }

    }

View more solutions

Comments

[Ace Troubleshooter]

Thanks, @InBetween. I'm unsure why the query wouldn't be getting results back, as the field is non-null and a check on the db table reveals the data is there.

[InBetween]

@brazos: if the submission id is not numeric you might be missing '' in your query string. If it is numeric check for any localization issues.

[vapcguy]

Empty or not, this construct doesn't prevent the error. I've ran into it even when I was just doing a SELECT * FROM Authors kind of query, while in debug mode.

[vapcguy]

HasRows was sufficient to clear my error - didn't need the 2nd if. And for me, even though I was expecting a GUID, when I did dr.GetGuid(0) > 0, it threw an error because it was saved in the DB as a char(35) column instead of a uniqueidentifier column -- that would never pass the if test in that instance. So let the buyer beware on that one, though I admit I had a special case...