我碰到這個問題是在GridView顯示大量資料時，當我點擊GridView中的按鈕，就會發生(由於該物件目前的狀態，導致作業無效)這個錯誤。要解決這個問題，可以在web.config檔中，在<appSettings>...</appSettings>之間加上aspnet:MaxHttpCollectionKeys就可以解決這個問題，如下：
<appSettings> <add key="aspnet:MaxHttpCollectionKeys" value="2000" /> </appSettings>
但是這樣還是會有問題(我想)，就是當使用者因為查詢條件的關係，查詢出來的資料有可能又超過MaxHttpCollectionKeys的設定值，又會發生這個錯誤。所以另外一個解決的辦法，就是透過分頁再加上SQL指令的配合，就可以解決這個問題，但是方法比較麻煩ㄧ些。
以下為 分頁+SQL指令 範例
test_paging.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="test_paging.aspx.vb" Inherits="testdb_mssql.test_paging" %>
<!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>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<div id="gv_p1" runat="server" style=" width:500px;">
分頁顯示在此處
</div>
</div>
</form>
</body>
</html>
test_paging.aspx.vb
Imports System.Data.SqlClient
Public Class test_paging
Inherits System.Web.UI.Page
Dim prows As Integer = 100 '每一頁多少筆數(此處100筆分成一頁)
Dim this_page As Integer = 1 '目前在第幾頁
Dim maxpage As Integer = 10 '最多顯示頁數(不變動)
Dim showpage As Integer = 10 '最多顯示頁數
Dim str_sql As String = String.Empty
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
If (Not Request("p") Is Nothing) Then
If Regex.IsMatch(Request("p").ToString, "^\d+$") = False Then '數字會回傳true
'不是數字,設定回1
this_page = 1
Else
this_page = CInt(Request("p").ToString)
End If
Else
this_page = 1
End If
Dim dt1 As DataTable = Nothing, tot_dt As DataTable = Nothing
Dim rows_count As Integer = 0 '資料的總筆數
str_sql = "select TOP " & prows & " * from (" & _
"select ROW_NUMBER() OVER(Order by t1.stud_id) as sec,t1.stud_id,t1.stud_name from Student as t1 where t1.stud_id like '401%'" & _
") as ta1 where ta1.sec>" & (CInt(this_page) - 1) * prows
dt1 = queryDataTable(str_sql)
GridView1.DataSource = dt1
GridView1.DataBind()
str_sql = "select count(t1.stud_id) as tot from Student as t1 where t1.stud_id like '401%'"
tot_dt = queryDataTable(str_sql)
If Not tot_dt Is Nothing Then
If tot_dt.Rows.Count = 1 Then
rows_count = tot_dt.Rows(0).Item("tot").ToString() '資料的總筆數
End If
tot_dt.Clear()
tot_dt.Dispose()
End If
If rows_count > 0 Then
Dim str_temp1 As String = String.Empty
's1開始頁數;tot_x1 全部的頁數
Dim s1 As Integer = 1, tot_x1 As Integer = 0
tot_x1 = Fix(rows_count / prows) '總筆數/每一頁多少筆數
'總筆數 求餘數,餘數>0就加1頁
If (CInt(rows_count) Mod prows) > 0 Then
tot_x1 = tot_x1 + 1
End If
'==========================
'頁數變動
If this_page >= 7 Then
If ((tot_x1 + 1) - ((this_page - 6) + 1)) >= maxpage Then
s1 = (this_page - 6) + 1
showpage = maxpage + (this_page - 6)
Else
s1 = ((this_page - 6) + 1) + (((tot_x1 + 1) - ((this_page - 6) + 1)) - maxpage)
showpage = tot_x1
End If
Else
'預設
s1 = 1 '第一頁開始
showpage = maxpage '顯示的頁數
End If
'==========================
Dim str_ago As String = String.Empty, str_back As String = String.Empty, str_num As String = String.Empty
For i As Integer = s1 To tot_x1
str_num = CStr(i)
If str_num.Length = 1 Then
str_num = "0" & str_num
End If
If i = this_page Then
str_temp1 = str_temp1 & "<a style=" & Chr(34) & "text-decoration:none" & Chr(34) & ">" & str_num & "</a> "
If i > 1 Then
str_ago = "<a href=" & Chr(34) & "test_paging.aspx?p=" & (i - 1) & Chr(34) & " style=" & Chr(34) & "text-decoration:none" & Chr(34) & "><</a> "
End If
If i < tot_x1 Then
str_back = "<a href=" & Chr(34) & "test_paging.aspx?p=" & (i + 1) & Chr(34) & " style=" & Chr(34) & "text-decoration:none" & Chr(34) & ">></a> "
End If
Else
str_temp1 = str_temp1 & "<a href=" & Chr(34) & "test_paging.aspx?p=" & i & Chr(34) & ">" & str_num & "</a> "
End If
If i = showpage Then
Exit For
End If
Next
Me.gv_p1.InnerHtml = str_ago & str_temp1 & str_back
Else
Me.gv_p1.InnerHtml = ""
End If
End If
End Sub
Function queryDataTable(ByVal sql_str As String) As DataTable
Dim ds As New DataSet()
Using conn As New SqlConnection("Server=127.0.0.1;Database=test1;uid=test;pwd=test")
Dim command As SqlCommand = New SqlCommand(sql_str, conn)
Dim da As New SqlDataAdapter()
da.SelectCommand = command
da.Fill(ds)
End Using
If ds.Tables.Count > 0 Then
Return ds.Tables(0)
Else
Return New DataTable()
End If
End Function
End Class
P.S SQL指令中的stud_id為學號，sec是自行自訂編號
沒有留言:
張貼留言