mysql - 使用类在aspx vb中用mysql表填充下拉列表中的数据

Question

我正在尝试在 vb 中的 webform asp.net 中加载链接到 mysql 中的表的下拉列表。我正在使用具有以下代码的类:

Public Class ClCountries
    Public Function Read(ByVal Opcion As Integer) As DataTable

        Dim cnn As New MySqlConnection(ConfigurationManager.ConnectionStrings("cnnPortalSmart").ConnectionString)
        Dim dt As New DataTable
        Try
            Dim cmd As New MySqlCommand("PA_COUNTRIES_SELECT", cnn)
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@Opcion", Opcion)

            cnn.Open()
            dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges)
            cnn.Close()
        Catch ex As Exception
            Dim msgError As String
            msgError = ex.Message.ToString()
            cnn.Close()
        End Try
        Return dt
    End Function
End Class

它是通过以下方法从 Page_Load() 调用的:

Sub LoadCountries()
    Try 
        Dim dv As New DataView(New ClCountries().Read(1)) 
        ddlCountry.DataSource = dv 
        ddlCountry.DataTextField = "Name" 
        ddlCountry.DataValueField = "ID" 
        ddlCountry.DataBind() 
        Dim li As New ListItem("Select Country", "0") 
        ddlCountry.Items.Insert(0, li) 
    Catch ex As Exception 
    End Try 
End Sub

aspx 中的连接字符串:

<connectionStrings>    
    <add name="cnnPortalSmart" connectionString="Data Source=IP_NUMBER;Initial Catalog=SCHEMA_NAME; User ID=root;Password=XXXX;" providerName="MySql.Data.MySqlClient" />  
</connectionStrings>

谢谢。

Answer 1

希望对您有帮助

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("SELECT CustomerId, Name FROM Customers")
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                con.Open()
                ddlCustomers.DataSource = cmd.ExecuteReader()
                ddlCustomers.DataTextField = "Name"
                ddlCustomers.DataValueField = "CustomerId"
                ddlCustomers.DataBind()
                con.Close()
            End Using
        End Using
        ddlCustomers.Items.Insert(0, New ListItem("--Select Customer--", "0"))
    End If
End Sub