2012年3月12日 星期一

[ASP.NET]CascadingDropDown 以郵局街道資料為例

實作完成畫面：
cascadingdropdown1
準備作業：
1. 郵局下載最新的zipcode街道資料檔，3+2郵遞區號資料Excel檔 (自解壓縮檔) 99/12
2. 將資料檔匯入sql server資料庫

實作一：建立GetRoadDropDown.asmx web service，提供GetCityDropDown, GetRegionDropDown, GetRoadDropDown等method，參考後面程式碼
實作二：建立linetest.aspx webpage，示範cascadingdropdown效果，另外在RoadDropDownList的Init加入onchange的事件動作
linetest.aspx：
 
<!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>
<br />
</div>
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</asp:ToolkitScriptManager>
<br />
<span lang="zh-tw">街道選單：</span><asp:DropDownList ID="cityDropDownList" 
runat="server">
</asp:DropDownList>
<asp:CascadingDropDown ID="cityDropDownList_CascadingDropDown" runat="server" LoadingText="請稍待...." PromptText="請選擇" 
Enabled="True" TargetControlID="cityDropDownList" ServicePath="GetRoadDropDown.asmx" ServiceMethod="GetCityDropDown" Category="city">
</asp:CascadingDropDown>
<asp:DropDownList ID="regionDropDownList" runat="server">
</asp:DropDownList>
<asp:CascadingDropDown ID="regionDropDownList_CascadingDropDown" runat="server" 
Category="region" Enabled="True" LoadingText="請稍待...." 
ParentControlID="cityDropDownList" PromptText="請選擇" 
ServiceMethod="GetRegionDropDown" TargetControlID="regionDropDownList"
ServicePath="GetRoadDropDown.asmx">
</asp:CascadingDropDown>
<span lang="zh-tw">&nbsp;</span><asp:DropDownList ID="roadDropDownList" 
runat="server">
</asp:DropDownList>
<asp:CascadingDropDown ID="roadDropDownList_CascadingDropDown" runat="server" 
Category="road" Enabled="True" LoadingText="請稍待...." 
ParentControlID="regionDropDownList" PromptText="請選擇" 
ServiceMethod="GetRoadDropDown" ServicePath="GetRoadDropDown.asmx" 
TargetControlID="roadDropDownList">
</asp:CascadingDropDown>
<br />
<span lang="zh-tw">地址欄位：</span><asp:TextBox ID="TextBox1" runat="server" 
Width="300px"></asp:TextBox>
<br />
<br />
<span lang="zh-tw">說明：<br />
&nbsp;&nbsp;&nbsp; 1.街道選單皆為為cascading dropdown(連動選單)，透過asmx取得DropDownList項目<br />
&nbsp;&nbsp;&nbsp; 2.選擇街道項目後，自動填入地址欄位<br />
</span>
</form>
</body>
</html>
 
linetest.aspx.vb：
Protected Sub roadDropDownList_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles roadDropDownList.Init
        CType(sender, DropDownList).Attributes.Add("onchange", "document.forms[0].TextBox1.value=document.forms[0].cityDropDownList.value+document.forms[0].regionDropDownList.value+document.forms[0].roadDropDownList.value;")
    End Sub






GetRoadDropDown.asmx：
Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
Imports AjaxControlToolkit
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Specialized
 
 
' 若要允許使用 ASP.NET AJAX 從指令碼呼叫此 Web 服務，請取消註解下一行。
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class GetRoadDropDown
Inherits System.Web.Services.WebService
 
