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

        }

are you sure that reader returns rows? It has "HasRows" property to check before reading — vityanya, May 30 '11 at 17:30
what does 'x' represent? Is it a string representing a string? a number? You should get used to using parameters, concatenating strings to build SQL queries is dangerous, specially if 'x' is user dependant. — InBetween, May 30 '11 at 17:31
The field being queried won't accept nulls, so it's definitely returning rows @vityanya, and @InBetween, 'x' is a string used to store the jqueried submission id on the page. — Ace Troubleshooter, May 30 '11 at 18:19
Possible duplicate - http://stackoverflow.com/questions/6021207/invalid-attempt-to-call-read-when-reader-is-closed — vapcguy, Sep 10 '15 at 15:42

score 4 · Answer 1 · answered May 30 '11 at 17:31

4

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

answered May 30 '11 at 17:31

Leniel Maccaferri

100,159
46
371
480

`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... – vapcguy Sep 09 '15 at 23:27

score 3 · Answer 2 · answered Nov 08 '12 at 09:54

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.

score 3 · Accepted Answer · edited Nov 26 '15 at 09:21

3

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.
{
    ...
}

edited Nov 26 '15 at 09:21

Jimbo

22,379
42
117
159

answered May 30 '11 at 17:34

InBetween

32,319
3
50
90

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. – Ace Troubleshooter May 30 '11 at 19:43
@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. – InBetween May 30 '11 at 21:22
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 Sep 09 '15 at 22:11

score 1 · Answer 4 · answered May 30 '11 at 18:22

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

    }

score -1 · Answer 5 · edited Oct 18 '14 at 04:30

if (conn.State == ConnectionState.Closed)
    conn.Open();

    SqlCommand qr1 = new SqlCommand("select TransID, FType, FldName, LTrans, 
        OnCCBeforeLoad, LTop, LLeft, LWidth, LHeight, LFColor 
        from jwOndropExecButtonS where active = 'T' and MyType = ''  
        and LTrans = '" + TmpRajTransID + "' order by dbo.val(TransID) ", conn);
    SqlDataReader d1 = qr1.ExecuteReader();
    while (d1.Read())
    {
        if (d1.HasRows)
        {
            string MrFldName = d1["FldName"].ToString().Trim();

            if (d1["OnCCBeforeLoad"].ToString().Trim() == "Clr")
                ClearValueandToolTipforFieldName(MrFldName);
            if (d1["LTop"].ToString().Trim() != "")
                DisplaySetTopValue(MrFldName, d1["LTop"].ToString().Trim());
            if (d1["LLeft"].ToString().Trim() != "")
                DisplaySetLeftValue(MrFldName, d1["LLeft"].ToString().Trim());
            if (d1["LWidth"].ToString().Trim() != "")
                DisplaySetWidthValue(MrFldName, d1["LWidth"].ToString().Trim());
            if (d1["LHeight"].ToString().Trim() != "")
                DisplaySetHeightValue(MrFldName, d1["LHeight"].ToString().Trim());
            if (d1["FType"].ToString().Trim() == "Visible")
                ShowTextBoxWithFldName(MrFldName);
            if (d1["FType"].ToString().Trim() == "InVisible")
                HideandClearTextBoxWithFldName(MrFldName);
            if (d1["FType"].ToString().Trim() == "InNVisible")
                HideOnlyTextBoxWithFldName(MrFldName);
            if (d1["FType"].ToString().Trim() == "Enable")
                SetEnableforFieldName(MrFldName);
            if (d1["FType"].ToString().Trim() == "Disable")
                SetDisableforFieldName(MrFldName);
        }
    }
    d1.Close();
    if (conn.State == ConnectionState.Open)
        conn.Close();

I got the same error once in a while at

if (d1["FType"].ToString().Trim() == "Visible")

saying metadata not found. Later I found that the reader was not closed, but the ASP.NET program using too much memory closed the reader automatically. It mainly depends on the SQL server stage. If it is a regular error then please check your connection.

Invalid attempt to call MetaData when reader is closed?

5 Answers5

Linked