Friday, May 8, 2009

SQL Server Paging and displaying Paged results on HTML Page

This is a situation which every SQL Server developer might face once in his lifetime. How to retrieve results based upon row counts? and display them on HMTL Pages. For the dismay SQL Server does not provide functionalities like MySQL LIMIT/OFFSET which makes it a bit more complicated. But my simple trick allows you to do paging by using simple SELECT statement combined with TOP. Apart from SQL Paging I have also included code to implement paging in HTML Pages so if you are using some other database just change the query appropriately and you are good to go.

Suppose you have to list all the customers from some database table filtered by some search string and the user submits the search string from an HTML form which is passed on to the search page as URL string (search.aspx?search=searchstring). Search page then displays results (MAX_RESULTS) on each page. To keep track of the number of displayed results we can use another field num in the URL String. So the initial search string will look like:

search.aspx?search=searchstring&num=0

Now to implement the search.aspx code we need to get the variables from the URL String and set the MAX_RESULTS limit:

Dim search As String = Request.QueryString("search")
Dim num As Integer = CInt(Request.QueryString("num"))
If num < 0 Then num = 0
Dim MAX_RESULTS As Integer = 10

Next step is to format the SQL Query. I have used nested Selects to restrict the results by not selecting the results matching the inner Select statement. The second Select statement just selects the number of rows after which we need to display the results:

Dim where As String = "Firstname LIKE '%" & search & "%'" 
Dim
cmdString As String = "SELECT TOP " & MAX_RESULTS & " * FROM CUSTOMERS" & _
                          "WHERE CustomerId NOT IN (" & _
                          "SELECT TOP " & num & " CustomerId FROM CUSTOMERS" & _
                          "WHERE " & where & _
                          "ORDER BY FirstName )" & _
                          " AND " & where & _
                          "ORDER BY FirstName"

After executing the above query and displaying the results we need to display the links for Next and Previous page navigation. To do that we first need to get the total count (total_count) of results from the database using the following query:

cmdString = "select count(*) from CUSTOMERS WHERE " & where

To format the Previous and Next page URL’s with Next and Previous results left we have used two variables Next_URL and Prev_URL as follows:

Dim Next_URL As String = ""
Dim Prev_URL As String = ""
Dim Nav_Table As String = ""
If total_count > MAX_RESULTS Then 
 
Dim next_count = num + MAX_RESULTS 
 
Dim prev_count = num – MAX_RESULTS
 
If num = 0 Then
    Next_URL = "<a href=""search.aspx?search=" & search & "&num=" & next_count & """>Next(" & total_count - next_count & ")&gt;</a>"
    Prev_URL = "&nbsp;"

  ElseIf next_count > total_count Then
   
Next_URL = "&nbsp;"
   
Prev_URL = "<a href=""search.aspx?search=" & search & "&num=" & prev_count & """>&lt;(" & num & ")Prev</a>"

 
Else
   
Next_URL = "<a href=""search.aspx?search=" & search & "&num=" & next_count & """>Next(" & total_count - next_count & ")&gt;</a>"
   
Prev_URL = "<a href=""search.aspx?search=" & search & "&num=" & prev_count & """>&lt;(" & num & ")Prev</a>"
  End If
End If

If you are using some other database system or programming language, you just need to change the SQL statement appropriately and use the same logic to display the results in html.

Technorati Tags:

3 comments:

  1. Guaranteed #1 Search Engine Ranking Supreme Free Viral Traffic Join Now Get Millions Of Hits Free To Your Site/Blog!

    PornKings Adult Shopping Backlinks-Shopping Mega Store Legendary Stars As Stormy Daniels,Shawna Edwards,Jenna Jamison-New Adult Stars Movies,Adult Toys,Enhancers,Merchandise-More !

    Hits Express Rotator System Do You Need Visitors to Your Website or Affiliate Program? If your looking to gain more visitors to your website Hits Express is your answer. With our program your site is being shown to people all over the world 24/7 365 days a year!

    PAYDIR Free For All Forum Portal Search Engine Crawled Network PR10 Ranking Information Forum XXX!

    FreeLinkExchanges Be Seen In 12 Nations 312 Sites Over 30 Millions Viewers Monthly Buy Featured Link Now With 150 Search Engines Crawling The Network!

    How do I get guaranteed traffic? When someone signs up from your site, they must first click on your classified ad which will open a new window leading to your main website. They will have to wait a few seconds for the code to appear on a separate frame at the top of the screen reach millions free now!

    Blast Your Ad to Over 23,000 Opt-in Prospects at ShowMyLinks Submit Your Solo Email Ad to All Showmylinks Members GET YOUR TEXT LINK ADs LISTED 100% FREE FOR LIFE PLUS EARN MONEY TO YOUR PAYPAL!!

    Adult Store Empires Backlinks Resources Search Engine XXX!

    GET YOUR OWN MONEY-MAKING AD BOARD -- Integrating Text ad, Banner Ad and Email Ad into one Portal Make Money Fast With Your Paypal Reach Million Dollars In A Year Fast !

    Full Length Homemade Videos Watch real people's private home sex videos. Forget about those shitty 1-2 minute clips, these are all high quality full length videos! 40+ Bonus Sites You will also get full access to the entire reelpass network of sites which features over 40+ awesome movie sites, 1000's of movies and pictures to download! Download, Burn & Share Not only do you get to watch all our movies, you can also download every single one and save it forever, burn them to dvd or share with friends!!

    ReplyDelete

Thanks a lot for your valuable comments :)