Custom Paging on Stored Procedure

Hello,

I receive this error "Incorrect syntax near 'GetGalleryPaged'." I'm trying to use custom paging on a stored procedure.

......
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int, 1)
objDA.SelectCommand.Parameters.Add("@.@.maximumRows", SqlDbType.Int, 9)
Dim objDS As New DataSet()
Dim objPds As PagedDataSource = New PagedDataSource
objDA.Fill(objDS, "Gallery") <<--error here
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True

......

ALTER PROCEDURE dbo.GetGalleryPaged
(
@.startRowIndex int,
@.maximumRows int
)
AS
SELECT idgallery, g_picpath
FROM
( SELECT idgallery, g_picpath, ROW_NUMBER() OVER (ORDER BY idgallery DESC) AS RowRank
FROM Gallery
) AS GalleryWithRowNumber
WHERE RowRank > @.startRowIndex AND RowRank <= (@.startRowIndex + @.maximumRows)
ORDER BY idgallery DESC

cheers,

imperialx

First problem is here

@.@.maximumRows in following line
objDA.SelectCommand.Parameters.Add("@.@.maximumRows", SqlDbType.Int, 9)

and second is you are adding parameter but not setting their value, try this

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = 1

objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

|||

Hi Vish4forum,

I still got the same error.

...
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = 1

objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

Dim objDS As New DataSet()
Dim objPds As PagedDataSource = New PagedDataSource
objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
...

|||

Hi Vish4forum,

I got it, I add this line

...
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
objDA.SelectCommand.CommandType = CommandType.StoredProcedure

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = 1

objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

Dim objDS As New DataSet()
Dim objPds As PagedDataSource = New PagedDataSource
objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
...

One thing is, have I set the stored procedure for a custom paging right?

|||

Hi Guys,

If I use the stored procedure that uses custom paging, the paging link controls doesn't work as intended (visible property is always set to false)

ALTER PROCEDURE dbo.GetGalleryPaged
(
@.startRowIndex int,
@.maximumRows int
)
AS
SELECT idgallery, g_picpath
FROM
( SELECT idgallery, g_picpath, ROW_NUMBER() OVER (ORDER BY idgallery DESC) AS RowRank
FROM Gallery
) AS GalleryWithRowNumber
WHERE RowRank > @.startRowIndex AND RowRank <= (@.startRowIndex + @.maximumRows)
ORDER BY idgallery DESC

....

Imports DataSet1TableAdapters
Imports System.Data.SqlClient
Imports System.Data

Partial Class gallery
Inherits System.Web.UI.Page
Dim TotalRowCount As Integer
Dim isLastPage As Boolean

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

Protected Sub DataGalleryList()
Dim cs As ClientScriptManager = Page.ClientScript 'just to initialize Ajax ScriptManager
Dim ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings("smsdataConnectionString1").ConnectionString
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objPds As PagedDataSource = New PagedDataSource
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
objDA.SelectCommand.CommandType = CommandType.StoredProcedure

If isLastPage Then
CurrentPage = objPds.PageCount - 1
objPds.CurrentPageIndex = objPds.PageCount - 1
Else
objPds.CurrentPageIndex = CurrentPage
End If

objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = objPds.CurrentPageIndex
objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = 9

Dim objDS As New DataSet()
objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
''''''objPds.PageSize = 9

Dim gallery As New DataSet1TableAdapters.galleryTableAdapter
Dim galleryGetPaged As DataSet1.galleryDataTable = gallery.GetDataGallery
TotalRowCount = gallery.TotalNumGallery

lblTotalImage.Text = ("Total Image: " + TotalRowCount.ToString)
lblTotalImage_dview.Text = ("Total Image: " + TotalRowCount.ToString)
lblCurrentPage.Text = ("Page: " + ((CurrentPage + 1).ToString + (" of " + objPds.PageCount.ToString)))

PrevPage.Visible = Not objPds.IsFirstPage
NextPage.Visible = Not objPds.IsLastPage
FirstPage.Visible = Not objPds.IsFirstPage
LastPage.Visible = Not objPds.IsLastPage

DataList1.DataSource = objPds
DataList1.DataBind()

ViewState("currentItemIndex") = currentItemIndex
End Sub

