problem with sql datareader

vishnuvardhan diyyala

15y4.8k.NET

Hi every body......,
i got an error as " There is already an open DataReader associated with this Command which must be closed first." while executing the following code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace Telstra
{
    public partial class Form2 : Form
    {
        SqlConnection con = new SqlConnection("data source=system;user id=sa;password=chowdary;Initial Catalog=usermanagement");
        //string a,b;
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {

            String st = "select token_no,software_1,software_2 from tokendetails where temp_user_id='" + Class1 .b + "'";
            String st1 = "select token_no,software_1,software_2 from tokendetails where Shift1_emp='" + Class1 .b + "'";
            String st2 = "select token_no,software_1,software_2 from tokendetails where Shift2_emp='" + Class1 .b + "'";
            String st3 = "select DmtNo,DmtName from UserDetails where user_id=" + Class1 .d + "";
            SqlCommand cmd = new SqlCommand(st, con);
            SqlCommand cmd1 = new SqlCommand(st1, con);
            SqlCommand cmd2 = new SqlCommand(st2, con);
            SqlCommand cmd3 = new SqlCommand(st3, con);
            con.Open();

            SqlDataReader dr = cmd.ExecuteReader();
            SqlDataReader dr1 = cmd1.ExecuteReader();
            SqlDataReader dr2 = cmd2.ExecuteReader();
            SqlDataReader dr3 = cmd3.ExecuteReader();
            if (dr.Read())
            {
                dr3.Read();
                MessageBox.Show(" Temperory these details are available for You ");
                textBox1.Text = dr.GetValue(0).ToString();
                textBox2.Text = dr.GetValue(1).ToString();
                textBox3.Text = dr.GetValue(2).ToString();
                textBox4.Text = dr3.GetValue(0).ToString();
                textBox5.Text = dr3.GetValue(1).ToString();
                con.Close();
            }
            else if (dr1.Read())
            {
                dr3.Read();
                textBox1.Text = dr1.GetValue(0).ToString();
                textBox2.Text = dr1.GetValue(1).ToString();
                textBox3.Text = dr1.GetValue(2).ToString();
                textBox4.Text = dr3.GetValue(0).ToString();
                textBox5.Text = dr3.GetValue(1).ToString();
                con.Close();
            }
            else if (dr2.Read())
            {
                dr3.Read();
                textBox1.Text = dr2.GetValue(0).ToString();
                textBox2.Text = dr2.GetValue(1).ToString();
                textBox3.Text = dr2.GetValue(2).ToString();
                textBox4.Text = dr3.GetValue(0).ToString();
                textBox5.Text = dr3.GetValue(1).ToString();
                con.Close();
            }

        }
    }
}

please sugget me how to modify the above code to errorless.

Thanks in advance

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Micke Blomqvist15y ago
Hi again,

I wouldn't suggest using a dataadapter loading the data, it is and overkill approach as you just need the first record returned. The datareader is the fastes access to the record you want to display.

When using a dataadapter, you need to create both a dataadapter object and a dataset object.
Crish15y ago
use dataset for getting individual tables rows from stored procedure...

create stored procedure

select * from xyz
select * from abc
select * from ddd

use this three tables row for binding your text box values
Suthish Nair15y ago
why dont use single SqlDataAdapter for all select queries and fill into a Dataset.

using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
   using (DataSet ds = new DataSet())
   {
   sda.Fill(ds);
   if (ds.Tables.Count > 0)
   {
           if (ds.Tables[0].Rows.Count > 0)
          {
                   textBox1.Text = ds.Tables[0].Rows[0][0].ToString();
                textBox2.Text = ds.Tables[0].Rows[0][1].ToString();
           }
             if (ds.Tables[1].Rows.Count > 0)
            {
   textBox1.Text = ds.Tables[1].Rows[0][0].ToString();
   textBox2.Text = ds.Tables[1].Rows[0][1].ToString();
           }
           if (ds.Tables[2].Rows.Count > 0)
            {
   textBox1.Text = ds.Tables[2].Rows[0][0].ToString();
   textBox2.Text = ds.Tables[2].Rows[0][1].ToString();
           }
   }
    }
}
Crish15y ago
DataReader - data reader is Read only and Forward only - so once you open connection for reading data from database you must be close your connection before getting another row. use con.Close(); after first records read
Micke Blomqvist15y ago
Hi,

You are not able to create or execute a new datareader for the open connection until the Close() method for the DataReader is called. Modify your code to execute one reader at the time. Close it if Read() returns false, and then execute the next reader.

Read more on http://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.71).aspx