<WebMethod()> _
Public Function GetRoadDropDown(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim roadsql As New SqlDataSource
Dim roaddv As DataView
Dim values As New List(Of CascadingDropDownNameValue)()
Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
 
roadsql.ConnectionString = ConfigurationManager.ConnectionStrings("LINEJOBConnectionString").ToString
roadsql.SelectCommand = "SELECT DISTINCT road FROM zipcode WHERE (city = @city) AND (region = @region) order by road"
roadsql.SelectParameters.Add("city", System.TypeCode.String, kv("city"))
roadsql.SelectParameters.Add("region", System.TypeCode.String, kv("region"))
 
roaddv = roadsql.Select(DataSourceSelectArguments.Empty)
 
If roaddv.Count > 0 Then
For Each dr As DataRow In roaddv.Table.Rows
Dim roaditem As New CascadingDropDownNameValue
roaditem.name = dr("road")
roaditem.value = dr("road")
values.Add(roaditem)
Next
End If
 
Return values.ToArray
End Function
 
<WebMethod()> _
Public Function GetRegionDropDown(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim roadsql As New SqlDataSource
Dim roaddv As DataView
Dim values As New List(Of CascadingDropDownNameValue)()
Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
 
roadsql.ConnectionString = ConfigurationManager.ConnectionStrings("LINEJOBConnectionString").ToString
roadsql.SelectCommand = "SELECT DISTINCT region, LEFT(zipcode, 3) AS zipcode3 FROM zipcode WHERE (city = @city) ORDER BY zipcode3"
roadsql.SelectParameters.Add("city", System.TypeCode.String, kv("city"))
 
roaddv = roadsql.Select(DataSourceSelectArguments.Empty)
 
If roaddv.Count > 0 Then
For Each dr As DataRow In roaddv.Table.Rows
Dim roaditem As New CascadingDropDownNameValue
roaditem.name = dr("region")
roaditem.value = dr("region")
values.Add(roaditem)
Next
End If
 
Return values.ToArray
End Function
 
<WebMethod()> _
Public Function GetCityDropDown(ByVal knownCategoryValues As String, ByVal category As String) As CascadingDropDownNameValue()
Dim roadsql As New SqlDataSource
Dim roaddv As DataView
Dim values As New List(Of CascadingDropDownNameValue)()
Dim kv As StringDictionary = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)
 
roadsql.ConnectionString = ConfigurationManager.ConnectionStrings("LINEJOBConnectionString").ToString
roadsql.SelectCommand = "SELECT DISTINCT city, LEFT(zipcode, 1) AS zipcode3 FROM zipcode ORDER BY zipcode3"
roaddv = roadsql.Select(DataSourceSelectArguments.Empty)
 
If roaddv.Count > 0 Then
For Each dr As DataRow In roaddv.Table.Rows
Dim roaditem As New CascadingDropDownNameValue
roaditem.name = dr("city")
roaditem.value = dr("city")
values.Add(roaditem)
Next
End If
 
Return values.ToArray
End Function
End Class






postxls
postsql









http://blog.sina.com.cn/s/blog_6c762bb30100rdgf.html 



http://www.tracefact.net/Asp-Net/CascadingDropDown.aspx 



http://www.veryhuo.com/a/view/14132.html 

关于使用<ajax:CascadingDropDown>控件出现的500错误和ScriptManager出现的注册表冲突错误解决方案 

<ajax:CascadingDropDown>控件为Ajax的联动DropDown控件 



<ajax:CascadingDropDown>控件的500错误意思是找不到连接地址 



以下面的代码为例： 



<ajax:CascadingDropDown ID="CascadingDropDown1" runat="server" ParentControlID="ddlProvince" ServicePath="SNWebService.asmx" ServiceMethod="GetCityContents" Category="City" TargetControlID="ddlCity" PromptText="请选择城市" LoadingText="城市加载中..."> 

</ajax:CascadingDropDown> 



其中ServicePath="SNWebService.asmx"属性指定的是控件指定的服务路径是SNWebService.asmx文件 



SNWebService.asmx文件在App_Code文件夹下（也不一定是必须放在这个文件夹下，如果不放在App_Code文件中则此处的ServicePath属性要指定文件的路径）， 



找到这个文件并打开它，能看到里面指定的连接字符串（ConnectionString)看看它指定的web.config文件中的连接字符串，一般就是这出错了。 



ScriptManager出现的注册表冲突错误： 

一般是在web.config文件中同时出现了不同的Ajax注册节点，有的用VS2005的朋友在使用Ajax的时候给使用一个叫 



ASPAJAXExtSetup.msi的工具对VS进行补充，他安装的是Ajax1.0的版本，而VS2008自带的是Ajax3.5版本，如果Web.config文件中同时存在两种版本就会照成这类型的错误，让去掉一个版本，只需要将相应的节点去掉就可以了。 



用VS2008的一般以3.5版本为主。 



有类似配置节的屏掉： 



<!--<add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/> 

<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/> 

<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/> 

