Question multiple DataReaders

Salam,

I am working on a project (windows application), I have used 2 SqlDataReaders in each event of 2 comboBoxes; where each comboBox display either the name of employees or their ID number.
I have also used TextChange event, so if I selected one of the ID the name appear in the other comboBox and vice versa.



this is the code:

Code:

private void comboBox2_TextChanged(object sender, EventArgs e)
        {
            SqlCommand Sc4 = new SqlCommand("select emp_id from employee where emp_fname +' '+ emp_sname +' '+ emp_lname='" + comboBox2.SelectedItem + "'", cn);

            if (cn.State == ConnectionState.Closed)
                cn.Open();
            SqlDataReader rdr = Sc4.ExecuteReader();
            if (rdr.Read())
            {
                comboBox3.Text = rdr["emp_id"].ToString().Trim();
                rdr.Close();
                cn.Close();
            }
            else
            {
                //MessageBox.Show("Not found");
                rdr.Close();
            }
        }

Code:

        private void comboBox3_TextChanged(object sender, EventArgs e)
        {
            SqlCommand Sc5 = new SqlCommand("select emp_fname+' '+ emp_sname+' '+ emp_lname as en from employee where emp_id=" + comboBox3.SelectedItem, cn);

            if (cn.State == ConnectionState.Closed)
                cn.Open();
            SqlDataReader rdr2 = Sc5.ExecuteReader();
            if (rdr2.Read())
            {
                comboBox2.Text = rdr2["en"].ToString().Trim();
                rdr2.Close();
                cn.Close();
            }
            else
            {
                //MessageBox.Show("Not found");
                rdr2.Close();
            }
        }

 

Using only one of the readers works perfectly, but when I use both of readers the following message appear:

There is already an open datareader associated with this command which must be closed first

FYI

I have tried to use different connection for each reader, also used MARS = True; in the connection string but non of the solutions worked.

Got any ideas?

Note: the picture is in the attachment

 

Hey,

I think a solution of your problem. Your problem is actually parallel text change event on a same table.
You can prevent it by using got focus or click event of each combo-box, just use a variable like isCombobox2Working when user clicks on combox2 assign isCombobox2Working = true and then in textchange event check if the isCombobox2Working = true then work. When user clicks on Combobox3 set isCombobox2Working = false and in TextChanged event for combox3 check to work only isCombobox2Working = false.

 

That is obvious, you are using same connection object between two different dataReaders.

You can try peterpaulrubens2011 way to run only one datareader at one time, or use two different connection objects for each datareader.

Code:

private void comboBox3_TextChanged(object sender, EventArgs e)
        {
            SqlCommand Sc5 = new SqlCommand("select emp_fname+' '+ emp_sname+' '+ emp_lname as en from employee where emp_id=" + comboBox3.SelectedItem, cn);

            if ([COLOR="Red"][B]cn[/B][/COLOR].State == ConnectionState.Closed)
                [COLOR="Red"][B]cn[/B][/COLOR].Open();
            SqlDataReader rdr2 = Sc5.ExecuteReader();
            if (rdr2.Read())
            {
                comboBox2.Text = rdr2["en"].ToString().Trim();
                rdr2.Close();
                [COLOR="Red"][B]cn[/B][/COLOR].Close();
            }
            else
            {
                //MessageBox.Show("Not found");
                rdr2.Close();
            }
        }