Pagination

Hi,

I have recently moved into ms sql from mysql and having problems with

finding a query to help paginate my search results. In mysql I would

use:

SELECT * FROM tablename WHERE something = something LIMIT 0,10

what would a similar query be in sql server?

From what I have experienced so far I know this is going to be a big query!

Thanks in advance

Steve209 views and no replies!!

I went out and managed to find a solution on my own.

cheers for all the help|||

can you paste the solution. i will help me too..

cheers

|||Yeah sure,
SELECT TOP $records_per_page * FROM tbl_name WHERE field LIKE '$getID'
AND id NOT IN (SELECT TOP $page id FROM tbl_name)
This is the dymanic version. In a nutshell;
SELECT TOP $records_per_page* FROM answers - selects the first 'n' records from table

AND id NOT IN (SELECT TOP $page id FROM answers) - id has to be a

unique identifier, NOT IN is declaring the offset; where id is not in

the top $page of this table
so
SELECT TOP 10 * FROM tbl_name WHERE field LIKE 'steve'
AND id NOT IN (SELECT TOP 20 id FROM tbl_name)
will return records 11-20 (presuming there are that many records)
alternatives can be found here

Pagination

Hi,
I have recently moved into ms sql from mysql and having problems with finding a query to help paginate my search results. In mysql I would use:
SELECT * FROM tablename WHERE something = something LIMIT 0,10
what would a similar query be in sql server?
From what I have experienced so far I know this is going to be a big query!
Thanks in advance
Steve
209 views and no replies!!
I went out and managed to find a solution on my own.
cheers for all the help
|||

can you paste the solution. i will help me too..

cheers

|||Yeah sure,
SELECT TOP $records_per_page * FROM tbl_name WHERE field LIKE '$getID'
AND id NOT IN (SELECT TOP $page id FROM tbl_name)
This is the dymanic version. In a nutshell;
SELECT TOP $records_per_page* FROM answers - selects the first 'n' records from table
AND id NOT IN (SELECT TOP $page id FROM answers) - id has to be a unique identifier, NOT IN is declaring the offset; where id is not in the top $page of this table
so
SELECT TOP 10 * FROM tbl_name WHERE field LIKE 'steve'
AND id NOT IN (SELECT TOP 20 id FROM tbl_name)
will return records 11-20 (presuming there are that many records)
alternatives can be found here

Paginate VB options

I have a VB.NET 2005 program that on a click of a button exports my report to a text file. This works fine apart from one thing, i get a strange charactor every 60 lines. So i opened Crystal Deveolper and tried exporting from there and it gave me and option set number of lines per page and setting this to 0 switched off paginate, which removed the charactors.

My question is this how do i add the options for Charactors/Inch and Number of Lines per Page (setting this to 0 switches off paginate). Here is my code so far:-

Private Sub btnGenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenFile.Click Dim

Exportpath As String Exportpath = "n:\Regatta Order Files\"
CrDiskfileDestinationoption = New DiskFileDestinationOptions
CrExportOption = CrReport.ExportOptions
CrDiskfileDestinationoption.DiskFileName = Exportpath + ("CHAN1" & sDate & ".txt")
With CrExportOption
.DestinationOptions = CrDiskfileDestinationoption
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.Text
End With

Try CrReport.Export()
'MessageBox.Show("Exported to " & CrReport.ExportOptions.DestinationOptions.DiskFileName)
txtStatus.Text = "Text File has been created, you can now tranfer to Regatta"
Catch ex As Exception
MessageBox.Show(Me, ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

End Sub

I have XP Pro SP2
Visual Studio 2005
Crystal Reports Developer XI R2dump