Solved

Paging GridView

Posted on 2016-11-08
7
32 Views
Last Modified: 2016-11-10
I am trying to get paging to work.  I have alot of records that are going to load for certain users, and I have found that this is a very timely process.  So the solution was to use sql server stored procedures.  I have got the records loading successfully, but I can not put a where clause in the records that come back.  I have tried puting the where clause in every select but it always returns pages for all records in the db and it shows no data.  I am generatnig the Where from a script running in the code. That script runs fine, when I put the Whereclause in a textbox, it is correct. Any help would be greatly appreciated.
Russell
Asp.net Vb.net code behind:
        WhereClause = "'3268'"
       
        Dim dt As New DataTable()
        Dim con As SqlConnection = New SqlConnection("Con String")
        Dim cmd As SqlCommand = New SqlCommand("MainPageGridView", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
        cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
        cmd.Parameters.AddWithValue("@WhereClause", WhereClause)
        TextBox1.Text = WhereClause
        cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
        Dim sda As New SqlDataAdapter()
        ' Try
        con.Open()
        sda.SelectCommand = cmd
        sda.Fill(dt)
        GridView1.DataSource = dt
        GridView1.DataBind()
        Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)

Open in new window


    Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
        Dim dblPageCount As Double = 10 'CType((CType(recordCount, Decimal) / Decimal.Parse(ddlPageSize.SelectedValue)), Double)
        Dim pageCount As Integer = CType(Math.Ceiling(dblPageCount), Integer)
        Dim pages As New List(Of ListItem)
        If (pageCount > 0) Then
            pages.Add(New ListItem("First", "1", (currentPage > 1)))
            Dim i As Integer = 1
            Do While (i <= pageCount)
                pages.Add(New ListItem(i.ToString, i.ToString, (i <> currentPage)))
                i = (i + 1)
            Loop
            pages.Add(New ListItem("Last", pageCount.ToString, (currentPage < pageCount)))
        End If
        rptPager.DataSource = pages
        rptPager.DataBind()
    End Sub

Open in new window


Protected Sub PageSize_Changed(ByVal sender As Object, ByVal e As EventArgs)
        BindData(1)
    End Sub

Open in new window


 Protected Sub Page_Changed(ByVal sender As Object, ByVal e As EventArgs)
        Dim pageIndex As Integer = Integer.Parse(CType(sender, LinkButton).CommandArgument)
        BindData(pageIndex)
    End Sub

Open in new window


