C# Retrieve ALL SQL Query Results

Question

else if (listBox1.SelectedIndex == 1)
{
    String sql2 = "SELECT FirstName, LastName FROM Players WHERE Team = 'Milwaukee Bucks'" +
                  "AND Number < 24";
    // command statement
    command = new SqlCommand(sql2, cnn);
    SqlDataReader reader = command.ExecuteReader();

    // Get table values
    if (reader.Read())
    {
        textBox1.Text = reader.GetString(0).ToString() + " " + reader.GetString(1).ToString();
    }
    cnn.Close();
    command.Dispose();
}

Above is a section of my code. I have a list box with options for the user to choose, and based on which item is selected, a display button will run a query and return the results to a textbox.

However, when I run the code I am only getting one result back. The query returns players on the Bucks who have a number less than 24. There should be multiple of them but I am only getting one in my C# application.

you need to do while(reader.Read()), but that will overwrite textBox1 on each loop iteration so I don't think that is the whole solution. — Crowcoder
– Crowcoder, Commented Jul 21, 2020 at 12:44
SqlDataReader does a secuential reading, so every call to reader.Read() will return the next record — Cleptus
– Cleptus, Commented Jul 21, 2020 at 12:44
This answer (How do I fill a DataTable using DataReader) gives an example of an alternative method for loading all the data — JayV
– JayV, Commented Jul 21, 2020 at 12:46
By the way, if you have an if else for each item in the list then you are doing it wrong. You can dynamically get the selected list item and use its value as a parameter in your query. — Crowcoder
– Crowcoder, Commented Jul 21, 2020 at 12:48
I do have multiple if else's. I had no clue how to do this more efficiently, can you please explain more? — Kajin
– Kajin, Commented Jul 21, 2020 at 12:53

Jawad · Accepted Answer · 2020-07-21 12:45:06Z

4

You need to use a while loop to read all the lines instead of reading a single line (via Read()).

Microsoft Documentation has an example of how to use the while loop.

StringBuilder sb = new StringBuilder()
while (reader.Read())
{
    sb.AppendLine(reader.GetString(0).ToString() + " " + reader.GetString(1).ToString());
}
textBox1.Text = sb.ToString();

answered Jul 21, 2020 at 12:45

Jawad

11.4k3 gold badges28 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

C# Retrieve ALL SQL Query Results

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related