在ASP中绑定国家、州和城市的下拉列表。净使用VB.NET

i try to bind country, state and city with DropDownList in ASP. NET using VB.NET. The problem is when i choose country, there not display list of state that related to the country. I use MS Access 2003 for database, Here is my table& code

我尝试用ASP的下拉列表来绑定国家、州和城市。净使用VB.NET。问题是当我选择国家时，没有显示与国家有关的国家名单。我使用MS Access 2003的数据库，这是我的表和代码。

Country Table

Create Table Country ( CountryID Int Primary Key, CountryName Varchar(30) )

创建表国家(CountryID Int Primary Key, CountryName Varchar(30))

CountryState Table

CountryState表

Create Table countryState ( StateID Int Primary Key, CountryID Int Foreign Key References Country(CountryID), State Varchar(30) )

创建表countryState (StateID Int Primary Key, CountryID Int Foreign Key References Country(CountryID)， State Varchar(30))

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    cnnOLEDB.ConnectionString = strConnectionString
    If Not IsPostBack Then
        Bind_ddlCountry()
    End If
End Sub
Public Sub Bind_ddlCountry()
    Dim cnnOLEDB As New OleDbConnection(strConnectionString)
    cnnOLEDB.Open()
    Dim cmd As New OleDbCommand("SELECT CountryID,CountryName FROM Country", cnnOLEDB)
    Dim dr As OleDbDataReader = cmd.ExecuteReader()
    ddlcountry.DataSource = dr
    ddlcountry.Items.Clear()
    ddlcountry.Items.Add("--Please Select country--")
    ddlcountry.DataTextField = "CountryName"
    ddlcountry.DataValueField = "CountryID"
    ddlcountry.DataBind()
    cnnOLEDB.Close()
End Sub

Public Sub Bind_ddlState()
    Dim cnnOLEDB As New OleDbConnection(strConnectionString)
    cnnOLEDB.Open()
    'Dim cmd As New OleDbCommand("SELECT StateID, State FROM CountryState", cnnOLEDB)
    Dim cmd As New OleDbCommand("SELECT StateID, State FROM CountryState WHERE CountryID='" & ddlcountry.SelectedValue & "'", cnnOLEDB)
    Dim dr As OleDbDataReader = cmd.ExecuteReader()
    ddlstate.DataSource = dr
    ddlstate.Items.Clear()
    ddlstate.Items.Add("--Please Select state--")
    ddlstate.DataTextField = "State"
    ddlstate.DataValueField = "StateID"
    ddlstate.DataBind()
    cnnOLEDB.Close()

End Sub
Protected Sub ddlcountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlcountry.SelectedIndexChanged
    Bind_ddlState()
End Sub

1 个解决方案

#1

Because CountryID is an integer, you don't need single quotes in the WHERE clause of you SQL query.

因为CountryID是一个整数，所以在SQL查询的WHERE子句中不需要单引号。

...WHERE CountryID = " & ddlcountry.SelectedValue, cnnOLEDB)

Another note - in general, you should always parametrize your variables. Otherwise you are creating low hanging fruit for a potential SQL injection attack.

另一个注意事项——通常，您应该始终参数化您的变量。否则，您将为潜在的SQL注入攻击创建低挂的结果。

#1