<add assembly="System.Web.Extensions.Design, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>--> 

<!--<add assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/> 

<add assembly="Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>--> 



<!--<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>--> 



添加： 



<add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/> 

<add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/> 

<add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/> 

<add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/> 

<add assembly="System.Data.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> 



等3.5版本的节点 








Atlas学习手记（12）：使用Cas



1．CascadingDropDown控件介绍
2．从数据库读取数据并填充CascadingDropDown
一．CascadingDropDown控件介绍
CascadingDropDown通常叫作级联下拉菜单，本文将通过读取Northwind数据库中的Emplyee、Order、OrderDatail信息作为示例，来展示一下它的使用。示例代码如下：
<atlasToolkit:CascadingDropDown ID="CDD1" runat="server">

<atlasToolkit:CascadingDropDownProperties

TargetControlID="DropDownList2"

Category="Model"

PromptText="Please select a model"

LoadingText="[Loading models]"

ServicePath="CarsService.asmx"

ServiceMethod="GetDropDownContents"

ParentControlID="DropDownList1"

SelectedValue="SomeValue">

</atlasToolkit:CascadingDropDownProperties>

</atlasToolkit:CascadingDropDown>
对于CascadingDropDown需要为它添加CascadingDropDownProperties，有多少个下拉列表，就添加几个CascadingDropDownProperties，主要属性如下：
属性 说明 TargetControlID 指定要扩展的DropDownList的ID Category DropDownList表示的类别名称，在WebMethod中会用到 PromptText 没有选择时显示的文字 LoadingText 加载数据时显示的文字 ServicePath 获取数据的Web Service，为每个DropDownList都要指定 ServiceMethod 获取数据的Web Method ParentControlID 要扩展的DropDownList的父控件ID SelectedValue 默认的选择项的值
二．从数据库读取数据并填充CascadingDropDown
下面用读取Northwind数据库中的Emplyee、Order、OrderDatail信息，看一个完整的示例。在新建一个Web Site后，先在页面的头部加上：
<%@ Register Assembly="AtlasControlToolkit"

Namespace="AtlasControlToolkit"

TagPrefix="atlasToolkit" %>加入三个DropDownList，分别用来显示Emplyee，Order，OrderDatail：
<div>

<h3>

Employee:

<asp:DropDownList ID="ddlEmployees" runat="server" /><br /><br />

&nbsp;&nbsp;Order:

<asp:DropDownList ID="ddlOrders" runat="server" /><br /><br />

&nbsp;&nbsp;Detail:

<asp:DropDownList ID="ddlOrderDetails" runat="server" />

</h3>

</div>
下面我们添加一个Northwind.asmx的Web Service，编写相关的Web Method：
[WebMethod]

public CascadingDropDownNameValue[] GetEmployees(

string knownCategoryValues, string category)

{

string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection connection = new

SqlConnection(connectionString);

SqlCommand command = new SqlCommand("SELECT * FROM Employees");

command.Connection = connection;

connection.Open();

SqlDataAdapter adapter = new SqlDataAdapter(command);

DataSet dataSet = new DataSet();

adapter.Fill(dataSet);

command.Connection.Close();

DataTable tbl = dataSet.Tables[0];

List<CascadingDropDownNameValue> values =

new List<CascadingDropDownNameValue>();

foreach (DataRow dr in tbl.Rows)

{

string sEmployee = (string)dr["FirstName"] + " " +

dr["LastName"];

int iEmployee = (int)dr["EmployeeID"];

values.Add(new CascadingDropDownNameValue(

sEmployee, iEmployee.ToString()));

}

return values.ToArray();

}
注意Web Method的参数签名是不可以改变的，并且它最后返回的是名-值对这种形式的数组。
整个完整后的Web Service如下：
完整代码
using System;

using System.Web;

using System.Collections;

using System.Configuration;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Web.Services;

using System.Web.Services.Protocols;

using AtlasControlToolkit;

using System.Data;

using System.Data.SqlClient;



/**//// <summary>

/// Summary description for Northwind

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

