VB.NET中GridView分页异常：仅显示2页而非79页的解决需求

问题背景 #

绑定到含2050条记录数据库表的GridView，出现分页显示异常：

• 按Math.Ceiling(2050/26)计算应显示79页，但实际仅显示2页
• 若前后端每页记录数统一为25，分页控件显示0页

关键变量取值：

• totalRecords：2050（表总记录数）
• pageSize：26（测试时从25修改而来）
• Totalpages：79（通过Math.Ceiling(totalRecords / pageSize)计算得出）

根本原因 #

1. 前后端分页参数不匹配：前端GridView设置PageSize="25"，后端全局变量pageSize=26，导致SQL查询加载26条数据后，GridView仅显示25条作为第一页，剩余1条作为第二页，分页控件误判总页数为2。
2. GridView默认分页逻辑限制：GridView默认根据当前绑定的数据集行数计算分页，而非数据库总记录数。当pageSize统一为25时，首次仅加载25条数据，GridView误认为总记录数就是25，与代码中计算的79页冲突，导致控件显示异常。

解决方案 #

步骤1：统一前后端pageSize #

确保前端GridView的PageSize属性与后端全局变量pageSize完全一致：

• 方案一：修改前端代码

  <asp:GridView ... PageSize="26" AllowPaging="true" OnPageIndexChanging="GridView1_PageIndexChanging">
  

• 方案二：修改后端全局变量

  Dim pageSize As Integer = 25
  

步骤2：设置VirtualItemCount属性 #

GridView的VirtualItemCount属性用于指定数据库总记录数，让控件基于这个值计算分页，而非当前绑定的数据集行数。在BindGridData方法中绑定数据前添加该设置：

GridView1.VirtualItemCount = totalRecords

修改后的BindGridData核心代码片段：

If totalRecords > 0 Then
    GridView1.DataSource = dt
    GridView1.VirtualItemCount = totalRecords ' 关键：告诉GridView数据库总记录数
    GridView1.DataBind()

    CountL.Text = dt.Rows.Count & " Records " & totalRecords & " =GetTotalRecordCount() " & (pageIndex + 1) & " of " & (Math.Ceiling(totalRecords / pageSize)) & " Pages"
Else
    CountL.Text = "No records found."
End If

效果验证 #

设置完成后，GridView会根据VirtualItemCount和PageSize计算出正确的79页，分页控件将正常显示所有页码。

完整相关代码 #

前端GridView代码 #

<asp:GridView ... PageSize="25" AllowPaging="true" OnPageIndexChanging="GridView1_PageIndexChanging">

全局变量 #

Dim currentPageIndex As Integer = 0  ' 存储当前页码索引（从0开始）
Dim pageSize As Integer = 26      ' 定义每页记录数（测试设为26）
Dim sortColumn As String = ""     ' 当前排序列名（初始为空）
Dim sortDirection As SortDirection = SortDirection.Ascending ' 当前排序方向（默认升序）

页面加载方法 #

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
        BindGridData()
    End If
End Sub

分页事件处理 #

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    BindGridData(pageIndex:=e.NewPageIndex)
End Sub

数据绑定方法 #

Protected Sub BindGridData(Optional ByVal pageIndex As Integer = 0,
                          Optional ByVal sortColumn As String = "",
                          Optional ByVal sortDirection As String = "ASC")
    Dim sqlConn1 As New OdbcConnection(myConnectionString1)
    Try
        sqlConn1.Open()

        Dim queryString As String = "SELECT * FROM MyTable "

        ' 添加排序条件
        If Not String.IsNullOrEmpty(sortColumn) Then
            queryString &= " ORDER BY " & sortColumn & " " & sortDirection.ToString()
        End If
        ' 添加分页逻辑（OFFSET/FETCH）
        queryString &= " OFFSET " & (pageIndex * pageSize) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY "

        Dim adapter As OdbcDataAdapter = New OdbcDataAdapter(queryString, sqlConn1)
        Dim dt As DataTable = New DataTable()
        adapter.Fill(dt)
        Dim totalRecords As Integer = GetTotalRecordCount() ' 获取数据库总记录数

        ' 绑定数据到GridView
        If totalRecords > 0 Then
            GridView1.DataSource = dt
            GridView1.VirtualItemCount = totalRecords ' 新增设置
            GridView1.DataBind()

            CountL.Text = dt.Rows.Count & " Records " & totalRecords & " =GetTotalRecordCount() " & (pageIndex + 1) & " of " & (Math.Ceiling(totalRecords / pageSize)) & " Pages"
        Else
            CountL.Text = "No records found."
        End If

        sqlConn1.Close()
    Catch ex As Exception
        ' 异常处理
    End Try
End Sub

获取总记录数方法 #

Private Function GetTotalRecordCount() As Integer
        Dim sqlConn2 As New OdbcConnection(myConnectionString1)
        Try
            sqlConn2.Open()

            Dim countQuery As String = "SELECT COUNT(*) FROM MyTable "
            Dim cmd As OdbcCommand = New OdbcCommand(countQuery, sqlConn2)
            Dim totalRecords As Integer = CInt(cmd.ExecuteScalar())

            Return totalRecords
        Finally
            sqlConn2.Close()
        End Try
    End Function

内容的提问来源于stack exchange，提问作者Dave