Recordset paging headache .....

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

Re: Recordset paging headache .....

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;"






--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: Recordset paging headache .....

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;"





------------------------------------------------------------ -----------------------------------------




>
> [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;"
>
>
>
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

Re: Recordset paging headache .....

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;"
>>
>>
>>

[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

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: Recordset paging headache .....

Evertjan,

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

>>> ReportID is the TableID field (Numeric)

------------------------------------------------------------ ------------------------------------------



>
> 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
>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)

Re: Recordset paging headache .....

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

> Evertjan,
>
> your code prints:

Sorry, I do not like topposting on usenet.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)