Firoz Ansari's Weblog - All Comments

re: SQL Server 2005 - Data Paging

firoz.ansari — Wed, 03 Jun 2009 18:46:38 GMT

I am not sure but you don't need four separate queries for First/Prev/Next/Last link. Only one SQL statement will fetch data based on which "page" you want to show. Modified SQL will be look like this:

WITH ClientList AS (

SELECT ROW_NUMBER() OVER (ORDER BY CompanyID) AS RowNumber, <field list>

FROM Clients

)

SELECT *

FROM ClientList

WHERE RowNumber between 1 and 10

Put above SQL in SP and pass two additional arguments, PageIndex & PageSize which used to determine lower and upper record number.

In SP:

SET @PageLowerBound = @PageSize * @PageIndex

SET @PageUpperBound = @PageLowerBound + @PageSize

WITH ClientList AS (

SELECT ROW_NUMBER() OVER (ORDER BY CompanyID) AS RowNumber, <field list>

FROM Clients

)

SELECT *

FROM ClientList

WHERE RowNumber between @PageLowerBound and @PageUpperBound

Page will hold the current page number. If you click on Next, add +1 to the current page and pass it to this SP. Same way, if you click on Prev, substract 1 from current page.

Hope this help.

re: SQL Server 2005 - Data Paging

Yahya — Wed, 03 Jun 2009 17:52:21 GMT

Faraz, this is a related but is from access. I have four sql that bring first, next, prev and last recs respectively as below, not sure how to implement in sql server using ROW_NUMBER(), if at all this is feasible. Wonder if you can give some pointers.

First

SELECT TOP 1 <field list>

FROM Clients

WHERE (Status = @Status)

ORDER BY CompanyID

SELECT TOP 1 <field list>

FROM Clients

WHERE (CompanyID > ?) AND (Status = @Status)

ORDER BY CompanyID

SELECT TOP 1 <field list>

FROM Clients

WHERE (CompanyID < ?) AND (Status = @Status)

ORDER BY CompanyID DESC

Last

SELECT TOP 1 <field list>

FROM Clients

WHERE (Status = @Status)

ORDER BY CompanyID DESC

re: PowerMenu – Minimizing application to system tray

CHS — Wed, 13 May 2009 14:34:56 GMT

You don't need a batch to add switches, just create a shortcut for the program then right click the shortcut and go to properties then edit the target to display PowerMenu.exe" -hideself on and drag that shortcut to the startup folder.

re: Power of Literal Control

HS — Fri, 13 Feb 2009 17:31:18 GMT

Great Article..... I always wanted to find how to use JavaScript with Asp.net efficiently and from the code behind page....

re: TinySQL Code Generator

Ömer Faruk Z — Sat, 17 Jan 2009 16:29:12 GMT

cool, try codesmith

re: SQL Server 2005 - Data Paging

Estetik — Sun, 16 Nov 2008 18:22:09 GMT

I'm guessing the only way to do this in Javascript would be to use the onresize event, and then using the resizeTo method to attempt to keep the window at the size you want?

re: PowerMenu – Minimizing application to system tray

Jojo — Sun, 16 Nov 2008 11:33:25 GMT

I run that batch file but see a dos command window popping up.

I have added a vbscript file to call that batch file, then put a shortcut to the vbs file at the startup menu, so it starts up in the background when windows starts.

The vbs file can be created using a notepad to copy the following and save the file as PowerMenu.vbs

Set WshShell = WScript.CreateObject("WScript.Shell")

cmd = "PowerMenu.bat"

Return = WshShell.Run(cmd, 0, True)

set WshShell = Nothing

re: SQL Server 2005 - Data Paging

DragonGod — Fri, 17 Oct 2008 12:37:31 GMT

This is excellent.

Thank you.

re: Power of Literal Control

Crazy — Thu, 16 Oct 2008 04:22:17 GMT

Hi,

Thanks for your info on Literal Control

i got good knowledge on literal control

re: SQL Server 2005 - Data Paging

Okey — Mon, 21 Apr 2008 21:26:25 GMT

very thanks