注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

幽狼De博客

记录成长中的点点滴滴

 
 
 

日志

 
 

[原创][分页] GridView 自定义查询翻页   

2010-03-01 13:59:01|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

[原创][分页] GridView 自定义查询翻页

GridView 自定义查询翻页
1.自定义LINKBUTTON
2.dropdownlist翻页
3.过滤引号及用 myAdapter.SelectCommand.Parameters.Add 方法过滤,防止SQL注入
4.先显示所有值,后可查询关键字再翻页

.ASPX

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="MemberList.aspx.vb" Inherits="Manage_Members_MemberList" Debug="true"%>

<!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>MemberList</title>
     
<link rel="stylesheet" href="../Css/Moban_MM.css" type="text/css" />
</head>
<body>
     
<form id="form1" runat="server">
        
<div>
        
<table width="100%" height="100%" border="0" align="center" cellpadding="0" cellspacing="0">
      
<tr>
        
<td><table width="90%" border="0" align="center" cellpadding="2" cellspacing="2">
          
<tr>
            
<td style="height: 45px"><table style="border-collapse:collapse" width="100%" border="1" cellspacing="1" cellpadding="1" bordercolor="#CCCCCC">
              
<tr>
                
<td bgcolor="#999999" height="21"><b><span Class =class="a180"><font color="#FFFFFF">会员列表 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                    
&nbsp; 
                    
<asp:TextBox ID="keywords" runat="server" BackColor="NavajoWhite"></asp:TextBox>
                     
<asp:Button ID="btnSearch" runat="server" OnClick="searchGridView" Text="查询" /></font></span></b></td>
              
</tr>
            
</table> 
                
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
                    BackColor
="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2"
                    DataKeyNames
="U_ID"  ForeColor="Black" GridLines="None"
                    Width
="100%" 
                    OnRowDeleting
="GridView1_OnRowDeleting"
                    OnRowDataBound
="GridView1_OnRowDataBound" 
                    PagerSettings
-Visible="false"  
                    OnPageIndexChanging 
="GridView1_OnPageIndexChanging"
                    
                    
>
                    
<FooterStyle BackColor="Tan" />
                    
<Columns>
                        
<asp:TemplateField HeaderText="编号">
                            
<HeaderTemplate>
                                
<asp:Label ID="Label1" runat="server" Text="编号"></asp:Label><asp:CheckBox ID="chkAll" Visible="false"  runat="server" ToolTip="全选" Text="编号" />
                            
</HeaderTemplate>
                            
<ItemTemplate >
                                
<asp:CheckBox ID="chkItem" Visible="false"  runat="server" /><%#Container.DataItemIndex + 1%>
                            
</ItemTemplate>
                        
</asp:TemplateField>
                        
<asp:BoundField DataField="U_GameID" HeaderText="游戏ID" SortExpression="U_GameID" />
                        
<asp:BoundField DataField="U_Name" HeaderText="用户名" SortExpression="U_Name" />
                        
<asp:TemplateField HeaderText="性别" SortExpression="U_Sex">
                            
<EditItemTemplate>
                                
<asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("U_Sex") %>' />
                            </EditItemTemplate>
                            
<ItemTemplate>
                                
<%#isSex(Eval("U_Sex"))%>
                            
</ItemTemplate>
                        
</asp:TemplateField>
                        
<asp:BoundField DataField="U_EMail" HeaderText="Email" SortExpression="U_EMail" />
                        
<asp:BoundField DataField="U_UserTruename" HeaderText="真实姓名" SortExpression="U_UserTruename" />
                        
<asp:BoundField DataField="U_MsnQQ" HeaderText="Msn/QQ" SortExpression="U_MsnQQ" />
                        
<asp:BoundField DataField="U_RegDatetime" HeaderText="注册时间" SortExpression="U_RegDatetime" />
                        
<asp:CommandField DeleteText="&lt;span onclick=&quot;return confirm('确定删除吗?')&quot;&gt;删除&lt;/span&gt;"
                            HeaderText
="操作" ShowDeleteButton="True" ShowEditButton="True" />
                    
</Columns>
                    
<PagerTemplate> 
                         
                    
</PagerTemplate>
                    
<RowStyle HorizontalAlign="Center" />
                    
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                    
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                    
<HeaderStyle BackColor="Tan" Font-Bold="True" />
                    
<AlternatingRowStyle BackColor="PaleGoldenrod" />
                    
<PagerSettings Visible="False" />
                
</asp:GridView>
                
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
                