public class Northwind : System.Web.Services.WebService {



public Northwind () {



//Uncomment the following line if using designed components

//InitializeComponent();

}



[WebMethod]

public CascadingDropDownNameValue[] GetEmployees(

string knownCategoryValues, string category)

{

string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection connection = new

SqlConnection(connectionString);



SqlCommand command = new SqlCommand("SELECT * FROM Employees");



command.Connection = connection;

connection.Open();



SqlDataAdapter adapter = new SqlDataAdapter(command);

DataSet dataSet = new DataSet();



adapter.Fill(dataSet);

command.Connection.Close();



DataTable tbl = dataSet.Tables[0];



List<CascadingDropDownNameValue> values =

new List<CascadingDropDownNameValue>();

foreach (DataRow dr in tbl.Rows)

{

string sEmployee = (string)dr["FirstName"] + " " +

dr["LastName"];

int iEmployee = (int)dr["EmployeeID"];

values.Add(new CascadingDropDownNameValue(

sEmployee, iEmployee.ToString()));

}

return values.ToArray();

}



[WebMethod]

public CascadingDropDownNameValue[] GetOrdersByEmployee(

string knownCategoryValues,

string category)

{

StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

int iEmployee;

if (!kv.ContainsKey("Employee") || !Int32.TryParse(kv["Employee"], out iEmployee))

{

return null;

}



string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection connection = new

SqlConnection(connectionString);



SqlCommand command =

new SqlCommand("SELECT OrderID FROM Orders WHERE EmployeeID = " + iEmployee);



command.Connection = connection;

connection.Open();



SqlDataAdapter adapter = new SqlDataAdapter(command);

DataSet dataSet = new DataSet();



adapter.Fill(dataSet);

command.Connection.Close();



DataTable tbl = dataSet.Tables[0];



List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();

foreach (DataRow dr in tbl.Rows)

{

string sOrder = dr["OrderID"].ToString();

int iOrder = (int)dr["OrderID"];

values.Add(new CascadingDropDownNameValue(

sOrder, iOrder.ToString()));

}

return values.ToArray();

}



[WebMethod]

public CascadingDropDownNameValue[] GetDetailsByOrder(

string knownCategoryValues,

string category)

{

StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

int iOrder;

if (!kv.ContainsKey("Order") || !Int32.TryParse(kv["Order"], out iOrder))

{

return null;

}



string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection connection = new

SqlConnection(connectionString);



SqlCommand command =

new SqlCommand("SELECT UnitPrice,OrderID FROM [Order Details] WHERE OrderID = " + iOrder);



command.Connection = connection;

connection.Open();



SqlDataAdapter adapter = new SqlDataAdapter(command);

DataSet dataSet = new DataSet();



adapter.Fill(dataSet);

command.Connection.Close();



DataTable tbl = dataSet.Tables[0];



List<CascadingDropDownNameValue> values = new List<CascadingDropDownNameValue>();

foreach (DataRow dr in tbl.Rows)

{

string UnitPrice = dr["UnitPrice"].ToString();

int intOrder = (int)dr["OrderID"];

values.Add(new CascadingDropDownNameValue(

UnitPrice, intOrder.ToString()));

}

return values.ToArray();

}　

}
在Web.config中设置连接信息：
<connectionStrings>

<add name="ConnectionString" connectionString="Data Source=RJ-097;User ID=sa;Password=sa;Initial Catalog=Northwind" providerName="System.Data.SqlClient"/>

</connectionStrings>
这时我们再添加CascadingDropDown控件，设置它的属性如下：
<atlasToolkit:CascadingDropDown ID="CascadingDropDown1" runat="server">

<atlasToolkit:CascadingDropDownProperties Category="Employee" ParentControlID=""

PromptText="Select Employee" SelectedValue="" ServiceMethod="GetEmployees" ServicePath="Northwind.asmx"

TargetControlID="ddlEmployees" />

<atlasToolkit:CascadingDropDownProperties Category="Order" ParentControlID="ddlEmployees"

PromptText="Select Order" SelectedValue="" ServiceMethod="GetOrdersByEmployee"

ServicePath="Northwind.asmx" TargetControlID="ddlOrders" />

<atlasToolkit:CascadingDropDownProperties Category="OrderDetail" ParentControlID="ddlOrders"

PromptText="Select OrderDetail" SelectedValue="" ServiceMethod="GetDetailsByOrder"

ServicePath="Northwind.asmx" TargetControlID="ddlOrderDetails" />

</atlasToolkit:CascadingDropDown>






