Drop down list not binding with sqldatareader

Question

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

where did you specify connection.open() & connection.close() — Shalem
– Shalem, Commented Sep 2, 2013 at 6:08

Suraj Singh · Accepted Answer · 2013-09-02 06:29:51Z

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.

edited Sep 2, 2013 at 6:29

answered Sep 2, 2013 at 6:21

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

C Sharper · Accepted Answer · 2013-09-02 05:55:50Z

0

This seems clear postback problem.

Bind your drop down on !postback.

Eg.

if(!IsPostBack)    
 {
   populateDdl();
 }

answered Sep 2, 2013 at 5:55

C Sharper

8,66430 gold badges96 silver badges158 bronze badges

Comments

Ehsan · Accepted Answer · 2013-09-02 05:59:47Z

0

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();

edited Sep 2, 2013 at 5:59

answered Sep 2, 2013 at 5:54

Ehsan

32.8k6 gold badges61 silver badges66 bronze badges

Comments

Noman Q · Accepted Answer · 2013-09-02 06:15:25Z

0

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.

answered Sep 2, 2013 at 6:15

Noman Q

111 bronze badge

Collectives™ on Stack Overflow

Drop down list not binding with sqldatareader

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related