CODEWITHSUNDEEP
c#sqlasp.netsql-server
Abdullah
Abdullah

Reputation: 63

querying column from database and bind it

con.Open();

SqlCommand com,com2;
string str5 = "SELECT count(Name) as count from [Committee].[dbo].[Supervisor] ;";

com2 = new SqlCommand(str5, con);

SqlDataReader reader4 = com2.ExecuteReader();
int count= 0;

if (reader4.Read())
{
    string s = reader4["count"].ToString();
    count = int.Parse(s.Trim());
    reader4.Close();
    con.Close();
 }

 con.Open();

 string str = "SELECT Name from [Committee].[dbo].[Supervisor] ;";
 com = new SqlCommand(str, con);
 SqlDataReader reader = com.ExecuteReader();

 string  [] list;
 list = new string [count] ;

 if (reader.Read())
 {
     for (int i = 0; i < count; i++)
     {
         list[i] = reader["Name"].ToString(); // so here I got the problem
     }

     reader.Close();
     con.Close();
 }

 DropDownList1.DataSource = list;
 DropDownList1.DataBind();

Hello guys, I have this code I'm retrieving a column "Name" from database And I want to bind it in DropdownList1. It works but, I'm getting the only first row from the column. The question is how to save it in the array row by row and bind it correctly?

Upvotes: 0

Views: 43

Answers (3)

Arash
Arash

Reputation: 885

The problem is that you should use while for reader instead of If

    int i=0;
    while(reader.Read())
     {
         list[i] = reader["Name"].ToString(); 
         i++;
     }
     reader.Close();
     con.Close();

Upvotes: 1

Stefan
Stefan

Reputation: 17688

Try this:

List<string> list = new List<string>();
while (reader.Read())
{
    list.Add(reader["Name"].ToString());
}

reader.Close(); 
con.Close();

This way you don't need the count query because the reader will execute aslong there are rows to return.

ps: If somehow you need an array, you can use the list.ToArray() method.

Upvotes: 2

Dave Zych
Dave Zych

Reputation: 21897

Every time SqlDataReader.Read is called it advances the record to the next row, however you're only calling it once which means each time you access reader["Name"] you're getting the first row. Instead of doing an if/for you should be calling it in a while:

var list = new List<string>();
while(reader.Read())
{
    list.Add(reader["Name"].ToString());
}

It's also probably easier if you use a List<string> instead of an array as shown above.

Upvotes: 2

Related Questions