&nbsp; &nbsp; &nbsp; 
                
                
<asp:Panel ID="PanelHyperLink" Visible="false" runat="server" Height="50px" Width="100%" HorizontalAlign="right" >
                总共有 [
<asp:Label ID="myRecordCount1" runat="server" Text="Label"></asp:Label>]
                条记录; 页数 [
<asp:Label ID="myPageIndex1" runat="server" Text="Label"></asp:Label>/<asp:Label ID="myPageCount1" runat="server" Text="Label"></asp:Label>&nbsp;;  
                
<asp:HyperLink ID="PageFirst1"  runat="server">首  页</asp:HyperLink> | 
                
<asp:HyperLink ID="PagePrevious1" runat="server">上一页</asp:HyperLink> |
                
<asp:HyperLink ID="PageNext1" runat="server">下一页</asp:HyperLink> |
                
<asp:HyperLink ID="PageLast1" runat="server">尾  页</asp:HyperLink>
                    
                
<asp:DropDownList AppendDataBoundItems="true" ID="myDDL1" runat="server">
                
<asp:ListItem Value="-1">跳转→</asp:ListItem>
                
</asp:DropDownList>
                
</asp:Panel>
                
                
<asp:Panel ID="PanelLinkButton" runat="server" Height="50px" Width="100%" HorizontalAlign="right" >
                 总共有 [
<asp:Label ID="myRecordCount" runat="server" Text="Label"></asp:Label>]
                条记录; 页数 [
<asp:Label ID="myPageIndex" runat="server" Text="Label"></asp:Label>/<asp:Label ID="myPageCount" runat="server" Text="Label"></asp:Label>&nbsp;;  
                
                    
<asp:LinkButton ID="PageFirst" CommandArgument="goPageFirst" OnClick="pageButtonClick" runat="server">首 页</asp:LinkButton> |
                    
<asp:LinkButton ID="PagePrevious" CommandArgument="goPagePrevious" OnClick="pageButtonClick" runat="server">上一页</asp:LinkButton> |
                    
<asp:LinkButton ID="PageNext" CommandArgument="goPageNext" OnClick="pageButtonClick" runat="server">下一页</asp:LinkButton> |
                    
<asp:LinkButton ID="PageLast" CommandArgument="goPageLast" OnClick="pageButtonClick" runat="server">尾 页</asp:LinkButton>
                
<asp:DropDownList AppendDataBoundItems="true" ID="myDDL" runat="server" OnSelectedIndexChanged="myDDL_OnSelectedIndexChanged" AutoPostBack="true" >
                
<asp:ListItem Value="-1">跳转→</asp:ListItem>
                
</asp:DropDownList>
               
</asp:Panel>
                
                
                
                
</td>
          
</tr>
        
</table></td>
      
</tr>
    
</table>
    
</div>
    
</form>
</body>
</html>



.vb

Imports System.Data
Imports System.Data.SqlClient
Imports System
Imports System.Web.UI
Imports System.Web.HttpUtility



Partial Class Manage_Members_MemberListClass Manage_Members_MemberList
    
Inherits System.Web.UI.Page

    
Dim conn As SqlConnection
    
Dim myappclass As New myAppClass
    
Dim sql As String
    
Public myPageSize As String



    
Public Sub Page_Load()Sub Page_Load(ByVal sender As ObjectByVal e As EventArgs) Handles Me.Load

        myPageSize 
= 10  '初始化一页为 10条数据

    

        
If Not Page.IsPostBack Then

            getGridView(
Me.keywords.Text)

        
End If


    
End Sub


    
Sub getGridView()Sub getGridView(ByVal keywords As String)


        conn 
= New SqlConnection(ConfigurationManager.ConnectionStrings("gameDBConnectionString").ConnectionString)
        conn.Open()

        
'  keywords = myappclass.FenHao(keywords)
        keywords = keywords.Trim.ToString


        
If keywords = "" Then

            sql 
= "Select [U_ID],[U_GameID],[U_Name],[U_Sex],[U_Email],[U_UserTruename],[U_MsnQQ],[U_RegDatetime] From [MemTable] Order By "
            sql 
+= " [U_RegDatetime] Desc "


        
Else
            sql 
= "Select [U_ID],[U_GameID],[U_Name],[U_Sex],[U_Email],[U_UserTruename],[U_MsnQQ],[U_RegDatetime] From [MemTable]  "
            
'  sql += " where [U_Name] like '%" + keywords + "%'"
            sql += " where [U_Name] like @U_name"
            sql 
+= " Order By [U_RegDatetime] Desc "

        
End If





        
Dim myAdapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(sql, conn)
        
If keywords <> "" Then
            myAdapter.SelectCommand.Parameters.Add(
New SqlParameter("@U_Name", SqlDbType.NVarChar))
            myAdapter.SelectCommand.Parameters(
"@U_Name").Value = "%" + keywords + "%"

        
End If

        
Dim ds As New DataSet
        myAdapter.Fill(ds, 
"MemTable")


        
Me.GridView1.PageSize = myPageSize
        
Me.GridView1.SelectedIndex = -1


        
Me.GridView1.DataSource = ds.Tables("MemTable").DefaultView
        
' Me.GridView1.PageIndex = 0 default 为 0 
        Me.GridView1.DataBind()


        
'要先绑定GIRDVIEW.DataBind()后才可以使用以下的方法
        CType(Me.FindControl("myRecordCount"), Label).Text = ds.Tables("MemTable").Rows.Count.ToString
        
CType(Me.FindControl("myPageIndex"), Label).Text = Me.GridView1.PageIndex + 1
        
CType(Me.FindControl("myPageCount"), Label).Text = Me.GridView1.PageCount

        
Dim getMyDDL As DropDownList = CType(Me.FindControl("myDDL"), DropDownList)
        getMyDDL.Items.Clear()
        getMyDDL.Items.Add(
New ListItem("跳转→"-1))


        
For i As Integer = 1 To Me.GridView1.PageCount

            getMyDDL.Items.Add(
New ListItem("" + i.ToString + "", i.ToString))

            
If i = Me.GridView1.PageIndex + 1 Then

                getMyDDL.SelectedValue 
= i


            
End If

        
Next


        myAdapter.Dispose()
        ds.Dispose()
        conn.Close()


    
End Sub


    
Protected Sub GridView1_OnRowDeleting()Sub GridView1_OnRowDeleting(ByVal sender As ObjectByVal e As GridViewDeleteEventArgs)
        conn 
= New SqlConnection(ConfigurationManager.ConnectionStrings("gameDBConnectionString").ConnectionString)
        conn.Open()
        
Dim str As String
        
' str = e.RowIndex
        str = Me.GridView1.DataKeys(e.RowIndex).Value.ToString
        sql 
= "Delete From [MemTable] Where U_ID=" + str + ""

        
Dim cmdDel As SqlCommand = New SqlCommand(sql, conn)
        cmdDel.ExecuteNonQuery()
        
Me.GridView1.EditIndex = -1
        getGridView(
Me.keywords.Text)
        conn.Close()

    
End Sub


    
Protected Sub GridView1_OnRowDataBound()Sub GridView1_OnRowDataBound(ByVal sender As ObjectByVal e As GridViewRowEventArgs)

        
If e.Row.RowType = DataControlRowType.DataRow Then

            
'  e.Row.Cells.Item(0).Text = e.Row.RowIndex + 1  

        
End If

    
End Sub


    
Protected Sub GridView1_OnPageIndexChanging()Sub GridView1_OnPageIndexChanging(ByVal sender As ObjectByVal e As GridViewPageEventArgs)

        
' Dim mylinkCommand As LinkButton = CType(Me.FindControl(""), LinkButton)


    
End Sub


    
Protected Sub myDDL_OnSelectedIndexChanged()Sub myDDL_OnSelectedIndexChanged(ByVal sender As ObjectByVal e As EventArgs) 

        
Dim getMyDDL As DropDownList = CType(Me.FindControl("myDDL"), DropDownList)

        
If getMyDDL.SelectedValue = -1 Then
            
Exit Sub

        
End If
        
Me.GridView1.PageIndex = getMyDDL.SelectedValue - 1
        getGridView(
Me.keywords.Text)

    
End Sub


    
Protected Sub pageButtonClick()Sub pageButtonClick(ByVal sender As ObjectByVal e As System.EventArgs)
        
'Dim arg As String = CType(sender,LinkButton).CommandArgument.ToString


        
Dim myLinkButton As LinkButton = CType(sender, LinkButton)

        
Dim arg As String = myLinkButton.CommandArgument.ToString()

        
Select Case arg
            
Case "goPageFirst"

                
Me.GridView1.PageIndex = 0
                getGridView(
Me.keywords.Text)

            
Case "goPagePrevious"
                
If Me.GridView1.PageIndex > 0 Then

                    
Me.GridView1.PageIndex -= 1
                    getGridView(
Me.keywords.Text)

                
End If


            
Case "goPageNext"

                
If Me.GridView1.PageIndex < Me.GridView1.PageCount - 1 Then
                    
Me.GridView1.PageIndex += 1
                    getGridView(
Me.keywords.Text)
                
End If
            
Case "goPageLast"

                
Me.GridView1.PageIndex = Me.GridView1.PageCount - 1
                getGridView(
Me.keywords.Text)

        
End Select

    
End Sub


    
Protected Sub searchGridView()Sub searchGridView(ByVal sender As ObjectByVal e As EventArgs)

        
Me.GridView1.PageIndex = 0
        getGridView(
Me.keywords.Text)

    
End Sub


    
Function isSex()Function isSex(ByVal str As BooleanAs String

        
If str = True Then
            
Return ""
        
Else
            
Return ""

        
End If


    
End Function


End Class


.web.config

<configuration>
 <appSettings/>
 <connectionStrings>
 
  <add name="gameDBConnectionString_At_Home" connectionString="Data Source=OEMUSER;Initial Catalog=gameDB;Persist Security Info=True;User ID=sa;Password=iloveyouBB"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
</configuration>
  评论这张
 
阅读(425)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017