Recordset paging headache .....

[David]

Hi,

I have always wanted to get the following working, but have never yet
mastered it :-(

I have an asp page which displays a list of records depending on what
was selected via another form.
I want to have these results broken down, displaying only 10 records,
with links underneath to display as
'Previous' and 'Next'

_____________________________________________________________

Firstly I have an include file for the connection which is:

strConnection = "xxxxxxxxxx"
Set adoDataConn = Server.CreateObject("ADODB.Connection")
adoDataConn.Open strConnection

______________________________________________________________

On my actual asp page, I just run the following code and print the
results on the screen

strquery = "SELECT * FROM reports ORDER BY ReportID DESC;"
Set RS = adoDataConn.Execute(strquery)

If RS.EOF then
Scream
Else
Print Results
End If
______________________________________________________________

What do I need to do to get this paging working. I have looked at many
example, but keep running into problems with bookmarks and recordset
not supporting this and that.

I would really appreciate any help in geting this working.

Thanks in advance

David

 

[Evertjan.]

David wrote on 24 apr 2007 in microsoft.public.inetserver.asp.general:

Hi,

I have always wanted to get the following working, but have never yet
mastered it :-(

I have an asp page which displays a list of records depending on what
was selected via another form.
I want to have these results broken down, displaying only 10 records,
with links underneath to display as
'Previous' and 'Next'

_____________________________________________________________

Firstly I have an include file for the connection which is:

strConnection = "xxxxxxxxxx"
Set adoDataConn = Server.CreateObject("ADODB.Connection")
adoDataConn.Open strConnection

______________________________________________________________

On my actual asp page, I just run the following code and print the
results on the screen

strquery = "SELECT * FROM reports ORDER BY ReportID DESC;"
Set RS = adoDataConn.Execute(strquery)

If RS.EOF then
Scream
Else
Print Results
End If
______________________________________________________________

What do I need to do to get this paging working. I have looked at many
example, but keep running into problems with bookmarks and recordset
not supporting this and that.

I would really appreciate any help in geting this working.

[If the ReportID is continuous from 0]

n = request.querysting("n")
if n="" or n<0 or not IsNumeric(n) then n=0

sql = "SELECT TOP 10 * FROM reports Where ReportID > "&n&"
ORDER BY ReportID;"


<a href='thisfile.asp?n=<% = n-10 %>'>Previous 10</a>
<a href='thisfile.asp?n=<% = n+10 %>'>Next 10</a>

 

[David]

Thanks Evertjan,

I get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-max]You have an error in your
SQL syntax. Check the manual that corresponds to your MySQL server
version for the right syntax to use near '10 * FROM reports Where
ReportID >0 ORDER BY ReportID DESC' at

/Repair_Reports/preview_all.asp, line 85

___________________________________________

I have coded:
strquery = "SELECT TOP 10 * FROM reports Where ReportID >" & n & "
ORDER BY ReportID DESC;"

 

[Evertjan.]

David wrote on 24 apr 2007 in microsoft.public.inetserver.asp.general:

[If the ReportID is continuous from 0]

n = request.querysting("n")
if n="" or n<0 or not IsNumeric(n) then n=0

sql = "SELECT TOP 10 * FROM reports Where ReportID > "&n&"
ORDER BY ReportID;"

<a href='thisfile.asp?n=<% = n-10 %>'>Previous 10</a>
<a href='thisfile.asp?n=<% = n+10 %>'>Next 10</a>

[Please do not toppost on usenet]
[Please do not include signatures in quotes on usenet]

I get this error:-

[MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-max]You have an error in your
SQL syntax. Check the manual that corresponds to your MySQL server
version for the right syntax to use near '10 * FROM reports Where
ReportID >0 ORDER BY ReportID DESC' at

I have coded:
strquery = "SELECT TOP 10 * FROM reports Where ReportID >" & n & "
ORDER BY ReportID DESC;"

Not that it explains the error, but the DESC is illogical with
Where ReportID >" & n & "

Is ReportID a numeric field ?

Or it could be that top is not allowed with * in MySQL,
I never used mySQL under ASP.

Please report the litteral SQL result resulting from this::

strquery = "SELECT TOP 10 * FROM reports Where ReportID >" & n & "
ORDER BY ReportID DESC;"
response.write strquery
response.end

 

[David]

Evertjan,

your code prints:
SELECT TOP 10 * FROM reports Where ReportID >0 ORDER BY ReportID DESC;

 

[Evertjan.]

David wrote on 24 apr 2007 in microsoft.public.inetserver.asp.general:

Evertjan,

your code prints:

Sorry, I do not like topposting on usenet.