Data reader already open problem

Question

1.00/5 (1 vote)

See more:

Hi all....!!!

i am checking values that exist in database through datareader and if the value exists then i am binding some other values from that table to datagrid.

The problem is that when it finds the value in database and enters if condition it says datareader already open and if i close the datareader then condition will be checked only once for the first time and then the connection will be terminated.

con.Open();
        SqlCommand cmd = new SqlCommand("Select Productcode, Status from Product", con);
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            string code = dr.GetValue(0).ToString();
            string status = dr.GetValue(1).ToString();
            if (status == "Active")
            {
               // dr.Close();
                SqlCommand cmd1 = new SqlCommand("Select Productcode, Name, MRP from Product where Productcode='" + code + "'", con);
                da = new SqlDataAdapter(cmd1);
                ds = new DataSet();
                da.Fill(ds);
                grid_pins.DataSource = ds;
                grid_pins.DataBind();
            }
        }

Posted 18-Nov-11 18:11pm

abhishekagrwl25

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RaisKazi · Answer 1 · 2011-11-18T18:27:00

Multiple issues with your code.

1) You can not execute SQLCommand when your DataReader is in open mode.

2) I guess you want records from Product Table which are having Status matching with 'Active'.

3) Try by changing your code as below.

C#

//Declaration and initialization of con here.

con.Open();

SqlCommand cmd1 = new SqlCommand("Select Productcode, Name, MRP from Product where Status='Active'", con);

cmd1.CommandType = CommandType.Text;

  da = new SqlDataAdapter(cmd1);
  ds = new DataSet();
  da.Fill(ds);
  grid_pins.DataSource = ds;
  grid_pins.DataBind();

Prince Antony G · Answer 2 · 2011-11-18T18:38:00

Why u use two queries..Use Single Query..

C#

SqlConnection con = new SqlConnection(con);
 SqlCommand cmd = new SqlCommand("Select Productcode, Name, MRP from Product where Status='Active'", con);
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 da.Fill(ds);
 grid_pins.DataSource = ds;
 grid_pins.DataBind();

ntsonawane · Answer 3 · 2011-11-18T18:52:00

insted of using sql reader for

SqlCommand cmd = new SqlCommand("Select Productcode, Status from Product", con);

use DataTable and close you connection using

cmd.Connection.close()

store result in datatable before closing Connection and use this datatable

foreach(DataRow dr in datatable.Rows)
{
// your 2nd query
// i.e. SqlCommand cmd1 = new SqlCommand("Select Productcode, Name, MRP from Product where Status='Active'", con);

}

jeet786 · Answer 4 · 2011-11-18T19:21:00

Solution 4

Hi,

I think, not sure, but you can use

MultipleActiveResultSets=True

in your connection string.that will solve your problem, that is used for more than one active result set.

Posted 18-Nov-11 19:21pm

jeet786

Updated 18-Nov-11 19:22pm

v2

Data reader already open problem

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0