vb.net 如何从数据集中填充下拉列表？

How To Populate a DropDown List From a DataSet?

提问by zoltar

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?

我试图用数据集中返回的存储过程的结果填充 vb.net 中的 ASP 下拉列表。我想知道是否有人知道填充下拉列表的 vb.net 代码？

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()

采纳答案by Markus

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:

为了在 DropDownList 控件中显示数据，可以使用以下代码。要使用存储过程的结果，您需要创建 SELECT 命令：

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 ifstatement at the beginning checks the IsPostBackproperty.
• In order to close and dispose the connection and the data datapter reliably, I've added some usingstatements.
• In order to access the stored procedure, I've created a SqlCommand and set the CommandTypeto StoredProcedure. The CommandTextis set to the name of the Stored Procedure. In the sample, I've also added a parameter named MyInputParamthat is sent to the Stored Procedure.

• 通常你只需要在初始请求上绑定数据。因此，if开头的语句检查IsPostBack属性。
• 为了可靠地关闭和处理连接和数据适配器，我添加了一些using语句。
• 为了访问存储的过程中，我创建了一个SqlCommand并设置CommandType到StoredProcedure。将CommandText被设置为存储过程的名称。在示例中，我还添加了一个名为的参数MyInputParam，该参数将发送到存储过程。

回答by Amarnath Balasubramanian

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