1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Question multiple DataReaders

Discussion in 'C#' started by Nuha_IT, May 25, 2011.

  1. Nuha_IT

    Nuha_IT New Member

    Joined:
    May 25, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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();
                }
            }
    
    
     
  2. Nuha_IT

    Nuha_IT New Member

    Joined:
    May 25, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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
     

    Attached Files:

  3. peterpaulrubens2011

    peterpaulrubens2011 New Member

    Joined:
    Jun 20, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  4. tzuhsun

    tzuhsun New Member

    Joined:
    Jun 22, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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();
                }
            }
     

Share This Page