Solved

ASP.Net GridView with ObjectDataSource Paging not working properly

Posted on 2010-11-25
3
703 Views
Last Modified: 2012-05-10
Dear Friends,

Thanks for all the assistance given till date and really appreciate a lot for the same.
Last 2 days I have been trying to master the GridView with ObjectDatasource and posted a few issues in this regard. Still a few issue remains and need all your generous help to sort the same.

The GridView with ObjectDatasource I am trying to learn has got a paging issue and I dont know how to sort this out. The first page when loaded looks perfect. The paging problem starts with the subsequent pages.
There are about 34 records in table and pagesize is set to 8. But when I click on the page link 2 it shows the last record.

The complete code with .aspx, .aspx.cs, BL, DL , SP, table structure and test data is uploaded. Please help to find a solution.
Thank you.

CodeDetails.txt
0
Comment
Question by:MadhuMenong
  • 2
3 Comments
 
LVL 14

Expert Comment

by:Dhanasekaran Sengodan
ID: 34215783
0
 

Accepted Solution

by:
MadhuMenong earned 0 total points
ID: 34221446
Dear All,

Found the solution to my promble with was in Stored Procedure "SupplierSearch".

The changed version of the stored procedure is given below:

The error was in the below line which I commented.

--IF @StartRowIndex > 0
--      SET @StartRowIndex =  (@StartRowIndex - 1)  * @MaximumRows




Create PROCEDURE [dbo].[SupplierSearch]
(
        @SupplierName           VARCHAR(50) = NULL, -- default to NULL
        @SupplierCity           INT = NULL,
        @SupplierCountry        INT = NULL,
        @SupplierEmail          VARCHAR(50) = NULL,
        @StartRowIndex                  INT,
        @MaximumRows                  INT,
        @TotalRows                        INT OUTPUT
)
AS
BEGIN
      
DECLARE @first_id int, @startRow INT
DECLARE @StringCity VARCHAR(20)
DECLARE @StringCountry VARCHAR(20)

--IF @StartRowIndex > 0
--      SET @StartRowIndex =  (@StartRowIndex - 1)  * @MaximumRows

IF @StartRowIndex = 0
      SET @StartRowIndex = 1
ELSE
      SET @StartRowIndex = @StartRowIndex + 1



IF RTRIM(@SupplierName)='' OR @SupplierName IS NULL
      SET @SupplierName = '%'
ELSE
      SET @SupplierName = @SupplierName + '%'
      
IF RTRIM(@SupplierEmail)='' OR @SupplierEmail IS NULL
      SET @SupplierEmail = '%'
ELSE
      SET @SupplierEmail = @SupplierEmail + '%'
      
IF RTRIM(@SupplierCity)='' OR @SupplierCity = 0 OR @SupplierCity IS NULL
      SET @StringCity = '%'
ELSE
      SET @StringCity = CONVERT(VARCHAR(20),@SupplierCity)
            
IF RTRIM(@SupplierCountry)='' OR @SupplierCountry = 0 OR @SupplierCountry IS NULL
      SET @StringCountry = '%'
ELSE
      SET @StringCountry = CONVERT(VARCHAR(20),@SupplierCountry)
      

 
       
        SET ROWCOUNT @StartRowIndex
       
        SELECT @first_id = SupplierID FROM Supplier Where
                        (@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
                --(SupplierCity = IsNull(@SupplierCity,SupplierCity)) AND
                --(SupplierCountry = IsNull(@SupplierCountry,SupplierCountry)) AND
               
                 (SupplierCity like @StringCity) AND
                (SupplierCountry like @StringCountry) AND
               
                (@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail)  
        ORDER BY SupplierID
               
       --PRINT Convert(varchar(30),@first_id) + ' - First ID'
               
            SET ROWCOUNT @MaximumRows
            
        SELECT s.SupplierID, s.SupplierName,ci.CityName AS SupplierCity, co.CountryName AS SupplierCountry,
        s.SupplierEmail,s.SupplierTelephone,s.SupplierAddress1 + ' ' + s.SupplierAddress2 as Address
          FROM Supplier s, City ci, Country co WHERE
                        s.SupplierCity = ci.CityID AND s.SupplierCountry = co.CountryID And
                (@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
--                (SupplierCity = IsNull(@SupplierCity,SupplierCity)) AND
--                (SupplierCountry = IsNull(@SupplierCountry,SupplierCountry)) AND

                         (SupplierCity like @StringCity) AND
                (SupplierCountry like @StringCountry) AND
                (@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail) AND
                (SupplierID >= @first_id) ORDER BY SupplierID
       
        SET ROWCOUNT 0
       
       
        SELECT @TotalRows = COUNT(*) FROM Supplier
          WHERE
                (@SupplierName IS NULL OR [SupplierName] LIKE @SupplierName) AND -- If passed only applies to filter
--                (SupplierCity = IsNull(@SupplierCity,SupplierCity)) AND
--                (SupplierCountry = IsNull(@SupplierCountry,SupplierCountry)) AND

                        (SupplierCity like @StringCity) AND
                (SupplierCountry like @StringCountry) AND
                (@SupplierEmail IS NULL OR SupplierEmail LIKE @SupplierEmail)
         
       -- PRINT Convert(varchar(30),@TotalRows) + ' - Total Rows'    
       
END

0
 

Author Closing Comment

by:MadhuMenong
ID: 34255627
Bcouz I located the problem and solved it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Connecting database in Xamarin? 6 50
C# Offline Apllication 5 54
Server Error 11 47
Importing CSV file encoded in UTF-8 in SQL SERVER 2008 using BULK INSERT 5 27
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

912 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

25 Experts available now in Live!

Get 1:1 Help Now