Saturday, 8 June 2013

Complete Procedure For Insert Country State and City And Show in Cascading Dropdownlist in asp.net

Complete Procedure  For Insert Country State and City And Show in Cascading Dropdownlist in asp.net
Create three tables as per our need
CREATE TABLE country
  (
     countryID     INT NOT NULL,
     countryName   varchar(50) NOT NULL,
     PRIMARY KEY (countryID ),
  );
CREATE TABLE state
  (
 stateID     INT NOT NULL,
 countryID INT NOTNULL,
 stateName   varchar(50) NOT NULL,
 PRIMARY KEY (stateID ),
 FOREIGN KEY (countryID ) REFERENCES country (countryID));

CREATE TABLE city
  (
cityID     INT NOT NULL,
 stateID INT NOTNULL,
 cityName   varchar(50) NOT NULL,
 PRIMARY KEY (cityID),
 FOREIGN KEY (stateID) REFERENCES state (stateID));
Insert Country:

.aspx code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Insert Country.aspx.cs" Inherits="Insert_Country" %>

<!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>Add Country</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h2>Add Country</h2>
        <table>
            
            <tr>
                <td>
                    <asp:Label ID="Label1" runat="server" Text="Country:"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

.cs code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class Insert_Country : System.Web.UI.Page
{
    string str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(str);
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into country(countryName) values('" + txtCountry.Text + "')", con);
        int i = cmd.ExecuteNonQuery();
        Response.Write(i.ToString() + "Row(s) updated Successfully");
    }
   
}

Insert State:
.aspx code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Insertstate.aspx.cs" Inherits="Insertstate" %>

<!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>Insert State</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Insert State</h1>
    <table>
            <tr>
                <td>
                    <asp:Label ID="lblCountry" runat="server" Text="Label">Country:</asp:Label>
                </td>
                <td>
                    <asp:DropDownList ID="ddlCountry" runat="server" Width="200px" DataTextField="Country_Name" DataValueField="Country_Id">
                    </asp:DropDownList>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblState" runat="server">State:</asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtState" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

.cs code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public partial class Insertstate : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            bindcountry();
        }

    }
    public void bindcountry()
    {
        DataTable dataTable = new DataTable();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("SELECT countryID, countryName FROM country", con);
        SqlDataReader sqlDataReader = cmd.ExecuteReader();
        dataTable.Load(sqlDataReader);
        ddlCountry.DataSource = dataTable;
        ddlCountry.DataTextField = "countryName";
        ddlCountry.DataValueField = "countryID";
        ddlCountry.DataBind();
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("INSERT INTO state(countryID,stateName) VALUES(@CI,@SN)", con);
        cmd.Parameters.AddWithValue("@CI", Convert.ToInt32(ddlCountry.SelectedItem.Value));
        cmd.Parameters.AddWithValue("@SN", txtState.Text);
        //cmd.Parameters.Add(new SqlParameter("@CI", Convert.ToInt32(ddlCountry.SelectedItem.Value)));
       // cmd.Parameters.Add(new SqlParameter("@SN", txtState.Text));
        int i = cmd.ExecuteNonQuery();
        Response.Write(i.ToString() + "Row(s) updated Successfully");
    }
   
}



Insert City:
.aspx Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Insert City.aspx.cs" Inherits="Insert_City" %>

<!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>Insert City</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Insert City</h1>
     <table>
            <tr>
                <td>
                    <asp:Label ID="lblCountry" runat="server" Text="Label">Country:</asp:Label>
                </td>
                <td>
                    <asp:DropDownList ID="ddlCountry" runat="server" Width="200px"
                        DataTextField="Country_Name" DataValueField="Country_Id" AutoPostBack="True"
                        onselectedindexchanged="ddlCountry_SelectedIndexChanged">
                    </asp:DropDownList>
                </td>
            </tr>
            <tr>
            <td>

                <asp:Label ID="Label1" runat="server" Text="State"></asp:Label>

            </td>
            <td>
                <asp:DropDownList ID="ddlstate" runat="server">
                </asp:DropDownList>
            </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="lblState" runat="server">City</asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtcity" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

