When I run this code my listbox is empty. What is the best way to get data from SQL and into a listbox? When the form is submitted I want to use the CustomreID value data to store into another table and thought using the index would be the best solution.
sSQL = "SELECT CustomerID, Company from Customers Order by Company ASC"
cmd = New SqlCommand(sSQL, moConn)
rs = cmd.ExecuteReader()
While rs.Read
lsbDestination.Items.Insert(CInt(rs("CustomerID")), rs("Company"))
End While
You can easily bind data to a ListBox using the DataSource property of the ListBox. Try something like this (untested):
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New DataSet
adapter.Fill(ds)
lsbDestination.DataTextField = "Company"
lsbDestination.DataValueField = "CustomerId"
lsbDestination.DataSource = ds.Tables(0)
lsbDestination.DataBind()
Good luck.
If you want to add a items to ListBox using While rs.Read.
Using rsAs SqlDataReader = cmd.ExecuteReader()
While rs.Read()
Dim items As Object() = {r("CustomerID"), r("Company").ToString()}
listBox1.Items.Add(items)
End While
listBox1.DisplayMember = "Company"
listBox1.ValueMember = "CustomerID"
End Using
Just let me know if you've encountered some problem.
lsbDestination.DisplayMember = "Company"; lsbDestination.ValueMember = "CustomerID";