[初涉asp.net]解决了个拖了很久的问题……



不知道这个问题困扰了我多久，不知道在网上搜了不知多少遍，也不知道向高手请教过多少次，最后还是无果而终……
今晚终于把它给解决掉了！可能对于初涉asp.net的我来说，这是遇到的麻烦的问题之一了，但对于各位asp.net的前辈们
来说这或许只是小菜一碟~

问题描述：
      GridView中通过CascadingDropDown实现了二级联动，用于“编辑”的时候方便修改一些固定信息，比如
选择国家、城市等，但是在点编辑后GridView中先前的信息就没了，如下图：
点“编辑”前：


点“编辑”后：


可以看到，之前的系别和专业都没了……
实现效果(点“编辑”后)：


解决方法：
      前台：

      要实现联动的每个DropDownList都对应有一个HiddenField，用于存放“编辑”前的值     

<asp:TemplateField HeaderText="系别" >
    <ItemTemplate>
        <%# Eval("系别名")%>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:HiddenField ID="HDFDep" runat="server" Value='<%# Eval("系别名") %>' />
        <asp:DropDownList ID="DDLDep" runat="server" Width="150px" />
        <cc1:CascadingDropDown ID="ccdDepart" TargetControlID="DDLDep" Category="Depart" LoadingText="系别加载中" 
        ServicePath="ClassService.asmx" ServiceMethod="GetDeparts" runat="server" />
    </EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="专业" >
    <ItemTemplate>
        <%# Eval("专业名")%>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:HiddenField ID="HDFMajor" runat="server" Value='<%# Eval("专业名") %>' />
        <asp:DropDownList ID="DDLMajor" runat="server" Width="110px" />
        <cc1:CascadingDropDown ID="ccdMajor" TargetControlID="DDLMajor" Category="Major"
        LoadingText="专业加载中" ServicePath="ClassService.asmx" ServiceMethod="GetMajorsForDepart"
        ParentControlID="DDLDep" runat="server" />
    </EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="班级">
    <ItemTemplate>
        <%# Eval("班级")%>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:HiddenField ID="HDFClass" Value='<%# Eval("班级") %>' runat="server" />
        <asp:DropDownList ID="DDLClass" runat="server" width="33px" />
        <cc1:CascadingDropDown ID="ccdClass" TargetControlID="DDLClass" Category="Class"
        LoadingText="班级加载中" ServicePath="ClassService.asmx" ServiceMethod="GetClassesForMajor"
        ParentControlID="DDLMajor" runat="server">
        </cc1:CascadingDropDown>
    </EditItemTemplate>
</asp:TemplateField>
 
      后台：       
      添加using AjaxControlToolkit(前提是你已经在工具箱中添加了AjaxControlToolkit)，然后在GridView的
onRowDataBound的方法中添加如下代码

if (((CascadingDropDown)e.Row.FindControl("ccdDept")) != null)//ccdDept为与系别DropDownList关联的CascadingDropDown
{    CascadingDropDown ccddept = (CascadingDropDown)e.Row.FindControl("ccdDept");    ccddept.SelectedValue = ((HiddenField)e.Row.FindControl("HDFDept")).Value;//获取HiddenField的值
}

      WebService中：
      无需做修改，还是把部分代码贴上来

[WebMethod]public CascadingDropDownNameValue[] GetDeparts(string knownCategoryValues, string category){    SqlConnection conn = new SqlConnection(@"server=/*服务器名*/;database=/*数据库名*/;Trusted_Connection=True;");
    string sql = "SELECT * FROM .";    SqlCommand cmm = new SqlCommand(sql, conn);    conn.Open();    SqlDataReader dr = cmm.ExecuteReader();    List<CascadingDropDownNameValue> departList = new List<CascadingDropDownNameValue>();
    while (dr.Read())    {        departList.Add(new CascadingDropDownNameValue(dr[1].ToString(), dr[0].ToString()));    }    dr.Close();
    return departList.ToArray();
}

      做好了回头来看其实还是不难，只是因为以前没用过Ajax，没使用using AjaxControlToolkit
      希望对大家有帮助，Enjoy~！ 










張貼者：



於







































沒有留言:















張貼留言


















        

      









訂閱：
張貼留言 (Atom)