<% @ Import namespace = "system. Data. sqlclient" %>
<% @ Import namespace = "system. Data" %>
<SCRIPT runat = "server">
Dim connorthwind as sqlconnection
Dim strsql as string
Dim strselect as string
Dim intstartindex as integer
Dim intendindex as integer
Dim intrecordcount as integer
Dim slave SQL as sqlcommand
Sub page_load
Btnfirst. Text = "Homepage"
Btnprev. Text = "Previous Page"
Btnnext. Text = "next page"
Btnlast. Text = "last page"
Connorthwind = new sqlconnection ("Server = 192.168.4.1; uid = sa; Pwd = 111111; database = yourdbname ")
If not ispostback then
Binddatagrid
End if
End sub
Sub binddatagrid
Dim cmdselect as sqlcommand
Dim dtrrecordcount as sqldatareader
Intendindex = dgrdproducts. pagesize
Cmdselect = new sqlcommand ("newspaged", connorthwind)
Cmdselect. commandtype = commandtype. storedprocedure
Cmdselect. Parameters. Add ("@ pageindex", intstartindex)
Cmdselect. Parameters. Add ("@ pagesize", intendindex)
Connorthwind. open ()
Dtrrecordcount = cmdselect. executereader ()
While dtrrecordcount. Read ()
Intrecordcount = dtrrecordcount (0)
End while
Dgrdproducts. virtualitemcount = (intrecordcount/dgrdproducts. pagesize)
Dtrrecordcount. nextresult ()
Dgrdproducts. datasource = dtrrecordcount
Dgrdproducts. databind ()
Connorthwind. Close ()
End sub
Sub dgrdproducts_pageindexchanged (S as object, e as datagridpagechangedeventargs)
Intstartindex = E. newpageindex
Dgrdproducts. currentpageindex = E. newpageindex
Binddatagrid
End sub
Sub pagerbuttonclick (byval sender as object, byval e as eventargs)
Dim Arg as string = sender. commandargument
Select case ARG
Case "Next"
If (dgrdproducts. currentpageindex <(dgrdproducts. pagecount-1) then
Dgrdproducts. currentpageindex + = 1
End if
Case "Prev"
If (dgrdproducts. currentpageindex> 0) then
Dgrdproducts. currentpageindex-= 1
End if
Case "last"
Dgrdproducts. currentpageindex = (dgrdproducts. pagecount-1)
Case else
'Page number
Dgrdproducts. currentpageindex = system. Convert. toint32 (ARG)
End select
Intstartindex = dgrdproducts. currentpageindex
Binddatagrid
End sub
Sub dgrdproducts_editcommand (S as object, e as datagridcommandeventargs)
Dgrdproducts. edititemindex = E. Item. itemindex
Intstartindex = dgrdproducts. currentpageindex
Binddatagrid
End sub
Sub dgrdproducts_updatecommand (S as object, e as datagridcommandeventargs)
Dim intarticleid as integer
Dim txttopic as textbox
Dim txteditor as textbox
Dim strtopic as string
Dim streditor as string
Intarticleid = dgrdproducts. datakeys (E. Item. itemindex)
Txttopic = E. Item. cells (1). Controls (0)
Txteditor = E. Item. cells (2). Controls (0)
Strtopic = txttopic. Text
Streditor = txteditor. Text
Strsql = "Update tb_article setTopic = @ topic,"_
&"Editor = @ EditorWhereArticleID = @ ArticleID"
Explain SQL = new sqlcommand (strsql, connorthwind)
Using SQL. Parameters. Add ("@ topic", strtopic)
Using SQL. Parameters. Add ("@ Editor", streditor)
Using SQL. Parameters. Add ("@ ArticleID", intarticleid)
Connorthwind. open ()
Explain SQL. executenonquery ()
Connorthwind. Close ()
Dgrdproducts. edititemindex =-1
Binddatagrid
End sub
Sub dgrdproducts_cancelcommand (S as object, e as datagridcommandeventargs)
Dgrdproducts. edititemindex =-1
Binddatagrid
End sub
</SCRIPT>
<HTML>
<Head> <title> datagridcustompaging. aspx </title> <Body>
<Form runat = "server">
<Asp: DataGrid runat = "server"
Id = "dgrdproducts"
Oneditcommand = "dgrdproducts_editcommand"
Onupdatecommand = "dgrdproducts_updatecommand"
Oncancelcommand = "dgrdproducts_cancelcommand"
Datakeyfield = "a_articleid"
Autogeneratecolumns = "false"
Showheader = "true"
Allowpaging = "true"
Allowcustompaging = "true"
Headerstyle-backcolor = "salmon"
Pagesize = "10"
Onpageindexchanged = "dgrdproducts_pageindexchanged"
Pagerstyle-mode = "numericpages"
Alternatingitemstyle-backcolor = "# eeaaee"
Font-size = "10pt"
Font-name = "verdana"
Cellspacing = "0"
Cellpadding = "3"
Gridlines = "both"
Borderwidth = "1"
Bordercolor = "black"
Pagerstyle-horizontalalign = "right">
<Alternatingitemstyle backcolor = "# eeeeee"> </alternatingitemstyle>
<Columns>
<Asp: boundcolumn
Headertext = "Serial Number"
Datafield = "ArticleID"
Readonly = "true"/>
<Asp: boundcolumn
Headertext = "title"
Datafield = "topic"/>
<Asp: boundcolumn
Headertext = "editer"
Datafield = "Editor"/>
<Asp: editcommandcolumn
Edittext = "Edit! "
Updatetext = "update! "
Canceltext = "Cancel! "/>
<Asp: hyperlinkcolumn
Headertext = "edit"
Datanavigateurlfield = "ArticleID"
Datanavigateurlformatstring = "details. aspx? Id = {0 }"
TEXT = "edit"/>
</Columns>
</ASP: DataGrid>
<Asp: linkbutton id = "btnfirst" onclick = "pagerbuttonclick" runat = "server" font-name = "verdana" font-size = "8pt" forecolor = "Navy" commandargument = "0"> </ASP: linkbutton> & nbsp;
<Asp: linkbutton id = "btnprev" onclick = "pagerbuttonclick" runat = "server" font-name = "verdana" font-size = "8pt" forecolor = "Navy" commandargument = "Prev"> </ASP: linkbutton> & nbsp;
<Asp: linkbutton id = "btnnext" onclick = "pagerbuttonclick" runat = "server" font-name = "verdana" font-size = "8pt" forecolor = "Navy" commandargument = "Next"> </ASP: linkbutton> & nbsp;
<Asp: linkbutton id = "btnlast" onclick = "pagerbuttonclick" runat = "server" font-name = "verdana" font-size = "8pt" forecolor = "Navy" commandargument = "last"> </ASP: linkbutton>
</Form>
</Html>
The following is the stored procedure:
Create procedure newspaged
(
@ Pageindex int,
@ Pagesize int
)
As
Begin
Declare @ pagelowerbound int
Declare @ pageupperbound int
Declare @ rowstoreturn int
-- First set the rowcount
Set @ rowstoreturn = @ pagesize * (@ pageindex + 1)
Set rowcount @ rowstoreturn
-- Set the page Bounds
Set @ pagelowerbound = @ pagesize * @ pageindex
Set @ pageupperbound = @ pagelowerbound + @ pagesize + 1
-- Create a temp table to store the select results
Create Table # pageindex
(
Indexid int identity (1, 1) not null,
ArticleID int,
)
-- Insert into the temp table
Insert into # pageindex (ArticleID)
Select
ArticleID
From
Tablename
Order
ArticleID DESC
-- Return total count
Select count (ArticleID) from tablename
-- Return paged results
Select
O. ArticleID, O. Topic, Editor
From
Tablename O,
# Pageindex
Where
O. ArticleID = pageindex. ArticleID and
Pageindex. indexid> @ pagelowerbound and
Pageindex. indexid <@ pageupperbound
Order
Pageindex. indexid
End
Go