Hi,I urgently need help with this. I am trying to implement custom paging with Sql 2000. However I have ran into a few problems.
I really hope that someone would be able to help me out. I need to get this done by tomorrow!! (I know I screwed up big time, but I didn;t relize that built in paging fetches all the records, and the displays it page by page!!)
1) Currently the paging seems to be working. However the primary key always resets itself. For example, lets say I have 4 records ID 1,2,3,4
When I delete ID 3, instead of displaying ID 1,2,4 it shows ID 1,2,3. Is there any way to solve this problem?
2) The database that I am currently creating will have about 40,000 records, is it advisible for me to use your control (I hope so)?
Thank you and do have a nice day.
http://www.codeproject.com/aspnet/ASPNETPagerControl.asp
CREATE PROCEDURE [dbo].[GetPagedProducts11](@.PageSizeint,@.CurrentPageint,@.ItemCountint output)ASDeclare @.UpperBandint, @.LowerBandint-- Get The Count Of The Rows That They Meet the CriteriaSET @.ItemCount = (SELECTCOUNT(*)FROM aduan)-- Calculate the @.LowerCount and @.UpperCountSET @.LowerBand = (@.CurrentPage - 1) * @.PageSizeSET @.UpperBand = (@.CurrentPage * @.PageSize) + 1-- create a temporaty tableCREATE TABLE #AllRows(ad_idint PRIMARY KEY IDENTITY(1, 1),ad_namavarchar(40),ad_tarikhdatetime , ad_statusvarchar(30) ,ad_titlevarchar(100) )-- INSERT ALL THE Rows that meets the CriteriaINSERT INTO #AllRowsSELECT ad_nama,ad_tarikh, ad_status,ad_titleFROM aduan-- AND finally select and return desired -Paged- RowsSELECT ad_id, ad_nama, ad_tarikh, ad_status,ad_titleFROM #AllRowsWHERE ad_id > @.LowerBandAND ad_id < @.UpperBandRETURN
In the table aduan is ad_id a column name ?
If it is then replace
CREATE TABLE #AllRows(
ad_idint PRIMARY KEY IDENTITY(1, 1),
ad_namavarchar(40),ad_tarikhdatetime , ad_statusvarchar(30) ,ad_titlevarchar(100)
)
with
CREATE TABLE #AllRows(
ad_idint PRIMARY KEY,
ad_namavarchar(40),ad_tarikhdatetime , ad_statusvarchar(30) ,ad_titlevarchar(100)
)
-- INSERT ALL THE Rows that meets the Criteria
INSERT INTO #AllRows
SELECT ad_nama,ad_tarikh, ad_status,ad_title
FROM aduan
with
INSERT INTO #AllRows
SELECT ad_id ,ad_nama,ad_tarikh, ad_status,ad_title
FROM aduan
The problem you are having is because the Identity column is generated in the temporary table everytime you create it fresh.
This should solve your problem
|||
Hiambarishg,
thanks for your help. I really appreciate it :)
I have tried changing it to your code but it seems that no data is being passed back.
BTW ad_id is the primary key for the aduan table. any
|||Does
@.PageSizeint,@.CurrentPageint parameters have proper value when you are calling the SP?
