Custom Paging in asp.net Datgrid Using Sql

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

All you devgeeks on .net,
Can you please help me on this.

I have a table ( tbl_emp).I allow users to search the record on this
table.
I have three filter conditions
Fname,Lname,Title.When user enters these values into Textboxes on
Asp.net page and click on search Button.
I Show the results of search on an ASP.NET datagrid.

Now my Database is huge ,it returns about 4 million records after
search,I can't use normal Paging because it gives me a Time out error.
I decided to Use Custom Paging.Here is my Logic
I have written A SP in SQL which will be used to Bind the grid.
This Sp_fillGrid has following Parameters.
1.Fname 2.LName 3.Title 4.StartIndex 5.endindex

Now When the first time grid is loaded after clicking on search button,
Sp_Fill builds a querry like
"Select Top [Pagesize of the grid] col 1,2....n from tbl_emp where
(Filter conditions) order by ID"

I pass Pagesize from The UI code to SQl SP.

I have 2 buttons : prev(<) and next(>) on grid.
When User clicks on Nextbutton,Sp_fill builds a querry like
"Select Top [Pagesize of the grid] col 1,2....n from tbl_emp where
(Filter conditions) and (pk_ID > [StartIndex])
order by Id"

pk_ID is the primary key of table.
StartIndex is the Value of Primary key of last record on the current
Page,which I pass to my SP.

Now,User can view a Prev Page only when he has visited that Page before
using Next Button.
My .Net code Stores(In the viewstate ) Id of the last record on the each
page Visited by clicking on Next Button.

So when user click on Prev Button ,My querry looks like this
"Select Top [Pagesize of the grid] col 1,2....n from tbl_emp where
(Filter conditions) and (pk_ID is betwen( [StartIndex,EndIndex)])
order by Id"

This used to work fine.
Now I have a new requirement on the grid,That I should be able to sort
records on the grid Using any of the column headers

So when User clicks on Fname column header..it sould sort by Fname.
So if i wnat to use sorting on column headers,My primary key gets
Jumbled.

Can anybody advice me or guide me to the solution of this.

Regards,
Raj(devgeekraj@xxxxxxxxx)





*** Sent via Developersdex http://www.developersdex.com ***
.

Relevant Pages

  • Re: Calculation of balance
    ... Is it that I keep a primary key for purchase_item so that each record will have an unique reference and I keep the key as a foreign key in the corresponding record in the goods_received table? ... the Grid control that uses the alias as its .RecordSource. ... User then use the cmdCheckIn button to add item into the lower grid, basically, it copy the PO item details on the upper grid and write it into the lower grid. ... There is a balance column on the lower grid which is supposed to automatically calculate the balance. ...
    (microsoft.public.fox.vfp.forms)
  • Re: help with microsoft ADO control/True DB Grid
    ... AutoNumber key and let it be the Primary Key. ... >> update data from a table that is in an Access 2003 database. ... >> The grid has ADODC1 selected as its data source. ...
    (microsoft.public.data.ado)
  • Re: Calculation of balance
    ... Imagine an order with multiple order opositions. ... Is it that I keep a primary key for purchase_item so that each record will ... the Grid control that uses the alias as its .RecordSource. ... User then use the cmdCheckIn button to add item into the lower grid ...
    (microsoft.public.fox.vfp.forms)
  • Re: Custom Paging in asp.net Datgrid Using Sql
    ... Here is a solution to dynamically return a sort result set back. ... CREATE TABLE #FileDownLoadJobs ... RowID INT IDENTITY NOT NULL PRIMARY KEY, ... I have written A SP in SQL which will be used to Bind the grid. ...
    (microsoft.public.dotnet.framework.aspnet.datagridcontrol)
  • Hi Deal or Big Strike?
    ... grid buttons and the "computer" effect created with relays. ... GRY ... Prev by Date: ...
    (rec.games.pinball)