1、在SQL Sever 中建表
CREATE TABLE T_Arear
(
Id INT IDENTITY PRIMARY KEY,
A_Id VARCHAR(8),
A_Name VARCHAR(16),
A_ParentId VARCHAR(8)
)
--添加省级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('P001','河北省','0');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('P002','河南省','0');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('P003','山西省','0');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('P004','山东省','0');
--添加河北省市级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C001','石家庄','P001');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C002','保定','P001');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C003','邯郸','P001');
--添加河南省市级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C004','郑州','P002');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C005','洛阳','P002');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C006','商丘','P002');
--添加山西省市级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C007','太原','P003');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C008','运城','P003');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C009','大同','P003');
--添加山东省市级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C010','济南','P004');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C011','青岛','P004');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('C012','淄博','P004');
--添加河北省石家庄区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A001','长安区','C001');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A002','桥东区','C001');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A003','桥西区','C001');
--添加河北省保定区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A004','满城','C002');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A005','徐水','C002');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A006','阜平','C002');
--添加河北省邯郸区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A007','临漳','C003');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A008','成安','C003');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A009','大名','C003');
--添加河南省郑州区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A009','金水','C004');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A010','惠济','C004');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A012','经开区','C004');
--添加河南省洛阳区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A013','新安','C005');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A014','嵩县','C005');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A015','宜阳','C005');
--添加河南省商丘区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A016','梁园','C006');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A017','永城','C006');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A018','宁陵','C006');
--添加山西省太原区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A019','小店','C007');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A020','迎泽','C007');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A021','阳曲','C007');
--添加山西省运城区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A022','盐湖','C008');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A023','万荣','C008');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A024','闻喜','C008');
--添加山西省大同市区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A025','新荣','C009');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A026','阳高','C009');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A027','天镇','C009');
--添加山东省济南区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A028','历下','C010');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A029','天桥','C010');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A030','长清','C010');
--添加山东省青岛区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A031','崂山','C011');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A032','黄岛','C011');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A033','胶州','C011');
--添加山东淄博区县级数据
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A034','张店','C012');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A035','博山','C012');
INSERT INTO T_Arear(A_Id,A_Name,A_ParentId) VALUES('A036','桓台','C012');
2、前台
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:DropDownList ID="ddlProvince" runat="server"
Xonselectedindexchanged="Dropprovince_SelectedIndexChanged"
AutoPostBack="True" Height="25px"
onselectedindexchanged="ddlProvince_SelectedIndexChanged" Width="120px">
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="True"
Xonselectedindexchanged="Dropcity_SelectedIndexChanged" Height="22px"
Width="114px" onselectedindexchanged="ddlCity_SelectedIndexChanged">
</asp:DropDownList>
</td>
<td>
<asp:DropDownList ID="ddlArear" runat="server" Height="24px" Width="123px" >
</asp:DropDownList>
</td>
</tr>
</table>
</div>
</form>
</body>
3、后台
public partial class 省市县三级联动 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetProvince();
}
}
private void LoadData(string id, DropDownList ddl)
{
string strcon = @"Data Source=Lan-PC;Initial Catalog=News;Persist Security Info=True;User ID=sa;Password=478950";
SqlConnection conn = new SqlConnection(strcon);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT A_Id,A_Name FROM T_Arear WHERE A_ParentId=@parentid ORDER BY A_Id";
cmd.Parameters.AddWithValue("@parentid", id);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
cmd.Dispose();
conn.Dispose();
ddl.DataSource = dt;
//指定下拉列表括要显示的字段的值
ddl.DataTextField = "A_Name";
//指定下拉列表括的value属性的字段的值
ddl.DataValueField = "A_Id";
ddl.DataBind();
//插入默认选中项
if (id == "0")
{
ListItem item = new ListItem("---请选择---", "0");
//this.ddlProvince.Items.Add(item);
ddl.Items.Insert(0, item);
}
}
private void GetProvince()
{
LoadData("0", this.ddlProvince);
}
private int Sum(int x, int y)
{
int num = x + y;
return num;
}
protected void ddlProvince_SelectedIndexChanged(object sender, EventArgs e)
{
string id = this.ddlProvince.SelectedItem.Value;
if (id != "0")
{
LoadData(id, this.ddlCity);
string selectid = this.ddlCity.SelectedItem.Value;
LoadData(selectid, this.ddlArear);
}
else
{
ddlCity.Items.Clear();
ddlArear.Items.Clear();
}
}
protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
{
string id = this.ddlCity.SelectedItem.Value;
LoadData(id, this.ddlArear);
}
}