hi,
i am trying to paging datalist. i am following Scottgus's method.in this method i taken .xsd to bind the data to datasource .To get solution i searched a lot in google.
Here is my code :
// method for ObjectDataSource
Protected Sub ProductDataSource_Selected(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
Dim productCount As Integer = e.OutputParameters("CategoryProductCount") '.toInt32()
Dim categoryName As String = e.OutputParameters("CategoryName") '.ToString()
Dim pageIndex As Integer = Convert.ToInt32(Request.QueryString("pageIndex"))
Dim CategoryId As Integer = Convert.ToInt32(Request.QueryString("CategoryId"))
Dim pageSize As Integer = Int32.Parse(ProductDataSource.SelectParameters("Nu mRows").DefaultValue)
UpdateTitles(categoryName)
UpdatePagerLocation(pageIndex, pageSize, productCount)
UpdateNextPrevLinks(CategoryId, pageIndex, pageSize, productCount)
End Sub
Public Sub UpdatePagerLocation(ByVal pageIndex As Integer, ByVal pageSize As Integer, ByVal productCount As Integer)
Dim currentStartRow As Integer = (pageIndex * pageSize) + 1
Dim currentEndRow = (pageIndex * pageSize) + pageSize
If (currentEndRow < productCount) Then
currentEndRow = productCount
PagerLocation.Text = currentStartRow & "-" & currentEndRow & " of " & productCount & " products"
End If
End Sub
Public Sub UpdateNextPrevLinks(ByVal categoryId As Integer, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByVal productCount As Integer)
Dim navigationFormat As String = "productspage.aspx?dealsId={0}&pageIndex={1}"
PreviousPageNav.HRef = String.Format(navigationFormat, categoryId, pageIndex - 1)
PreviousPageNav.Visible = IIf((pageIndex > 0), True, False)
NextPageNav.HRef = String.Format(navigationFormat, categoryId, pageIndex + 1)
NextPageNav.Visible = IIf(((pageIndex + 1) * pageSize < productCount), True, False)
End Sub
Public Sub UpdateTitles(ByVal title As String)
productHeader.Text = title
Page.Title = "products:" + title
End Sub
sql procedure is :
CREATE procedure GetProductsByCategoryId
@CategoryId varchar(250),
@pageIndex int,
@NumRows int,
@CategoryName nvarchar(50) output,
@categoryProductCount int output
as
begin
set @categoryProductCount=(select count(*) from productDeals where productDeals.productCategory=@CategoryId)
set @CategoryName=(select category from pro_category where category=@CategoryId)
declare @startRowIndex int;
set @startRowIndex =(@pageIndex * @NumRows) + 1;
with productEntries as (
select ROW_NUMBER() over (order by dealsId asc) as Row,count(*) over() as ResultRowCount,dealsId,productName,validityFrom,va lidityTo,product,dealPrice from productDeals
where productCategory=@CategoryName)
select * from
productEntries where Row between @startRowIndex and @startRowIndex+@NumRows-1
End
//.aspx page
<div>
<asp:Label ID="productHeader" runat="server" />
<asp:DataList ID="ProductDataList" DataSourceID="ProductDataSource" RepeatColumns="2" RepeatDirection="Horizontal" Runat="server" DataKeyField="dealsId" >
<ItemTemplate>
productName:
<asp:Label ID="productNameLabel" runat="server" Text='<%# Eval("productName") %>'>
</asp:Label><br />
dealPrice:
<asp:Label ID="dealPriceLabel" runat="server" Text='<%# Eval("dealPrice") %>'></asp:Label><br />
validityFrom:
<asp:Label ID="validityFromLabel" runat="server" Text='<%# Eval("validityFrom") %>'>
</asp:Label><br />
validityTo:
<asp:Label ID="validityToLabel" runat="server" Text='<%# Eval("validityTo") %>'>
</asp:Label><br /><br />
</ItemTemplate>
</asp:DataList>
<asp:ObjectDataSource ID="ProductDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetProducts" TypeName="MyDataLayerTableAdapters.productsTableAd apter" OnSelected="ProductDataSource_Selected" UpdateMethod="GetProducts" >
<SelectParameters>
<asp:QueryStringParameter DefaultValue="Mobiles" Name="CategoryId" QueryStringField="categoryId"
Type="String" />
<asp:Parameter Name="pageIndex" Type="Int32" DefaultValue="0" />
<asp:Parameter Name="NumRows" Type="Int32" DefaultValue="2" />
<asp:Parameter Direction="InputOutput" Name="CategoryName" Type="String" DefaultValue="empty" />
<asp:Parameter Direction="InputOutput" Name="categoryProductCount" Type="Object" DefaultValue="0" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="CategoryId" Type="String" />
<asp:Parameter Name="pageIndex" Type="Int32" />
<asp:Parameter Name="NumRows" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="CategoryName" Type="String" />
<asp:Parameter Direction="InputOutput" Name="categoryProductCount" Type="Object" />
</UpdateParameters>
</asp:ObjectDataSource>
<div id="Navigation" class="navigation" runat="server" >
<div id="leftnav">
<a id="PreviousPageNav" runat="server" > <<</a> <a id="NextPageNav" runat="server">>></a>
<asp:Label ID="PagerLocation" runat="server" />
</div>
<div id="numnav">
</div>
</div>
</div>
in my table i have 9 rows. i want to display two items per page so that i given pageindex as 0 and pagesize as 2.when i run products.aspx page its displaying only first two rows only.the pageidex is getting increment but page is not displaying after second record. what i do. if any one can suggest me that how can i get 3rd to 9th record .
thanks in advance.
paging datalist
