Can the DataReader bind to DropDownList control ?

[Hean]

Hi all,
I need help on my ASP.NET Web Application.
Can the DataReader bind to DropDownList control ?
The following code give System.Data.Common.DBDataRecord error.

Dim cnn As New OleDb.OleDbConnection(strConnTeamPE)
cnn.Open()
strSQL = "SELECT sTeamID FROM TeamMaster"
Dim cmd As New OleDb.OleDbCommand(strSQL, cnn)
Dim dr As OleDb.OleDbDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Bind Data Reader to DropDownList
ddlTeamName.DataSource = dr
ddlTeamName.DataBind()

'Do While dr.Read
' ddlTeamName.Items.Add(dr("sTeamID"))
'Loop

If I replace last 2 lines with Do..While Loop then it's work.
So, I wonder is DataReader able for DataBind method ?
Many books does not teach binding controls with DataReader.(They used Do..While Loop or DataSet method)
But according to 4GuysFromRolla.com, it can be done. (Pls see the acticles below)
http://aspnet.4guysfromrolla.com/art...32702-1.2.aspx

Please advice.

Thanks.

Regards,
Hean

[DevGrp]

You dont need the do while loop. Once you set the ddlTeamName datasource and databind, thats all you need to do. You would only use the do while loop if you were'nt databinding.

[Hean]

This is what I did, but it give me give System.Data.Common.DBDataRecord error.
Any ideas ?
Thanks.

Regards,
Hean

[Edneeis]

I'm almost positive you can't bind to a datareader, because its a forward only kind of quick reader, you'll need to use a dataset if you want to bind.

[DevGrp]

In a Web App, and dataReader can be bound to a Web Form, it cant be bound to a WinForms App though. DataReaders work different in Web Apps and WinForms Apps.

[Hean]

Yes I Bind the DataReader in the Web Form, but it give me error.

Below is my code, anything goes wrong ?

The following code give System.Data.Common.DBDataRecord error.

Dim cnn As New OleDb.OleDbConnection(strConnTeamPE)
cnn.Open()
strSQL = "SELECT sTeamID FROM TeamMaster"
Dim cmd As New OleDb.OleDbCommand(strSQL, cnn)
Dim dr As OleDb.OleDbDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Bind Data Reader to DropDownList
ddlTeamName.DataSource = dr
ddlTeamName.DataBind()

Thanks.

Regards,
Hean

[DevGrp]

What is the error you are getting?

[Cin0s3]

You may need to specify the DataTextName and the DataTextfield but it should work fine with a datareader. This is how I do mine

                Dim f as new Listbox
                f.EnableViewState = True
                f.DataSource = dr
                f.DataValueField = "code"
                f.DataTextField = "name"
                f.DataBind()
                panel1.controls.add(f)

[Hean]

Yes ! it work if include with the setting of DataTextField & DataValueField.
Thanks, Cin0s3 & thank you to everyone that gave the advice.

Regards,
Koay

[indydavid32]

Hi guys, I can't seem to get this DropDownList thing to work on my web form. Here is my code.

Dim cn As New SqlClient.SqlConnection()
        Dim commSQL As New SqlClient.SqlCommand()
 
        cn.ConnectionString = "Integrated Security=True;" & _
        "Data Source=Brutus;Initial Catalog=Problem Tracking;" & _
        "user id=xxx;password=yyyyy;"
        cn.Open()
        commSQL.Connection = cn
        commSQL.CommandText = "Select [Store Number] From Stores Order By [Store Number]"
        Dim datRead As SqlClient.SqlDataReader
        datRead = commSQL.ExecuteReader()
        cmbStores.DataSource = datRead
        cmbStores.DataBind()

The field I am trying to place in the dropdown box is Stores.Store Number.

I am getting the same errors the first person in this post got. I do not understand the fix that the other gave him.

Can anyone help me please?

Thanks

[Cin0s3]

Just do like this:

Dim cn As New SqlClient.SqlConnection()
        Dim commSQL As New SqlClient.SqlCommand()

        cn.ConnectionString = "Integrated Security=True;" & _
        "Data Source=Brutus;Initial Catalog=Problem Tracking;" & _
        "user id=xxx;password=yyyyy;"
        cn.Open()
        commSQL.Connection = cn
        commSQL.CommandText = "Select [Store Number] From Stores Order By [Store Number]"
        Dim datRead As SqlClient.SqlDataReader
        datRead = commSQL.ExecuteReader()
        cmbStores.DataSource = datRead
        cmbStores.datatextfield="Store Number"
        cmbStores.datavaluefield="Store Number"
        cmbStores.DataBind()

[indydavid32]

Cin, I tried that and I still get the same error.

[Cin0s3]

Whats the error?

[indydavid32]

Cin, I don't really get an error. It's just that the dropdown box is filled with 32 lines of the text 'System.Data.Common.DbDataRecord' I need the actualy store number in this drop down box.

David

[indydavid32]

Nevermind, this works. I had the code in the wrong event.

Lets say that I wanted to add the the text word Add to this dropdown box to be inserted before the stores. How would I do that?

[Lord_Rat]

Either add the items manually (looping through the datareader) and add that item before your loop

or

Save the result set to a datatable and use the INSERT on the datatable to add a row to the beginning of the datatable and then bind the datatable to the drop down list.

[indydavid32]

I knew about the loop and add method.

It is not a good idea for me to add 'All' to the database table.

I was just thinking maybe there was a more efficient way to do this. The Loop method is fine since there are only 32 records in this particular table anyway.

Thanks!

[Lord_Rat]

Just to make sure we are clear--

I was not suggesting you add ALL to the database table, but rather a system.data.datatable object that you create on the fly.

[pvb]

After binding to the reader do this:

cmbStores.Items.Insert(0, New ListItem("Add", ""))

[indydavid32]

Thanks pvb, that works great!

Where do you guys learn all this stuff? Books? What books?

Thanks