• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

Paging GridView

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
russell12
Asked:
russell12
  • 4
  • 3
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
russell12Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
russell12Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
russell12Author Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Glad to help you Russell. Great !

Bye
Pawan
1

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now