C# SqlDataReader reads only 1 row

Question

I'm trying to read data from database to store in arrays, but SqlDataReader only reads 1 row which is either the last or the first depending if I use ORDER BY DESC or ASC which seems to bug me.

Code:

private void chkAdr()
{
 string connStr = "Data Source=MARINCHI\\SQLEXPRESS;Initial Catalog=login1;Integrated Security=True";
 SqlConnection conn = new SqlConnection(connStr);
 conn.Open();
 SqlCommand getAdr = new SqlCommand("SELECT adress, floor,city, state, country, zipcode FROM userAdress where userID = @userID", conn);
 SqlParameter parUserID = new SqlParameter("@userID", Login.id);
 getAdr.Parameters.Add(parUserID);
 getAdr.ExecuteNonQuery();
 SqlDataReader dr = getAdr.ExecuteReader();
 string[] adress = new string[100];
 string[] floor = new string[100]; 
 string[] city = new string[100]; 
 string[] state = new string[100]; 
 string[] country = new string[100]; 
 string[] zipcode = new string[100]; 
 while (dr.Read())
 {
 int count = dr.FieldCount;
 for (int i = 0; i < count; i++)
 {
 adress[i] = dr["adress"].ToString();
 floor[i] = dr["floor"].ToString();
 city[i] = dr["city"].ToString();
 state[i] = dr["state"].ToString();
 country[i] = dr["country"].ToString();
 zipcode[i] = dr["zipcode"].ToString();
 }
 // test to see, what values I get, no matter what adress[i] for I any number, always get the last data from my table in sql...
 textBox5.Text = adress[0];
 textBox6.Text = floor[0];
 textBox7.Text = city[0];
 textBox8.Text = state[1];
 textBox9.Text = country[1];
 textBox10.Text = zipcode[1];
 }
 dr.Close();
}

I tried dr.HasRows but it didn't really work for me, and I searched on google and tried all combinations but nothing seems to work, I can't get it to work with reading from begging to last row..

This should be a component of my code to retrieve adress from database if a user added his address before, if it ends up true, its gonna be added to combobox as option "Saved address 1" and when u click on it, it should display it in textboxes. Same if there are 2 addresses, both should be added.

As I said my problem is I can't read all addresses the user has added as the SqlDataReader reads only either last row or first row only.. If anyone has a idea on how I can do this, please help! thanks!

• hardkoded: Why are you executing ExecuteNonQuery and then ExecuteReader?
• Rin: ExecuteNonQuery cuz im using parameters used in execution of sql command, and execute reader to read data from db and to be able to store em in array.
• hardkoded: You don't need to call getAdr.ExecuteNonQuery(), ExecuteReader is enough
• Rin: Ok, i will remove it, thanks for that, but still doesnt help me with my real problem of why it reads only one row, either last or first:(
• hardkoded: IMHO your code should work, could you doble check your data? test your query in SSMS.

Answer 1

You looping is not correct, you should read like this:

int i = 0;
while (dr.Read())
{ 
 adress[i] = dr["adress"].ToString();
 floor[i] = dr["floor"].ToString();
 city[i] = dr["city"].ToString();
 state[i] = dr["state"].ToString();
 country[i] = dr["country"].ToString();
 zipcode[i] = dr["zipcode"].ToString();
 i++;
}

• Rin: Noticed that, but still doesnt fix problem, I did it with for loop with i < 3 instead of i < count, cuz i have 3 rows of adresses in my DB table. :(
• Antonín Lejsek: You have for loop in the while loop, so you write the same row three times, rewriting everything you had there. You end up with the last row three times.
• Rin: Oh I see what the problem is now. Let me try that.