Why is ExecuteReader only giving me 1 row of data back?

Question

I have this code and its only returning the first string [0] and errors on the rest of them saying the index is out of the array which means only 1 row is getting pulled BUT I DON'T KNOW WHY!!!

MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = new MySqlCommand("SELECT email_address FROM account_info", connection);
MySqlDataReader reader;

try
{

    connection.Open();
    reader = command.ExecuteReader();
     if (reader.HasRows)
    {
        while (reader.Read())
        {
            textBox1.Text = reader[0].ToString();

            textBox2.Text = reader[0].ToString();

            textBox3.Text = reader[0].ToString();
        }


        reader.Close();
    }

score 5 · Answer 1 · answered Feb 15 '12 at 22:11

5

reader[0] accesses the first field from the reader, not the first row. Check out the sample code from MSDN.

// Call Read before accessing data.
while (reader.Read())
{
    Console.WriteLine(String.Format("{0}, {1}",
            reader[0], reader[1]));
}

This writes out the first and second columns of each row.

Also, I'm not really sure why you're not using a using statement, and why you're calling ExecuteReader in the finally block - those both look odd.

answered Feb 15 '12 at 22:11

dsolimano

8,870
3
48
63

I'm using windows forms how would it look instead of console.writeline? – Devin Prejean Feb 15 '12 at 22:15
Are you trying to display an unknown number of email addresses, or just the first three? If just the first three, just replace while loop with a for loop. If an unknown number, you'll need some sort of data grid, perhaps `DataGridView`, to do that. – dsolimano Feb 15 '12 at 22:20
i only need the first 3 i'm trying a for loop and aparently i don't understand the syntax it needs to be in – Devin Prejean Feb 15 '12 at 22:24

phoog · Accepted Answer · 2012-02-15T22:37:19.450

2

You're only getting one row because you're only calling reader.Read() once. Each time you call Read(), the reader advances to the next row and returns true; or, when the reader advances past the last row, it returns false.

The indexer returns data from additional columns, and you have only one column in your query; that's why index 1 and 2 are failing.

EDIT:

IF you're trying to loop through the reader, you need to put your three textboxes in a structure where they can be looped through as well. Simpler, but less flexible, but correct:

if (reader.HasRows) 
{ 
    reader.Read()
    textBox1.Text = reader[0].ToString(); 
    reader.Read()
    textBox2.Text = reader[0].ToString(); 
    reader.Read()
    textBox3.Text = reader[0].ToString(); 
    reader.Close(); 
}

more flexible:

List<TextBox> boxes = new List<TextBox> { textBox1, textBox2, textBox3 };
for (int index = 0; index < boxes.Count; index++)
{
    if (!reader.Read())
    {
        break;  // in case there are fewer rows than text boxes
    }
    boxes[index] = reader[0].ToString();
}

edited Feb 15 '12 at 22:37

answered Feb 15 '12 at 22:10

phoog

42,068
6
79
117

So i call it in between each text box? – Devin Prejean Feb 15 '12 at 22:16
@DevinPrejean that's one way to do it. Normally, `Read()` is called in a loop. (And note that it returns a boolean so you know when all the rows have been read.) In this case, since you're filling a definite number of controls, you can either call `Read()` repeatedly or put the controls into a collection so you can use a loop. – phoog Feb 15 '12 at 22:20
@DevinPrejean the index must always be `0` because the reader has only one column. Did you forget to change the `1` and the `2` to zeros? – phoog Feb 15 '12 at 22:30
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7764/discussion-between-phoog-and-devin-prejean) – phoog Feb 15 '12 at 22:31
@DevinPrejean I also added another example to show how the looping approach would work. – phoog Feb 15 '12 at 22:41

score 0 · Answer 3 · answered Feb 15 '12 at 23:43

Here's the basics of what I do, replace the string EmailAddress part with whatever you need:

        using (SqlConnection SQL_Conn01 = new SqlConnection(SQLSync))
        {
            bool IsConnected = false;
            try
            {
                SQL_Conn01.Open();
                IsConnected = true;
            }
            catch
            {
                // unable to connect
            }
            if (IsConnected)
            {

                SqlCommand GetSQL = new SqlCommand("SELECT email_address FROM account_info", SQL_Conn01);

                using (SqlDataReader Reader = GetSQL.ExecuteReader())
                {
                    while (Reader.Read())
                    {
                        string EmailAddress = Reader.GetString(0).TrimEnd();
                    }
                }
                GetSQL.Dispose();
            }
        }

Why is ExecuteReader only giving me 1 row of data back?

3 Answers3