.cs Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public partial class Insert_City : System.Web.UI.Page
{
    string str= ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
           BindContrydropdown();
        }
    }

    /// <summary>
    /// Bind COuntrydropdown
    /// </summary>
    protected void BindContrydropdown()
    {
        //conenction path for database
        SqlConnection con = new SqlConnection(str);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from country", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        ddlCountry.DataSource = ds;
        ddlCountry.DataTextField = "countryName";
        ddlCountry.DataValueField = "countryID";
        ddlCountry.DataBind();
        ddlCountry.Items.Insert(0, new ListItem("--Select--", "0"));
        ddlstate.Items.Insert(0, new ListItem("--Select--", "0"));
        //ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));

    }
  

    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        int countryID = Convert.ToInt32(ddlCountry.SelectedValue);
        SqlConnection con = new SqlConnection(str);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from state where countryID  =" + countryID, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        ddlstate.DataSource = ds;
        ddlstate.DataTextField = "stateName";
        ddlstate.DataValueField = "stateID";
        ddlstate.DataBind();
        ddlstate.Items.Insert(0, new ListItem("--Select--", "0"));
        //if (ddlstate.SelectedValue == "0")
        //{
        //    ddlRegion.Items.Clear();
        //    ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));
        //}
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(str);
        con.Open();
        SqlCommand cmd = new SqlCommand("INSERT INTO city(stateID,cityName) VALUES(@CI,@SN)", con);
        cmd.Parameters.AddWithValue("@CI", Convert.ToInt32(ddlstate.SelectedItem.Value));
        cmd.Parameters.AddWithValue("@SN", txtcity.Text);
        //cmd.Parameters.Add(new SqlParameter("@CI", Convert.ToInt32(ddlCountry.SelectedItem.Value)));
        // cmd.Parameters.Add(new SqlParameter("@SN", txtState.Text));
        int i = cmd.ExecuteNonQuery();
        Response.Write(i.ToString() + "Row(s) updated Successfully");
    }
   
}
Cascading Dropdownlist:
.aspx code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CascadingDropdownlist.aspx.cs" Inherits="CascadingDropdownlist" %>

<!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>
    <div>
<table align="center">
<tr>
<td>
Select Country:
</td>
<td>
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true"
onselectedindexchanged="ddlCountry_SelectedIndexChanged"></asp:DropDownList>
</td>
</tr>
<tr>
<td>
Select State:
</td>
<td>
<asp:DropDownList ID="ddlState" runat="server" AutoPostBack="true"
onselectedindexchanged="ddlState_SelectedIndexChanged"></asp:DropDownList>
</td>
</tr>
<tr>
<td>
Select CITY:
</td>
<td>
<asp:DropDownList ID="ddlRegion" runat="server"></asp:DropDownList>
</td>
</tr>
</table>
</div>
    </div>
    </form>
</body>
</html>

.cs code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class CascadingDropdownlist : System.Web.UI.Page
{
    string str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindContrydropdown();
        }

    }

    /// <summary>
    /// Bind COuntrydropdown
    /// </summary>
    protected void BindContrydropdown()
    {
        //conenction path for database
        SqlConnection con = new SqlConnection(str);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from country", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        ddlCountry.DataSource = ds;
        ddlCountry.DataTextField = "countryName";
        ddlCountry.DataValueField = "countryID";
        ddlCountry.DataBind();
        ddlCountry.Items.Insert(0, new ListItem("--Select--", "0"));
        ddlState.Items.Insert(0, new ListItem("--Select--", "0"));
        ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));

    }


    /// <summary>
    /// Bind State Dropdown Based on CountryID
    /// </summary>
    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        int countryID = Convert.ToInt32(ddlCountry.SelectedValue);
        SqlConnection con = new SqlConnection(str);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from state where countryID  =" + countryID, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        ddlState.DataSource = ds;
        ddlState.DataTextField = "stateName";
        ddlState.DataValueField = "stateID";
        ddlState.DataBind();
        ddlState.Items.Insert(0, new ListItem("--Select--", "0"));
        if (ddlState.SelectedValue == "0")
        {
            ddlRegion.Items.Clear();
            ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));
        }
    }



    /// <summary>
    /// Bind Region dropdown based on Re
    /// </summary>
    protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
    {
        int stateID = Convert.ToInt32(ddlState.SelectedValue);
        SqlConnection con = new SqlConnection(str);
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from city where stateID =" + stateID, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        con.Close();
        ddlRegion.DataSource = ds;
        ddlRegion.DataTextField = "cityName";
        ddlRegion.DataValueField = "cityID";
        ddlRegion.DataBind();
        ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));
    }
}




Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)