CODEWITHSUNDEEP
c#sql-serversqldatareader
Rin
Rin

Reputation: 81

C# SqlDataReader reads only 1 row

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!

Upvotes: 0

Views: 4538

Answers (1)

Anton&#237;n Lejsek
Anton&#237;n Lejsek

Reputation: 6103

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++;
}

Upvotes: 3

Related Questions