Drop down list not binding with sqldatareader

i have a form with a collection of about five drop down . i have my query as follows .

 string sql = "SELECT a.clientID ,a.[cname],b.bid,b.[bname],c.contactID, c.[name] FROM "
                         + " dbo.[CLIENT] AS a INNER JOIN dbo.[BRANCH] AS b "
                         + "ON a.clientID = b.clientID JOIN dbo.[CONTACT] AS "
                         + " c ON b.bid = c.bid ORDER BY a.clientID ";

i then followed and bind my drop down individually to their respective columns as follows.

    SqlCommand cmd = new SqlCommand(sql, connection);
    cmd.CommandType = CommandType.Text;


    SqlDataReader reader = cmd.ExecuteReader();




        drClient.Enabled = true;
        drClient.DataSource = reader;
        drClient.DataTextField = "cname";
        drClient.DataValueField = "clientID";
        drClient.DataBind();

        drBranch.Enabled = true;
        drBranch.DataSource = reader;
        drBranch.DataTextField = "bname";
        drBranch.DataValueField = "bid";
        drBranch.DataBind();

        drContact.Enabled = true;
        drContact.DataSource = reader;
        drContact.DataTextField = "name";
        drContact.DataValueField = "contactID";

        drContact.DataBind();


        drEmail.Enabled = true;
        drEmail.DataSource = reader;
        drEmail.DataTextField = "name";
        drEmail.DataValueField = "contactID";
        drEmail.DataBind();


        drFax.Enabled = true;
        drFax.DataSource = reader;
        drFax.DataValueField = "contactID";
        drFax.DataTextField = "name";
        drFax.DataBind();

when i run this, only the first drop down bind successfully. The rest don’t. I also try to loop through the reader by adding

while(reader.read())
{
then my bindings
}

the above also fails. I though of looping as below as well.

while(read.HasRows)
{

}

it still fails. I am confused,any help would be appreciated. thanks

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Reader is readonly and forward only that’s why only first dropdonw get filled with data and others are empty.
You can use datset or Datatable for same problem .

  SqlCommand cmd = new SqlCommand(sql, connection);
    cmd.CommandType = CommandType.Text;


    Dataset dsresult = cmd.ExecuteDataset();
   If(dsResult !=null)
   {
     if(dsResult.Rows.count>0) 
    {
    drClient.Enabled = true;
    drClient.DataSource = dsResult.Tables[0] ;
    drClient.DataTextField = Convert.ToString(ds.Tables[0].Columns["cname"]);
    drClient.DataValueField = ds.Tables[0].Columns["clientID"] ;
    drClient.DataBind();

    }

   }

Datareader is connected architecture needs continuous connection and fetches one row at a time in forward mode better use dataset which uses disconnected architecture and can be used for retrieving data multiple times.

Method 2

This seems clear postback problem.

Bind your drop down on !postback.

Eg.

if(!IsPostBack)    
 {
   populateDdl();
 }

Method 3

Either you will have to make a seperate reader for each binding

or you can do this by filling a datatable ( i would prefer this). Like,

DataTable dt = new DataTable();
using (SqlDataAdapter a = new SqlDataAdapter(sql, connection))
{       
   a.Fill(dt);
}
drClient.DataSource = dt;
drClient.DataBind();
drBranch.DataSource = dt;
drBranch.DataBind();
drContact.DataSource = dt;
drContact.DataBind();
drFax.DataSource = dt;
drFax.DataBind();

Method 4

Your choices are to either rerun/refill it or create separate readers or better yet fill a datatable instead and then you can reuse the datatable.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0