CODEWITHSUNDEEP
asp.netvb.net
user692495
user692495

Reputation: 69

bind country, state and city with DropDownList in ASP. NET using 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

Country Table

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

CountryState Table

Create Table 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

Upvotes: 0

Views: 3144

Answers (1)

CoderMarkus
CoderMarkus

Reputation: 1118

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

...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.

Upvotes: 2

Related Questions