Emulate A DataBind to a DropdownList in VB.NET Using MySQL

Question

I have an application where I need to populate a DropDownList using MySQL. This is easily done using

DropDownList1.Items.Add(“ItemName”) 

The problem though is that I need to pass the database index for the ItemName rather than the default index. I have tried to use

DropDownList1.Items.Insert(int index, “ItemName”)

for example,

   Using command As New MySqlCommand(sqlQuery, handle)
     Dim data_reader As MySqlDataReader = command.ExecuteReader()
     While data_reader.Read
       Dim index = data_reader("Group ID")
       Dim item = data_reader("Group Name")
       DropDownList1.Items.Insert(index, item)
    End While
    data_reader.Close()
 End Using

but this fails due

"Insertion index was out of range. Must be non-negative and less than or equal to size." & vbCrLf & "Parameter name: index"}

This is because I have no idea of what the index range will be. Is there another way other than the programme capturing the indexes and mapping to the Database ID; which I really don't want to do.

Answer 1

You can't insert something at an index that doesn't exist in a list, e.g. you can't insert at index 10 if a list only has 5 items. Actually data-binding is the logical option but, if you really must just insert items, the logical thing to do is to sort the items by the database ID first and then add them in that order. This obviously requires that those IDs be sequential at least. They should also start at zero, or else you'd have to add some offset to the index to get the actual ID.

Answer 2

Based on @Hursey 's comment, the answer lies here - but for context of this question here is my implementation based on that answer and hope it helps someone else.

Dim myData As New DataSet
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
cmd.CommandText = sqlQuery 'Your query...
cmd.Connection = conn

myAdapter.SelectCommand = cmd
myAdapter.Fill(myData)
DropDownList1.DataSource() = myData
DropDownList1.DataValueField = "Group ID"
DropDownList1.DataTextField = "Group Name"
DropDownList1.DataBind()