I am working to create a customize paging using oracle database.My code is only gives me the first 20 records from the database and I know because everytime it loads it get the value 20 and it does not show the paging number. I would like to have the paging to be displayed in the first page and the gridview to show the next 20 items every time I change the page nubmer. How can I do this. thanks
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
BindData()
End Sub
Sub BindData()
Dim pageSize As Integer = 20
Dim UpperRowNumber As Integer = ((gvItem.PageIndex + 1) * pageSize) + 1
Dim LowerRowNumber As Integer = (((gvItem.PageIndex + 1) - 1) * pageSize) + 1
Label1.Text = gvItem.PageIndex + 1
Label2.Text = gvItem.PageCount
Dim connectionString As String = ConnectionStrings("ConnectionString").ConnectionString
Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
oOracleConn.Open()
Dim strStringBuilder As StringBuilder
strStringBuilder = New StringBuilder
With strStringBuilder
.Append("SELECT b.*, ROWNUM ")
.Append("FROM (SELECT a.*, func_get_unit_name (unit_name) plan_unit_description, ROWNUM r__ ")
.Append("FROM (SELECT (SUBSTR (item, 1, 4) || '.' || SUBSTR (item, 5) ) item_number, ")
.Append(" idescr short_description, idescrl long_description, ")
.Append(" iunits unit_name, ispecyr spec_year, iobselet, item ")
.Append("FROM(itemlist) ")
.Append("WHERE item <> '2999509/00001' ")
.Append(" AND iobselet = 'N' ")
.Append(" AND ispecyr = '05' ")
.Append(" ORDER BY item) a ")
.Append(" WHERE ROWNUM < (:rownumberA)) b ")
.Append(" WHERE r__ >= :rownumberB ")
End With
'CREATE A NEW COMMAND AND PASS THE SQL STATEMENT / CONNECTION OBJECT
Dim cmdItemDetail As OracleCommand = New OracleCommand()
cmdItemDetail.Parameters.Add(":rownumberA", OracleType.Int32).Value = UpperRowNumber
cmdItemDetail.Parameters.Add(":rownumberB", OracleType.Int32).Value = LowerRowNumber
cmdItemDetail.Connection = oOracleConn
cmdItemDetail.CommandType = CommandType.Text
cmdItemDetail.CommandText = strStringBuilder.ToString
' Dim adItemDetail As New OleDbDataAdapter(cmdItemDetail)
Dim adItemDetail As New OracleDataAdapter(cmdItemDetail)
Dim dsItemDetail As New DataSet
'Fill the dataset with the result of our query from the specified command
adItemDetail.Fill(dsItemDetail, "ItemDetails")
'Bind the DataSet to the GridView
gvItem.DataSource = dsItemDetail
gvItem.DataBind()
End Sub
<%@ Page Language="VB" MaintainScrollPositionOnPostback="true" AutoEventWireup="false" EnableViewState="false" CodeFile="custPaging.aspx.vb" Inherits="ItemPerLine" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title> projects</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
Height="18px" Width="101px">
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
</asp:DropDownList>
<asp:Label ID="lblView" runat="server" ></asp:Label>
<asp:GridView ID="gvItem"
runat="server"
CellPadding="4"
ForeColor="#333333"
Width="99%"
HorizontalAlign="Left"
AutoGenerateColumns="False"
AllowPaging="True"
PageSize="20"
OnPageIndexChanging ="gvItem_PageIndexChanging"
AllowSorting="True"
BackColor="PapayaWhip" Font-Size="Small"
Font-Bold="False" style="border-top-style: groove; border-right-style: groove; border-left-style: groove; border-bottom-style: groove" >
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerSettings Position="TopAndBottom" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" Font-Size="Small" HorizontalAlign="Left" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" Font-Size="Small" HorizontalAlign="Left" />
<PagerStyle BackColor="#FFE0C0" ForeColor="Black" HorizontalAlign="Right" Font-Size="X-Large" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" Font-Size="Smaller" HorizontalAlign="Left" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField DataField="Item_Number" HeaderText="Line Number" >
<HeaderStyle Font-Size="Small" />
<ItemStyle Font-Size="Smaller" />
</asp:BoundField>
<asp:BoundField DataField="Short_Description" HeaderText="Item Number" >
<HeaderStyle Font-Size="Small" />
<ItemStyle Font-Size="Smaller" />
</asp:BoundField>
<asp:BoundField DataField="Long_Description" HeaderText="Item Description" >
<HeaderStyle Font-Size="Small" />
<ItemStyle Font-Size="Smaller" />
</asp:BoundField>
<asp:BoundField DataField="Unit_Name" HeaderText="Unit_Name" >
<HeaderStyle Font-Size="Small" />
<ItemStyle Font-Size="Smaller" />
</asp:BoundField>
</Columns>
</asp:GridView>
</form>
</body>
</html>