Click here to Skip to main content
13,146,498 members (49,962 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi All,

While retrieving data from database getting this error "Invalid attempt to call MetaData when reader is closed". (Windows application with c# coding)

 

        private void textBox10_TextChanged(object sender, EventArgs e)
        {
            //Retrieve Antenatal Details 
            //Here textbox10 = PersonID field in antenatal form.
            SqlConnection con = new SqlConnection(strConn);
            con.Open();
            SqlCommand cmd = new SqlCommand("sp_get_antenatal", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@person_id", textBox10.Text);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                cbPlaceoforder.SelectedValue = dr["antenatal_care_id"].ToString();
                txtAgeofantenatalmother.Text = dr["age"].ToString();
                if (dr["regular_anc"].ToString().Equals("Yes"))
                {
                    rbtnRegularancyes.Select();
                }
                else
                {
                    rbtnRegularancno.Select();
                }
            }
            con.Close();
            //Retrieve Lactating details
            con.Open();
            SqlCommand cmd1 = new SqlCommand("sp_get_lactating", con);
            cmd1.CommandType = CommandType.StoredProcedure;
            cmd1.Parameters.AddWithValue("@person_id", textBox10.Text);
            SqlDataReader dr1 = cmd1.ExecuteReader();
            if (dr1.Read())
            {                
                cbPlaceofanc.SelectedValue = dr1["delivery_place_id"].ToString();
                cbTypeofdelivery.SelectedValue = dr1["delivery_type_id"].ToString();
                cbMilksupplement.SelectedValue = dr1["milk_suppliment_id"].ToString();
                if (dr["breast_feeding"].ToString().Equals("Yes"))
                {
                    rbtnBreastfeeding1stsixmonthsyes.Select();
                }
                else
                {
                    rbtnBreastfeeding1stsixmonthsno.Select();
                }
            }
            con.Close();                
        } 
 


How to resolve this issue?

Thanks and Regards,
Murali.K
Posted 2-Jan-13 0:22am
Comments
jibesh 2-Jan-13 6:36am
   
which line you are experiencing this error? post the exception stack for better understanding. As explained its always better to close the dataReader when you are done with the data Reader.
muralikrishna53 2-Jan-13 6:45am
   
if (dr["breast_feeding"].ToString().Equals("Yes"))

Above line the error getting.
jibesh 2-Jan-13 7:02am
   
datareader dr is already closed and you are trying to access a closed connection. check my solution.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

hi,

Use this
SqlDataReader dr = cmd.ExecuteReader();
if (dr!=null && dr.HasRows)
{
  While(dr.Read())
  {
 
   // Do what ever you want
  }
  dr.Close();
}
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hi,

Here in the following code you are accessing the data reader and not closing it explicitly.
if (dr.Read())
            {
                cbPlaceoforder.SelectedValue = dr["antenatal_care_id"].ToString();
                txtAgeofantenatalmother.Text = dr["age"].ToString();
                if (dr["regular_anc"].ToString().Equals("Yes"))
                {
                    rbtnRegularancyes.Select();
                }
                else
                {
                    rbtnRegularancno.Select();
                }
            }
            dr.Close();
            con.Close();


So change the code by closing the dara reader before the con object is closed.
This will resolve your problem.

Thanks
  Permalink  
v2
Comments
muralikrishna53 2-Jan-13 6:55am
   
I tried like you, but getting same error in below line.
if (dr["breast_feeding"].ToString().Equals("Yes"))
sisir patro 2-Jan-13 6:59am
   
Keep your dr["breast_feeding"].ToString() value in a variable and then use it in the lower block. The error is comming only due to the datareader dr is closed. So change your code accordingly.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

You are trying to access the reader value on a closed object. i.e

Quote:
if (dr["breast_feeding"].ToString().Equals("Yes"))
is called after the prev connection is closed. what you can do here is copy this value into a local variable and use that here instead of using the dataReader.

bool breastFeeding = false;
if (dr.Read())
 {
      cbPlaceoforder.SelectedValue = dr["antenatal_care_id"].ToString();
      txtAgeofantenatalmother.Text = dr["age"].ToString();
      if (dr["regular_anc"].ToString().Equals("Yes"))
      {
         rbtnRegularancyes.Select();
      }
       else
       {
         rbtnRegularancno.Select();
        }
      breastFeeding = dr["breast_feeding"].ToString().Equals("Yes")?true:false;
   }
  con.Close();


then use it like below
if (dr1.Read())
{
        cbPlaceofanc.SelectedValue = dr1["delivery_place_id"].ToString();
        cbTypeofdelivery.SelectedValue = dr1["delivery_type_id"].ToString();
        cbMilksupplement.SelectedValue = dr1["milk_suppliment_id"].ToString();
        if (breastFeeding)
        {
               rbtnBreastfeeding1stsixmonthsyes.Select();
          }
         else
           {
               rbtnBreastfeeding1stsixmonthsno.Select();
            }          
 }
  Permalink  
Comments
muralikrishna53 2-Jan-13 7:52am
   
This time getting error below line.
breastFeeding = dr["breast_feeding"].ToString().Equals("Yes")?true:false;

"IndexOutof range exception was unhanded".
jibesh 2-Jan-13 7:54am
   
can you update your code using Improve Question link. post the updated code. so that we can see what went wrong.
muralikrishna53 2-Jan-13 23:27pm
   
I have modified my code like you.

private void textBox10_TextChanged(object sender, EventArgs e)
{
bool breastFeeding = false;
//Retrieve Antenatal Details
//Here textbox10 = PersonID field in antenatal form.
SqlConnection con = new SqlConnection(strConn);
con.Open();
SqlCommand cmd = new SqlCommand("sp_get_antenatal", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@person_id", textBox10.Text);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
cbPlaceoforder.SelectedValue = dr["antenatal_care_id"].ToString();
txtAgeofantenatalmother.Text = dr["age"].ToString();
if (dr["regular_anc"].ToString().Equals("Yes"))
{
rbtnRegularancyes.Select();
}
else
{
rbtnRegularancno.Select();
}
breastFeeding = dr["breast_feeding"].ToString().Equals("Yes") ? true : false;
}
//dr.Close();
con.Close();
//Retrieve Lactating details
con.Open();
SqlCommand cmd1 = new SqlCommand("sp_get_lactating", con);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@person_id", textBox10.Text);
SqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read())
{
cbPlaceofanc.SelectedValue = dr1["delivery_place_id"].ToString();
cbTypeofdelivery.SelectedValue = dr1["delivery_type_id"].ToString();
cbMilksupplement.SelectedValue = dr1["milk_suppliment_id"].ToString();
if (dr["breast_feeding"].ToString().Equals("Yes"))
{
rbtnBreastfeeding1stsixmonthsyes.Select();
}
else
{
rbtnBreastfeeding1stsixmonthsno.Select();
}
}
//dr1.Close();
con.Close();
}

Error: breast_feeding "Index out of Range Exception was unhandled.
jibesh 2-Jan-13 23:49pm
   
did you check 'breast_feeding' field is being selected or available in the selected list? check for any misspell.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.170915.1 | Last Updated 2 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100