SQL Stored Procedure:
USE [UNAME]
GO
/****** Object:  StoredProcedure [dbo].[MainPageGridView]    Script Date: 11/8/2016 6:58:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[MainPageGridView]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
	  ,@WhereClause Nvarchar(MAX)
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [Item] ASC
      )AS RowNumber
	  ,[Item]
      ,[DateAdded]
      ,[StoreNumber]
      ,[Prio]
	  ,[AreaOfStore]
	  ,[EquipmentName]
	  ,[DetailedDescription]
	  ,[DateCompleted]
     INTO #Results
      FROM [tblFILMainForm]
	  
 
      SELECT @RecordCount = COUNT(*)
      FROM #Results

 

      SELECT Item, DateAdded, StoreNumber, Prio, AreaOfStore, EquipmentName, DetailedDescription FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1 
      DROP TABLE #Results
END

Open in new window

0
Comment
Question by:russell12
  • 4
  • 3
7 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41880057
Try this

USE [UNAME]
GO
/****** Object:  StoredProcedure [dbo].[MainPageGridView]    Script Date: 11/8/2016 6:58:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[MainPageGridView]
       @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
	  ,@WhereClause Nvarchar(MAX)
AS
BEGIN
      SET NOCOUNT ON;

	  DECLARE @MinPageRank INT,
        @MaxPageRank INT
		SET @MinPageRank = (@PageIndex - 1) * @PageSize + 1
		SET @MaxPageRank = @PageIndex * @PageSize 

	SELECT * FROM 
	(
		  SELECT ROW_NUMBER() OVER
		  (
				ORDER BY [Item] ASC
		  )AS rnk
		  ,[Item]
		  ,[DateAdded]
		  ,[StoreNumber]
		  ,[Prio]
		  ,[AreaOfStore]
		  ,[EquipmentName]
		  ,[DetailedDescription]
		  ,[DateCompleted]    
		  FROM [tblFILMainForm]
	)k
	WHERE rnk BETWEEN @MinPageRank AND @MaxPageRank

	SELECT @RecordCount = COUNT(1) FROM [tblFILMainForm]	
    
END

Open in new window

0
 
LVL 2

Author Comment

by:russell12
ID: 41881542
Where would I put my @Whereclause.  This comes from the site depending on which store # you selected.  Example sql string would be:

  "Select Item, DateAdded, StoreNumber, Prio, AreaOfStore, EquipmentName, DetailedDescription FROM tblFILMainForm WHERE (StoreNumber = "'3268'") AND DateCompleted Is Null"

Open in new window


This is the query I was running before realizing that 100 records + trying to load in a gridview was lagging the website.  Thats when I went to custom paging in gridview.
Thank you very much for your response,
Russell
0
 
LVL 17

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
ID: 41881560
Added where clause.. added @StoreNumber - a new parameter.

USE [UNAME]
GO
/****** Object:  StoredProcedure [dbo].[MainPageGridView]    Script Date: 11/8/2016 6:58:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[MainPageGridView]
       @StoreNumber INT = 0
      ,@PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
      ,@WhereClause Nvarchar(MAX)
AS
BEGIN
      SET NOCOUNT ON;

	  DECLARE @MinPageRank INT,
        @MaxPageRank INT
		SET @MinPageRank = (@PageIndex - 1) * @PageSize + 1
		SET @MaxPageRank = @PageIndex * @PageSize 

	SELECT * FROM 
	(
		  SELECT ROW_NUMBER() OVER
		  (
				ORDER BY [Item] ASC
		  )AS rnk
		  ,[Item]
		  ,[DateAdded]
		  ,[StoreNumber]
		  ,[Prio]
		  ,[AreaOfStore]
		  ,[EquipmentName]
		  ,[DetailedDescription]
		  ,[DateCompleted]    
		  FROM [tblFILMainForm]
		  WHERE (StoreNumber = @StoreNumber) AND DateCompleted Is Null
	)k
	WHERE rnk BETWEEN @MinPageRank AND @MaxPageRank


	SELECT @RecordCount = COUNT(1) FROM [tblFILMainForm]	
    
END

Open in new window


Hope it helps !!
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 2

Author Comment

by:russell12
ID: 41881576
Your sql code does work well when I put the store numbers in manually.  But when I try to use the @WhereClause it shows no records.  What I have gathered is for some reason the @WhereClause is not being passed.  I have checked on server side if the variable I am sending to @WhereClause is populating, and it is on the server side.  I tested this by adding a textbox to the form and populated the dimmed variable for @WhereClause.  If I copy and paste it directly into the stored procedure, it shows the data.  Below is the SQL Stored Procedure
GO
/****** Object:  StoredProcedure [dbo].[MainPageGridView]    Script Date: 11/8/2016 6:58:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
Alter PROCEDURE [dbo].[MainPageGridView1]
       @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
	  ,@WhereClause Nvarchar(MAX)
AS
BEGIN
      SET NOCOUNT ON;

	  DECLARE @MinPageRank INT,
        @MaxPageRank INT
		SET @MinPageRank = (@PageIndex - 1) * @PageSize + 1
		SET @MaxPageRank = @PageIndex * @PageSize 

	SELECT * FROM 
	(
		  SELECT ROW_NUMBER() OVER
		  (
				ORDER BY [Item] ASC
		  )AS rnk
		  ,[Item]
		  ,[DateAdded]
		  ,[StoreNumber]
		  ,[Prio]
		  ,[AreaOfStore]
		  ,[EquipmentName]
		  ,[DetailedDescription]
		  ,[DateCompleted]    
		  FROM [tblFILMainForm]
		  WHERE (StoreNumber = @WhereClause) AND DateCompleted Is Null

	)k
	WHERE rnk BETWEEN @MinPageRank AND @MaxPageRank


	SELECT @RecordCount = COUNT(1) FROM [tblFILMainForm] WHERE (StoreNumber = @WhereClause) AND DateCompleted Is Null

    
END

Open in new window


Below is the code that gets the @WhereClause
    

        Select Case (cmbStoreNumber.Text)
                
            Case = "Assigned Stores"
                WhereClause = "9999"

                Dim connection As SqlConnection = New SqlConnection("ConnString")
                Dim command As SqlCommand = New SqlCommand("SELECT AssignedTo FROM tblSupStoreAssignment WHERE UserName = '" + Session("UserName") + "' ORDER BY AssignedTo ASC", connection)

                Try
                    connection.Open()

                    Dim reader As SqlDataReader = command.ExecuteReader()

                    If reader.HasRows Then
                        Do While reader.Read()

                            If reader.IsDBNull(0) Then
                                WhereClause = "9999"
                            Else
                                If WhereClause = "9999" Then
                                    WhereClause = "'" & reader.GetString(0) & "'"
                                Else
                                    WhereClause = WhereClause & " OR StoreNumber = '" & reader.GetString(0) & "'"
                                End If

                            End If

                        Loop
                    Else
                        WhereClause = "'" & StoreNumber & "'"
                    End If
                Catch ex As Exception
                    connection.Close()
                    connection.Dispose()
                    Exit Function
                Finally
                    connection.Close()
                    connection.Dispose()
                End Try
                
            Case Else

               WhereClause = cmbStoreNumber.Text
        End Select

        Dim dt As New DataTable()
        Dim con As SqlConnection = New SqlConnection("ConnString")
        Dim cmd As SqlCommand = New SqlCommand("MainPageGridView1", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
        cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
        cmd.Parameters.Add("@WhereClause", Data.SqlDbType.NVarChar).Value = WhereClause
        TextBox1.Text = WhereClause
        cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output

        Dim sda As New SqlDataAdapter()
        ' Try
        con.Open()
        sda.SelectCommand = cmd
        sda.Fill(dt)
        GridView1.DataSource = dt
       GridView1.DataBind()
        Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
           Me.PopulatePager(recordCount, pageIndex)

Open in new window


I'm not sure if I need to open another thread on this, it is pertaining to the subject, but if you prefer me to open a new thread, I will.
Thank you for your help!!
Russell
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41881595
Ok, try this should help, Modify to handle strings.

GO
/****** Object:  StoredProcedure [dbo].[MainPageGridView]    Script Date: 11/8/2016 6:58:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
Alter PROCEDURE [dbo].[MainPageGridView1]
       @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
	  ,@WhereClause Nvarchar(MAX)
AS
BEGIN
      SET NOCOUNT ON;

	  DECLARE @MinPageRank INT,
        @MaxPageRank INT
		SET @MinPageRank = (@PageIndex - 1) * @PageSize + 1
		SET @MaxPageRank = @PageIndex * @PageSize 

    DECLARE @SQL AS VARCHAR(MAX) = ''

	SET @SQL = '

	SELECT * INTO #Pawan FROM 
	(
			SELECT ROW_NUMBER() OVER
			(
				ORDER BY [Item] ASC
			)AS rnk
			,[Item]
			,[DateAdded]
			,[StoreNumber]
			,[Prio]
			,[AreaOfStore]
			,[EquipmentName]
			,[DetailedDescription]
			,[DateCompleted]    
			FROM [tblFILMainForm]
			WHERE (StoreNumber = ' + '''' + @WhereClause + '''' + ' ) AND DateCompleted Is Null 

	)k
	WHERE rnk BETWEEN ' + CAST(@MinPageRank AS VARCHAR(10)) + ' AND ' +  CAST(@MaxPageRank AS VARCHAR(10))

	EXEC(@SQL)

	SELECT * FROM #Pawan

	SELECT @RecordCount = COUNT(1) FROM [tblFILMainForm] WHERE (StoreNumber = @WhereClause) AND DateCompleted Is Null

    
END

Open in new window

0
 
LVL 2

Author Closing Comment

by:russell12
ID: 41883143
I ended up using the code that I accepted as an answer.  I have learned a lot on this little issue.  The problem why I was not getting the results needed was 2 things.  I have always passed values that were text with ' ' at the beginning and end.  Also if there were multiple values being sent to the where clause, I also add OR ColumnName = 'text'.  Instead with a stored procedure you send the clause without ' ' and with a comma instead of OR.  I can not thank you enough for your continued help!
Russell
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41883264
Glad to help you Russell. Great !

Bye
Pawan
1

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now