Using multiple datareaders

Question

hi im making a code wherein all the database values from different tables appear in one form provided below:

NpgsqlConnection conn = new NpgsqlConnection(connstring);
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM identification; SELECT * FROM height; SELECT * FROM weight; SELECT * FROM bloodpressure  WHERE eid like '" + textBox1.Text + "%'", conn);
conn.Open();

using (NpgsqlDataReader dr = cmd.ExecuteReader())
{
    while (dr.Read())
    {       
        textBox3.Text = (dr["lastname"].ToString());
        textBox4.Text = (dr["firstname"].ToString());
        textBox2.Text = (dr["middlename"].ToString());
        textBox9.Text = (dr["sex"].ToString());
        textBox5.Text = (dr["birthdate"].ToString());
        textBox6.Text = (dr["age"].ToString());
        textBox10.Text = (dr["department"].ToString());
        textBox7.Text = (dr["address"].ToString());
        textBox11.Text = (dr["status"].ToString());
        textBox8.Text = (dr["contact"].ToString());
    }

    if (dr.NextResult())
    {
        while (dr.Read())
        {
            textBox12.Text = (dr["height"].ToString());
        }
    }

    if (dr.NextResult())
    {
        while (dr.Read())
        {
            textBox15.Text = (dr["weight"].ToString());
        }
    }

    if (dr.NextResult())
    {
        while (dr.Read())
        {
            textBox16.Text = (dr["systole"].ToString());
            textBox17.Text = (dr["diastole"].ToString());
        }
    }

the code runs however only the values on the former eid appears whenever i tried to type another eid. what should i do so that all the values of a selected eid appears on the form?

Is the using statement closed after the last if? If not it may be something to do with a connection being left open and the UI not able to update until its closed. I.e when a new connection is made. — Lex Webb
– Lex Webb, Commented Sep 10, 2015 at 14:47
Important: you should parameterize that - it is currently very unsafe. Have you done anything like suspend UI updates / binding / drawing? — Marc Gravell
– Marc Gravell, Commented Sep 10, 2015 at 14:50
What triggers this code to run? Have you tried running it in a debugger to verify that it is getting the expected value in textBox1.Text? — Jack A.
– Jack A., Commented Sep 10, 2015 at 14:55
Code shown is unlikely related to problem you see - which is most likely caused by listening to wrong event. You should be able to replace all this code with constant values (like textBox1.Text + "weight") for debugging and still see the same effect. Please simplify code in the post and add missing pieces (this will also avoid embracing SQL injection code from being ridiculed). — Alexei Levenkov
– Alexei Levenkov, Commented Sep 10, 2015 at 14:59

Royi Namir · Accepted Answer · 2015-09-10 16:30:58Z

Your code is fine.

You have a problem somewhere else (Triggers , postback etc , maybe you have a n event order problem - try run it at the pre_render event ).

Looking at a real CITIES table (which contains 4 rows):

Running your exact code (with sql-server provider):

void Main()
{
    SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=aaa;Persist Security Info=True;User ID=-----;Password=------+");
            SqlCommand cmd = new SqlCommand(
                                @"SELECT * FROM cities; 
                                SELECT city as secondBulkCity FROM cities; 
                                SELECT  city as thirdBulkCity from cities; 
                                SELECT  city as fourthBulkCity from cities  WHERE city like '" + "new" + "%'", conn);
            conn.Open();

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
            "".Dump("first bulk");
                while (dr.Read())
                {       
                 dr["city"].Dump();
                }

            "".Dump("second bulk");
                 if (dr.NextResult())
                {
                    while (dr.Read())
                    {
                        dr["secondBulkCity"].Dump();
                    }
                }

           "".Dump("third bulk");
                if (dr.NextResult())
                {
                    while (dr.Read())
                    {
                       dr["thirdBulkCity"].Dump();
                    }
                }
            "".Dump("fourth bulk");
                if (dr.NextResult())
                {
                    while (dr.Read())
                    {
                       dr["fourthBulkCity"].Dump();
                    }
                }
}


}

Yields this :

Your problem is not in this specific code you've shown us.

Seth Kitchen · Accepted Answer · 2015-09-10 14:55:15Z

0

Make cmd a global variable.

Generate a function for when the EID textbox is changed by the user. Usually done by double clicking the textbox in the design view. In this function you should update the cmd to incorporate the new eid. Then call your GUI update function.

NpgsqlCommand cmd;

[OnTextboxUpdate]
{
 cmd=new NpgsqlCommand("SELECT * FROM identification; SELECT * FROM height; SELECT * FROM weight; SELECT * FROM bloodpressure  WHERE eid like '" + textBox1.Text + "%'", conn);
 updateGUI();
}


[updateGUI]
{
   using (NpgsqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {       
                textBox3.Text = (dr["lastname"].ToString());
                textBox4.Text = (dr["firstname"].ToString());
                textBox2.Text = (dr["middlename"].ToString());
                textBox9.Text = (dr["sex"].ToString());
                textBox5.Text = (dr["birthdate"].ToString());
                textBox6.Text = (dr["age"].ToString());
                textBox10.Text = (dr["department"].ToString());
                textBox7.Text = (dr["address"].ToString());
                textBox11.Text = (dr["status"].ToString());
                textBox8.Text = (dr["contact"].ToString());
            }

            if (dr.NextResult())
            {
                while (dr.Read())
                {
                    textBox12.Text = (dr["height"].ToString());
                }
            }

            if (dr.NextResult())
            {
                while (dr.Read())
                {
                    textBox15.Text = (dr["weight"].ToString());
                }
            }

            if (dr.NextResult())
            {
                while (dr.Read())
                {
                    textBox16.Text = (dr["systole"].ToString());
                    textBox17.Text = (dr["diastole"].ToString());
                }
            }
 }

answered Sep 10, 2015 at 14:55

Seth Kitchen

1,5661 gold badge21 silver badges55 bronze badges

3 Comments

newbie Over a year ago

i already generated it on a button click so i think it would already cause an exception if ill generate another

Seth Kitchen Over a year ago

Can you post the surrounding function? I am not sure when this function is being called?

newbie Over a year ago

private void button1_Click(object sender, EventArgs e) { (the code above)... conn.Close(); }

Collectives™ on Stack Overflow

Using multiple datareaders

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related