How To Populate a DropDown List From a DataSet?

Question

I am trying to populate an ASP dropdown list in vb.net with the results from a stored procedure returned in a data set. I was wondering if anyone knew the vb.net code to populate the dropdown list?

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load

    Dim connString As String = "Server=MYCOMPUTER\SQLEXPRESS;Database=scales;Trusted_Connection=True"
    Dim myConn As New SqlConnection(connString)
    myConn.Open()
    Dim da As New SqlDataAdapter("select scaleName from scales", myConn)
    Dim dt As New DataTable
    da.Fill(dt)

    ComboBox1.DisplayMember = "scaleName"

    ComboBox1.DataSource = dt
    myConn.Close()

Answer 1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
             IF Not IsPostback then
               PopulateDropdown()
             End IF
End Sub

Private Sub PopulateDropDown()
      Dim connString As String = "Server=MYCOMPUTER\SQLEXPRESS;Database=scales;Trusted_Connection=True"
      Dim myConn As New SqlConnection(connString)
      myConn.Open()
      Dim da As New SqlDataAdapter("select ScaleId, scaleName from scales", myConn)
      Dim dt As New DataTable
      da.Fill(dt)

          Me.ComboBox1.DataTextField = "scaleName "
          Me.ComboBox1.DataValueField = "ScaleId"
          Me.ComboBox1.DataSource = dt
          Me.ComboBox1.DataSourceID = String.Empty
          Me.ComboBox1.DataBind()

     myConn.Close()
End Sub

Answer 2

In order to show the data in the DropDownList control, you can use the following Code. To use the results of a Stored Procedure, you need to create the SELECT command:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
        Dim dt As New DataTable
        Dim connString As String = "Server=MYCOMPUTER\SQLEXPRESS;Database=scales;Trusted_Connection=True"
        Using myConn As New SqlConnection(connString)
            myConn.Open()
            Using cmd = myConn.CreateCommand()
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "dbo.uspMyStoredProc"
                cmd.Parameters.AddWithValue("@MyInputParam", 123)
                Using da As New SqlDataAdapter(cmd)
                    da.Fill(dt)
                End Using
            End Using
        End Using
        ComboBox1.DisplayMember = "scaleName"
        ComboBox1.DataSource = dt
        ComboBox1.DataBind()
    End If
    ' ...
End Sub

I've adjusted the following things:

• Usually you only need to bind the data on the initial request. Therefore, the if statement at the beginning checks the IsPostBack property.
• In order to close and dispose the connection and the data datapter reliably, I've added some using statements.
• In order to access the stored procedure, I've created a SqlCommand and set the CommandType to StoredProcedure. The CommandText is set to the name of the Stored Procedure. In the sample, I've also added a parameter named MyInputParam that is sent to the Stored Procedure.