Invali attempt to call metadata when reader is closed c# winform

Question

This is the edited code I removed try catch to see where i am getting the exceptions. I alse marked where I edited the code. This code is getting on my nerves I have been googling this problem and tried everything i found. I checked every connection other then this code there seems to be no problem with them. each and every connection is closed.

    queryString = "SELECT * FROM product WHERE prd_code = @c OR prd_name=@pn ";
    SqlCommand command = new SqlCommand(queryString, con);
    command.Parameters.AddWithValue("@c", Convert.ToInt32(txtpCode.Text));
    command.Parameters.AddWithValue("@pn", txtpName.Text.ToString());
    con.Open();
    //edited here
    using (SqlDataReader dr = command.ExecuteReader())
    {
    // and here
    if (dr != null && fr.HasRows && dr.Read() == true)
    {
        if (txtpCode.Text == "")
        {
            txtpCode.Text = dr["prd_code"].ToString();
        }
        else if (txtpName.Text == "")
        {
            txtpName.Text = dr["prd_name"].ToString();
        }            
        txtpCompany.Text = dr["prd_company"].ToString();
        txtUnitPrice.Text = dr["prd_price"].ToString();
        txtDiscount.Text = dr["prd_dis"].ToString();
        txtFinalRate.Text = dr["prd_final"].ToString();
        } 
        else
        {
            MessageBox.Show("No such record exists");

            if (txtpName.Text == "")
            {
                txtpCode.Text = "";
            }
            else if (txtpCode.Text == "")
            {
                 txtpName.Text = "";
            }
            con.Close();
        }

    }
    }

Use [`using` statement](http://msdn.microsoft.com/en-us/library/yh598w02.aspx) to dispose your `SqlConnection`, `SqlCommand` and `SqlDataReader`. — Soner Gönül, Apr 14 '14 at 10:09
@Damith I get exception on this line txtFinalRate.Text = dr["prd_final"].ToString(); — Ryan, Apr 14 '14 at 10:25

score 1 · Answer 1 · edited May 23 '17 at 12:27

  using (var reader = command.ExecuteReader())
      {
           if (reader.HasRows)
              {
                 if(reader.Read())
                 {

                   if (txtpCode.Text == "")
                   {
                       txtpCode.Text = dr["prd_code"].ToString();
                   }
                   else if (txtpName.Text == "")
                   {
                       txtpName.Text = dr["prd_name"].ToString();
                   }
                   txtpCompany.Text = dr["prd_company"].ToString();
                   txtUnitPrice.Text = dr["prd_price"].ToString();
                   txtDiscount.Text = dr["prd_dis"].ToString();
                   txtFinalRate.Text = dr["prd_final"].ToString();
                }
            }
      }

Have a look at What is the C# Using block and why should I use it?

+1 for showing `using`. The only thing is that he does a one-time read, not read in a loop, so the `if (reader.HasRows) { while (reader.Read())` should be replaced by `if (reader.Read())`. — Thorsten Dittmar, Apr 14 '14 at 11:35
Actually you can remove the `reader.HasRows` entirely now ;-) — Thorsten Dittmar, Apr 14 '14 at 11:40

score 0 · Answer 2 · edited May 23 '17 at 12:13

You should update your finally block with:

finally
{
    dr.close();
    con.Close();
}

Also, you should look t other SO and other forum threads where similar issues have been discussed already.

invalid-attempt-to-call-metadata-when-reader-is-closed

InvalidplusattemptplustopluscallplusMetaDatapluswh

EDIT:

It is always good to release resources in a finally block. I would recommend to to make dr declaration outside try catch and release it in finally block.

It is also possible that you query is not returning any record so use short-circuit technique to avoid this error:

if (dr != null && dr.HasRows && dr.Read() == true)
{
    // TODO : do your stuff here
}

EDIT:

Look at the connection and reader close lines:

queryString = "SELECT * FROM product WHERE prd_code = @c OR prd_name=@pn ";
SqlCommand command = new SqlCommand(queryString, con);
command.Parameters.AddWithValue("@c", Convert.ToInt32(txtpCode.Text));
command.Parameters.AddWithValue("@pn", txtpName.Text.ToString());
con.Open();

using (SqlDataReader dr = command.ExecuteReader())
{
    if (dr != null && dr.HasRows && dr.Read() == true)
    {
        if (txtpCode.Text == "")
        {
            txtpCode.Text = dr["prd_code"].ToString();
        }
        else if (txtpName.Text == "")
        {
           txtpName.Text = dr["prd_name"].ToString();
        }

        txtpCompany.Text = dr["prd_company"].ToString();
        UnitPrice.Text = dr["prd_price"].ToString();
        txtDiscount.Text = dr["prd_dis"].ToString();
        txtFinalRate.Text = dr["prd_final"].ToString();
    } 
    else
    {
        MessageBox.Show("No such record exists");

        if (txtpName.Text == "")
        {
            txtpCode.Text = "";
        }
        else if (txtpCode.Text == "")
        {
             txtpName.Text = "";
        }
     }  

    dr.Close();   // <-------------------- Look here
    con.Close;    // <-------------------- and here
}

Thankyou your edit works but for only 2 to 3 times i search then there is the same exception and after that i just keep searching and nothing happens no exception nothing. — Ryan, Apr 14 '14 at 10:14
I played around a bit and now the exception only occurs when data is found other then that it just shows the error message in the else block. — Ryan, Apr 14 '14 at 11:30

score 0 · Answer 3 · answered Nov 02 '22 at 14:18

0

I was able to fix issue by adding MultipleActiveResultSets=True to connection issue. For more info refer to: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/multiple-active-result-sets-mars?redirectedfrom=MSDN

answered Nov 02 '22 at 14:18

SharpCoder

18,279
43
153
249

Invali attempt to call metadata when reader is closed c# winform

3 Answers3