How to use sql datareader to read multiple rows linked to a number from database base on textbox value c#

Question

When l enter the invoice number 1992 in textbox1 which is linked to 3 rows in the database, only the last row out of 3 is displayed. I want to populate all rows linked to a particular invoice number from the database as shown in the picture using SQL datareader. Below is the code l have tried, and it shows only one row instead of three.

private void button1_Click(object sender, EventArgs e)
{
    cmd = new SqlCommand("SELECT ProduceID, Produce, Category, UnitPrice, UnitsOnOrder, Weight, Discount, Total FROM ordertable WHERE InvoiceNumber = '" + textBox1.Text + "'", con);
    con.Open();

    cmd.ExecuteNonQuery();
    SqlDataReader DR;
    DR = cmd.ExecuteReader();
    while (DR.Read())
    {
        string ProduceID = (string)DR["ProduceID"].ToString();
        dataGridView1.Rows[0].Cells["ProduceID"].Value = ProduceID;
        string Produce = (string)DR["Produce"].ToString();
        dataGridView1.Rows[0].Cells["Produce"].Value = Produce;
        string Category = (string)DR["Category"].ToString();
        dataGridView1.Rows[0].Cells["Category"].Value = Category;
        string UnitPrice = (string)DR["UnitPrice"].ToString();
        dataGridView1.Rows[0].Cells["UnitPrice"].Value = UnitPrice;
        string UnitsOnOrder = (string)DR["UnitsOnOrder"].ToString();
        dataGridView1.Rows[0].Cells["UnitsOnOrder"].Value = UnitsOnOrder;
        string Weight = (string)DR["Weight"].ToString();
        dataGridView1.Rows[0].Cells["Weight"].Value = Weight;
        string Discount = (string)DR["Discount"].ToString();
        dataGridView1.Rows[0].Cells["Discount"].Value = Discount;
        string Total = (string)DR["Total"].ToString();
        dataGridView1.Rows[0].Cells["Total"].Value = Total;
    }
    con.Close();

What do you think will happen in a loop with dataGridView1.Rows[0].Cells..... ? hint what does Rows[0] do? — Igor
– Igor, Commented Mar 24, 2021 at 15:30
As a side note you could also look at binding the dataGridView1 instance which might be easier and have less code. You can usually do this in the designer but it depends on your IDE and your framework (win forms, wpf, asp.net, etc). — Igor
– Igor, Commented Mar 24, 2021 at 15:32
@Igor, I have tried binding it too, but the Column Total is not showing any value because it is throwing an exception Expression '*' not valid. Total Column is a calculated column of UnitPrice * UnitsOnOrder. The codes above is the only one that seems to show Total Column with its corresponding values — kan
– kan, Commented Mar 24, 2021 at 15:41

Boris Sokolov · Accepted Answer · 2021-03-24 15:39:16Z

1

It should be easier to bind the DataTable to your grid

private void button1_Click(object sender, EventArgs e)
{
    cmd = new SqlCommand("SELECT ProduceID, Produce, Category, UnitPrice, UnitsOnOrder, Weight, Discount, Total FROM ordertable WHERE InvoiceNumber = '" + textBox1.Text + "'", con);
    con.Open();

    cmd.ExecuteNonQuery();
    dt.Load(cmd.ExecuteReader());
    dataGridView1.DataSource = dt;
    con.Close();
}

P.S. If you want to have a manual mapping - probably you'll have to use some kind of counter

dataGridView1.Rows[counter]

answered Mar 24, 2021 at 15:39

Boris Sokolov

1,8134 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

kan Over a year ago

i tried your code but it's returning blank datagrid

Boris Sokolov Over a year ago

Try to split your code into the chunks. Test your query, etc. The code that you provided sets the grid in a wrong way. But I don't have any idea regarding your DB connection, etc.

kan Over a year ago

@ Boris, The code i provided above is the only method that i have tried that shows the Total columns value. The others including binding datagrid to datatable are not showing Total column value. I need assistance on how to use datareader to show all rows linked to a particular invoice number.

kan Over a year ago

this is my database connection SqlConnection con = new SqlConnection(@"Data Source=Local \winSERVER;Initial Catalog=Northwind;Integrated Security=True"); SqlCommand cmd;

Boris Sokolov Over a year ago

So your code shows only one line? Yes? Have you tried dataGridView1.Rows[counter]. You are modifying the only first row of your table

|

Collectives™ on Stack Overflow

How to use sql datareader to read multiple rows linked to a number from database base on textbox value c#

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related