我的数据库中有两个"大"表。这两个表包含大量州和国家/地区。
发出和请求
我在更新面板中有两个下拉列表控制器,每次用户在部分页面回发的国家/地区下拉列表中更改国家/地区时,我都会尝试重新填充州下拉列表。我目前在国家/地区下拉列表中更改国家/地区时收到错误:
数据绑定:"System.Data.DataRow"不包含名为"Name"的属性。
我不确定为什么它会给我这个错误,但所有输入都是有帮助的。我有一个解决方案,可以给我所需的结果,但它是在JS和客户端完成的。我想改为处理这个服务器端。
下面是国家和州表的缩短示例,以及我当前带有代码隐藏的代码。
我还做了一个快速函数来创建状态数据表。(如果这有帮助)
Form.ascx
<asp:UpdatePanel runat="server" ID="pnlAddressSelect" UpdateMode="Conditional">
<ContentTemplate>
<!-- State -->
<div class="fieldGroup">
<p class="inputLabel b f4"><span class="required-label"></span>State/Province:</p>
<asp:DropDownList runat="server" ID="ddlState" AppendDataBoundItems="true" CssClass="inputField" autocomplete="new-state" />
</div>
<!-- Country -->
<div class="fieldGroup">
<p class="inputLabel b f4"><span class="required-label"></span>Country:</p>
<asp:DropDownList runat="server" ID="ddlCountry" AppendDataBoundItems="true" CssClass="inputField" autocomplete="new-country" OnSelectedIndexChanged="DdlCountry_SelectedValueChanged" />
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="ddlCountry" EventName="SelectedIndexChanged" />
</Triggers>
</asp:UpdatePanel>
Form.ascx.vb(代码隐藏)
Public Function GetTable() As DataTable
Dim table As New DataTable
With table
With.Columns
.Add("value", GetType(String))
.Add("Name", GetType(String))
End With
With.Rows
.Add("220-220", "Kaliro")
.Add("220-221", "Manafwa")
.Add("220-222", "Namutumba")
.Add("22-05", "Vidin")
.Add("22-06", "Vratsa")
.Add("22-07", "Gabrovo")
.Add("222-AK", "Alaska")
.Add("222-AL", "Alabama")
.Add("222-AR", "Arkansas")
.Add("223-AR", "Artigas")
.Add("223-CA", "Canelones")
.Add("223-DU", "Durazno")
End With
End With
Return table
End Function
Public Property CountryCode As String = "-1"
ReadOnly DefaultOption As ListItem = New ListItem("--Select--", "-1")
Protected Sub DdlCountry_SelectedValueChanged(ByVal sender As Object, ByVal e As EventArgs)
'Save the selected country code to the public property
CountryCode = ddlCountry.SelectedValue
'Populate States
'Dim StatesSQL As String = "..spGetAllStatesForSearch",
' StatesTable As DataTable = DataAction.GetDataTable(StatesSQL)
' The above will pull the DataTable from the Database but for this
' example. I provided a function that creates the table specifically
' for the states. The country is not needed in this as this value can
' be manually set for this example.
Dim StatesTable As DataTable = GetTable()
Dim FilteredStates As List(Of DataRow) = (From state In StatesTable.AsEnumerable Select state Where state.Field(Of String)("value").StartsWith(CountryCode & "-")).ToList
'Clear the options from the dropdown list first before binding
ddlState.Items.Clear()
ddlState.DataSource = FilteredStates
ddlState.Items.Add(DefaultOption)
ddlState.DataTextField = "Name"
ddlState.DataValueField = "value"
ddlState.DataBind()
' The database has the first row as an empty value,
' So I replace this with the custom option 'DefaultOption' that I defined above
ddlState.Items.RemoveAt(1)
End Sub
出于此示例的目的,我限制下面每个表中的条目。由于它们有超过 3K 个条目组合,除了表结构之外,并没有真正为问题增加那么多价值。
国家/地区表
| (String) | (String) |
| id | country |
----------------------------
| 222 | United States |
| 22 | Bulgaria |
| 220 | Uganda |
| 223 | Uruguay |
----------------------------
状态表
| (String) | (String) |
| value | Name |
--------------------------
| 220-220 | Kaliro |
| 220-221 | Manafwa |
| 220-222 | Namutumba |
| 22-05 | Vidin |
| 22-06 | Vratsa |
| 22-07 | Gabrovo |
| 222-AK | Alaska |
| 222-AL | Alabama |
| 222-AR | Arkansas |
| 223-AR | Artigas |
| 223-CA | Canelones |
| 223-DU | Durazno |
-------------------------
我想了想我的问题,开始浏览我可用的方法,我看到了CopyToDataTable
这是DataTableExtensions类的方法。所以我改变了:
源语言
Dim FilteredStates As List(Of DataRow) = (From state In StatesTable.AsEnumerable Select state Where state.Field(Of String)("value").StartsWith(CountryCode & "-")).ToList
自
更新
Dim FilteredStates As DataTable = (From state In StatesTable.AsEnumerable Select state Where state.Field(Of String)("value").StartsWith(CountryCode & "-")).CopyToDataTable
这给了我想要的结果。