Protected Sub FirstPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles FirstPage.Click
CurrentPage = 0
DataGalleryList()
End Sub
Protected Sub PrevPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles PrevPage.Click
CurrentPage -= 1
DataGalleryList()
End Sub
Protected Sub NextPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles NextPage.Click
CurrentPage += 1
DataGalleryList()
End Sub
Protected Sub LastPage_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles LastPage.Click
isLastPage = True
DataGalleryList()
End Sub
Public Property CurrentPage() As Integer
Get
Dim o As Object = ViewState("_CurrentPage")
If (o = Nothing) Then
Return 0
End If
Return CType(o, Integer)
End Get
Set(ByVal value As Integer)
ViewState("_CurrentPage") = value
End Set
End Property

...

<DIV class="totalimage">
<asp:Label id="lblTotalImage" runat="server" CssClass="tot_img"></asp:Label>
<asp:Label id="lblCurrentPage" runat="server" CssClass="crntpnum" Text="Label"></asp:Label>
</DIV>
<BR /><BR />
<DIV style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; FLOAT: left; PADDING-BOTTOM: 0px; MARGIN: 0px 0px 0px 25px; WIDTH: 500px; PADDING-TOP: 0px; TEXT-ALIGN: center">
<asp:DataList id="DataList1" runat="server" Width="480px" HorizontalAlign="Center" RepeatDirection="Horizontal" RepeatColumns="3" DataKeyField="idGallery">
<ItemTemplate>
<asp:ImageButton CommandName="Select" CssClass="g_thumbs" ImageUrl='<%#Eval("g_picpath") %>' ID="ImageButton2" runat="server" />
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Wrap="True" />
</asp:DataList>
</DIV>
<DIV style="WIDTH: 347px" class="g_paginglink">
<UL>
<LI><asp:LinkButton id="FirstPage" runat="server">First</asp:LinkButton></LI>
<LI><asp:LinkButton id="PrevPage" runat="server">Previous</asp:LinkButton></LI>
<LI><asp:LinkButton id="NextPage" runat="server">Next</asp:LinkButton></LI>
<LI><asp:LinkButton id="LastPage" runat="server">Last</asp:LinkButton></LI>
</UL>
</DIV>

|||

Hello,

I got it now.

The Stored Procedure parameter values must be called first before the PagedDataSource values.

Protected Sub DataGalleryList()
Dim cs As ClientScriptManager = Page.ClientScript 'just to initialize Ajax ScriptManager
Dim ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings("smsdataConnectionString1").ConnectionString
Dim mySqlConn As New SqlConnection(ConnStr)
Dim objPds As PagedDataSource = New PagedDataSource
Dim objDA As New SqlDataAdapter("GetGalleryPaged", mySqlConn)
Dim objDS As New DataSet()
Dim gallery As New DataSet1TableAdapters.galleryTableAdapter
Dim galleryGetPaged As DataSet1.galleryDataTable = gallery.GetDataGallery
TotalRowCount = gallery.TotalNumGallery

objDA.SelectCommand.CommandType = CommandType.StoredProcedure
objDA.SelectCommand.Parameters.Add("@.startRowIndex", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.startRowIndex").Value = objPds.CurrentPageIndex
objDA.SelectCommand.Parameters.Add("@.maximumRows", SqlDbType.Int)
objDA.SelectCommand.Parameters("@.maximumRows").Value = TotalRowCount

objDA.Fill(objDS, "Gallery")
mySqlConn.Close()

objPds.DataSource = objDS.Tables(0).DefaultView
objPds.AllowPaging = True
objPds.PageSize = 9

If isLastPage Then
CurrentPage = objPds.PageCount - 1
objPds.CurrentPageIndex = CurrentPage
Else
objPds.CurrentPageIndex = CurrentPage
End If

currentItemIndex = objPds.FirstIndexInPage 'indexing datailview control

lblTotalImage.Text = ("Total Image: " + TotalRowCount.ToString)
lblTotalImage_dview.Text = ("Total Image: " + TotalRowCount.ToString)
lblCurrentPage.Text = ("Page: " + ((CurrentPage + 1).ToString + (" of " + objPds.PageCount.ToString)))

PrevPage.Visible = Not objPds.IsFirstPage
NextPage.Visible = Not objPds.IsLastPage
FirstPage.Visible = Not objPds.IsFirstPage
LastPage.Visible = Not objPds.IsLastPage

DataList1.DataSource = objPds
DataList1.DataBind()

ViewState("currentItemIndex") = currentItemIndex
End Sub