how to bind two control by one SqlDataReader

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

C#

ASP.NET

, +

I want to know that how to bind DropDownList and GridView by one SqlDataReaderobject?

C#

SqlDataReader dr = cmd.ExecuteReader();
              
               GridView1.DataSource = dr;
                SearchDropDownList.DataSource = dr;
                SearchDropDownList.DataValueField = "DesigID";
               GridView1.DataBind();
               SearchDropDownList.DataBind();
               SearchDropDownList.Items.Insert(0, new ListItem("Select Designation ID", "Default value"));
               dr.Close();

and i have tried following

C#

SqlDataReader dr = cmd.ExecuteReader();
            SqlDataReader dr1 = dr;
            GridView1.DataSource = dr;
             SearchDropDownList.DataSource = dr1;
             SearchDropDownList.DataValueField = "DesigID";
            GridView1.DataBind();
            SearchDropDownList.DataBind();
            SearchDropDownList.Items.Insert(0, new ListItem("Select Designation ID", "Default value"));
            dr.Close();

but it is filling only GridView1.

Posted 14-May-12 1:38am

Member 7909353

Add a Solution

5 solutions

Solution 1

I would suggest you create a custom web control in your ASP.NET application which would contain both the DropDownList and GridView objects.

In this control you should also place a suitable datasource from the data section of the toolbox such as ObjectDataSource.

Bind both objects to this dataSource, then place the custom control containing all of this in your page.

Thats how I would do it anyway.

Posted 14-May-12 1:52am

chris.obrien.07

Comments

Member 7909353 14-May-12 8:16am

Thanks,Your solution is correct but I want to use only sqldatareader.

Solution 3

try like this

C#

SqlDataReader dr ;//declare globally
SqlCommand cmd//declare globally

//In Button Click Write This
 cmd = New SqlCommand("select * from sample", con)

dr= cmd.ExecuteReader();
                
               GridView1.DataSource = dr;
 GridView1.DataBind();
 dr.Close()

      dr=cmd.ExecuteReader();
 SearchDropDownList.DataSource = dr;
SearchDropDownList.DataTextField="DesigID";
                SearchDropDownList.DataValueField = "DesigID";
  SearchDropDownList.DataBind();
              
             
               SearchDropDownList.Items.Insert(0, new ListItem("Select Designation ID", "Default value"));
               dr1.Close();

Posted 14-May-12 2:00am

vangapally Naveen Kumar

Updated 14-May-12 3:25am

v5

Comments

Member 7909353 14-May-12 8:15am

No this is not working.

Solution 4

You can not use DataReader in this way. Once you read data from it (first DataBind()) there is no way to "rewind" it and read data again.
You could call ExecuteReader() again, but that means new database access.
I suggest you use one of the approaches above (SqlDataSource, ObjectDataSource, DataTable...).

Posted 14-May-12 2:47am

sjelen

Updated 14-May-12 2:49am

v2

Solution 5

Ok,
If you absolutely need to use an SQL datareader use it with a datatable like so

VB

Dim dt As New DataTable
Dim dr As New System.Data.SqlClient.SqlDataReader
'TODO Fill datareader here
dt.Load(dr)

Use the datatable as the datasource for your page controls
This should work.

...BUT... its a bad idea to link to your page to your database directly.

Consider using an intermediary object like a business layer object.

Posted 14-May-12 8:31am

chris.obrien.07

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2012-05-14T01:59:00

Solution 2

Why not use DataTable? Fill it with DataAdapter and then use the DataTable with multiple controls as a DataSource.

Have a look at these knowledge-base:
MSDN: DataAdapter Parameters (ADO.NET)[^]
MSDN: Accessing Data with ADO.NET[^]

Posted 14-May-12 1:59am

Sandeep Mewara

Comments

Member 7909353 14-May-12 8:34am

Thanks,Your solution is correct but I want to use only sqldatareader.

Sandeep Mewara 14-May-12 9:02am

You cannot do it. DataReader is one way forward only thingy. Once it is used by one datasource, you cannot re-use the same one. Hence, my suggestion to use a datatable.