DropDownList doesn't display the first item on the list. 2

[Katya85S]

DropdownList and radiobuttonList are populated from sql server, using DataBinding.
Somewhat neither of them shows the value of the first record. For example, if SQL query result is <John, Mike, Kim>, the radiobutton list will have just 2 radiobuttons:
-Mike
-Kim

And the same with DropDownList.

Here is the code for my radiobuttonlist:
aspx code:

<asp:radiobuttonlist id="rbList" runat="server" DataTextField="EmpName" DataValueField="EmpName"></asp:radiobuttonlist>

aspx.vb code:

strSQL = "SELECT EmpName FROM tblEmp WHERE Status = 'm'

myConn.Open()

Dim objCmd As New SqlCommand(strSQL, myConn)
Dim DR As SqlDataReader

DR = objCmd.ExecuteReader(CommandBehavior.CloseConnection)

If DR.Read Then
rbList.DataSource = DR
rbList.DataBind()

End If

objCmd.Dispose()
myConn.Close()
strSQL = ""

Any advice on how to fix this? Thank you all in advance.

 

[jbenson001]

Here's an example I found online at:

http://www.devcity.net/Articles/68/1/.aspx

8. Here is an example of how you might populate the control from a SQL Server database using a data reader object: 

' Let's assume the connection string is stored in a session variable. 
Dim strConnect as Strng
strConnect = Session("ConnectionString") 

' Open the Connection 
Dim Con as new System.Data.SQLClient.SQLConnection(strConnect) 
Con.Open() 

' SQL Statement
Dim strSQL as String
strSQL = "SELECT State_Name, State_Code FROM TableState ORDER BY State_Name"

' Command, Data Reader  
Dim Com as new System.Data.SQLClient.SQLCommand(strSQL, Con) 
Dim rdr as System.Data.SQLClient.SQLDataReader = Com.ExecuteReader() 

' Populate the Control 
While rdr.Read()
    Dim newListItem as new ListItem() 
    newListItem.Text = rdr.GetString(0)
    newListItem.Value = rdr.GetString(1) 
    DropDownList1.Items.Add(newListItem) 
End While

 

[GoTerps88]

If you have Visual Studio 2005 or one of the Express versions it would be easier just to create a SQL Data source and use the smart tag for the dropdownlist and radiobuttonlist to set the data source. You can then configure the text and value fields.

 

[Katya85S]

Hi guys. Thank you for your responds.
I work in Visual Studio 2003 and cannot apply GoTerps88 advice.
And i've tried jbenson's suggestion, but the first item (“John”) still is omitted.
What could be wrong?

 

[jbenson001]

What does your new code look like?

 

[ca8msm]

I can see one reason why your code may not work (as the Read method in your If block may actually read the first record) but I can't see a reason why the code jbenson001 posted wouldn't work (unless of course the source data was wrong).


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[Katya85S]

Thank you so much guys! I didn't realise "IF DR.Read" can hurt the logic. I've deleted the if-block and now jbenson001 code works
Thanks to both of you

 

[Katya85S]

Forgot to mention one little correction, just in case it might be helpful to somebody else:

newListItem.Value = rdr.GetString(1)
throws an error.

newListItem.Value = rdr.GetValue(1)
does it right.

 

[jbenson001]

glad you got it

 

[GoTerps88]

What I like to do is insert the values from the data reader into a typed collection, generic collection or list in my data access class. I return the IList or IEnumerable object back to the caller, and then I can set the data source to the collection. The method public static IEnumerable getCustomers() or something like that.

I'm not sure if there is a performance hit for adding items to a drop down list one by one as you are doing. For a small list you may not even notice.

A little known feature you can use is list.AddRange where you could add your list in one bulk action instead of one by one. I have found this faster for large lists.

 

[GoTerps88]

If you notice in the .NET generated code for windows and web forms, you'll see .AddRange used extensively for adding controls.

 

[jbenson001]

I'm not sure if there is a performance hit for adding items to a drop down list one by one as you are doing. For a small list you may not even notice.

Probably not, but you shouldn't be adding a lot of items to the list anyway as the user will be overloaded with data.

 

[cupcakesrule]

You can use

If DR.HasRows Then
rbList.DataSource = DR
rbList.DataBind()
End If

 

[Katya85S]

That's exactly what i need! Thank you cupcakesrule!