How can I populate a dropdown list with data?

Question

I have a dropdownlist that I want to populate with a specific value and specific text. I'm not using a datasource but I am manually making a connection and retrieving data in code. How can I populate this dropdownlist? If I read the data with a datareader and increment an array I only get either the value or the text. This is what I have so far, but it is completely wrong:

//connection string etc goes here
Dbcmd2.CommandText = "select dept,deptname from table"

Dim dr As SqlClient.SqlDataReader
    dr = Dbcmd2.ExecuteReader
    Dim i As Integer
    Dim arydept As New arraylist
    While dr.Read
       arydept.Add(dr1("dept"))
    End While

    ddldept.datasource = arydept
    ddldept.DataTextField = ????????
    ddldept.DataValueField = dr("dept")
    ddldept.DataBind()

How can I get this to work without having to create a class object for Department? Is there anyway or should I create the class object?

Answer 1

Is there any particular reason you can do it the normal way?

Dbcmd2.CommandText = "select dept,deptname from table"
Dim dr As SqlClient.SqlDataReader = Dbcmd2.ExecuteReader
While dr.Read() 
  ddldept.Items.Add(new ListItem(dr("deptname"),dr("dept"))
End While
' ddldept is now populated with all items from the query

Answer 2

Instead of using an ArrayList if you use a dictionary object then you can store the name and the value.

Something like this should do it

Dim All As New Dictionary(Of String, String)
All.Add("Test", 1)
All.Add("Test2", 2)
test.DataSource = All
test.DataTextField = "Key"
test.DataValueField = "Value"
test.DataBind()

Answer 3

Instead of using the datasource/databind() approach, you could simply create the list items and add them to your dropdownlist.

 dr = Dbcmd2.ExecuteReader
    Dim i As Integer
    Dim arydept As New arraylist

    ddldept.Items.Clear()
    While dr.Read
       ddldept.Items.Add(new ListItem(dr1("dept"), dr1("dept")))
    End While

Answer 4

How about this one?

ddldept.Items.Clear()

While dr.Read
   ListItem item = New ListItem()
   item.Text = dr("deptname").ToString()
   item.Value = dr("dept").ToString()

   ddldept.Items.Add(item)
End While

Answer 5

You are already using the reader so just pop that into a DataTable and bind:

//connection string etc goes here
Dbcmd2.CommandText = "select dept,deptname from table"

Dim dr As SqlClient.SqlDataReader
dr = Dbcmd2.ExecuteReader
Dim myData as DataTable
If dr.HasRows Then
    myData.Load(dr)
End If

ddldept.datasource = myData
ddldept.DataTextField = myData("myTextField")
ddldept.DataValueField = myData("dept")
ddldept.DataBind()