In my previous post, I have explained how to add a VB.Net Web Solution. In this article I'm going to -provide the solution. If you want to see my previous post click here.
The Problem:
In a web form (don’t confuse what is a web form!!!) I’m going to add the 2 dropdown namely: 1) Country Dropdown 2) State Dropdown The Country Drop down contains a list of countries and the state contains list of states. Whenever the user select the country from country drop down, the respective states which is located on that Country will be loaded in to the State drop down.
In the Default.aspx page copy and paste the following:
Designer - Default.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="AllInOne._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
Select Country:
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true">
</asp:DropDownList>
Select City:
<asp:DropDownList ID="ddlCity" runat="server">
</asp:DropDownList>
</div>
</form>
</body>
</html>
Code Behind : Default.aspx.vb
Copy and paste the following in the Default.aspx.vb page.
Imports System.Data
Imports System.Data.SqlClient
Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConString As String = GetConnectionStringFromConfig()
If Not IsPostBack Then
GetDataFromDB(strConString)
End If
End Sub
Private Function GetConnectionStringFromConfig() As String
Dim strConString As String = String.Empty
strConString = ConfigurationManager.AppSettings("connectionstring")
Return strConString
End Function
Private Sub GetDataFromDB(ByVal strConString As String)
Dim sqlCon As New SqlConnection
Dim sqlCmd As New SqlCommand
sqlCon.ConnectionString = strConString
sqlCmd.Connection = sqlCon
sqlCmd.CommandType = CommandType.Text
sqlCmd.CommandText = "Select country_id,country_name from tblCountry"
Dim sqlAdaptor As New SqlDataAdapter(sqlCmd)
Dim ds As New DataSet
sqlAdaptor.Fill(ds)
ddlCountry.DataSource = ds
ddlCountry.DataTextField = "country_name"
ddlCountry.DataValueField = "country_id"
ddlCountry.DataBind()
sqlCon.Close()
End Sub
Private Sub GetCities(ByVal strConString As String, ByVal strSelectedCountry As String)
Dim sqlCon As New SqlConnection
Dim sqlCmd As New SqlCommand
sqlCon.ConnectionString = strConString
sqlCmd.Connection = sqlCon
sqlCmd.CommandType = CommandType.Text
sqlCmd.CommandText = String.Concat("Select city_id,city_name from tblcity where country_id = ", strSelectedCountry)
Dim sqlAdaptor As New SqlDataAdapter(sqlCmd)
Dim ds As New DataSet
sqlAdaptor.Fill(ds)
ddlCity.DataSource = ds
ddlCity.DataTextField = "city_name"
ddlCity.DataValueField = "city_id"
ddlCity.DataBind()
sqlCon.Close()
End Sub
Private Sub ddlCountry_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountry.TextChanged
Dim strConstring As String = GetConnectionStringFromConfig()
GetCities(strConstring, ddlCountry.SelectedValue)
End Sub
End Class
Add the Following lines into the web.config file, under the configuration secion(i.e., between <configuration> and </configuration> tags)
<appSettings> <add key="connectionstring" value="Data Source=DBServerName;Initial Catalog=DBName;Integrated Security=True"></add> </appSettings>In the above "Datasource" denotes the DB server address and "Initial Catalog" denotes the Database Name. Here, found the queries for this application:
---Query To Create City Table CREATE TABLE [dbo].[tblCity]( [city_id] [int] NOT NULL, [city_name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [country_id] [int] NOT NULL ) ON [PRIMARY]; ---Query To Create Country Table CREATE TABLE [dbo].[tblCountry]( [country_id] [int] NOT NULL, [country_name] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]; -- Insert Some Values on country and city tables insert into tblCountry ([country_id],[country_name]) values (1,'India'); insert into tblCountry ([country_id],[country_name]) values (2,'Pakistan'); insert into tblCountry ([country_id],[country_name]) values (3,'US'); insert into tblCountry ([country_id],[country_name]) values (4,'SriLanka'); insert into tblCountry ([country_id],[country_name]) values (5,'China'); insert into tblCountry ([country_id],[country_name]) values (6,'Japan'); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(1,'Chennai',1); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(2,'Mumbai', 1); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(3,'Karachi', 2); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(4,'Lahore', 2); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(5,'Beijing', 5); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(6,'Tokyo', 4); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(7,'NewYork', 3); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(8,'LosAngels', 3); INSERT INTO tblCity([city_id],[city_name],[country_id]) VALUES(9,'Kolkatta', 1);Press F5 To run the application!!!!
