Going backwards through recordsets

[bellmd]

I'm trying to use recordsets to display a list of items, this is simple, but I don't seem to be able to display them in reverse chronological order (most recent first), as they are added into the database in chronological order. I have tried several things, but they all cause errors of some sort.

while not genNewsSet.EOF

Response.Write genNewsSet("title")

Response.Write &quot;<br>&quot;

genNewsSet.moveNext

wend

[sig][/sig]

 

[ciberperson]

I think you might have to set the reverse order in your SQL select statement by adding at the end of the statement an "order by" command. You statement would look like this: "Select * FROM sometable ORDER BY somefield DESC;"

The DESC (descending) reverses the fields. Good luck. I've had some great help from the people on this board. [sig][/sig]

 

[bellmd]

Cheers, that's great, I also have had some great help from people on this site, which includes you ciberperson.

Thanks again,

bellmd
matthew@sexycooking.co.uk [sig][/sig]

 

[wduty]

Ciberperson's right.

However, here are some further notes and suggestions:

Ciberperson's suggestion will work as long as you don't want to move backwards and forwards. If you want to do both on the same recordset then you have to use the recordset.movenext and the recordset.moveprevious methods. But if you try to do this and you don't have your recordset set up correctly you'll get an error like this:

[red]The operation requested by the application is not allowed in this context.[/red]

There are two ways to achieve a two-way freedom of motion in the recordset. The first is to set the recordset cursor to dynamic mode. A lot of the time all you need to do to open a recordset is write:

recordsetName.open someSQLstring, connectionName

HOWEVER, although there are only two parameters passed to the open method as shown above, there are three more parameters you can pass to this method. The full FIVE parameters for the recordset.open method are:

recordset.open source, databaseConnection, cursorType, LockType, whatthesourceis

The important one here is the third parameter "cursorType" which, if you don't include it, defaults to 0 or "adOpenForwardOnly". If you set this value to 2, however, you will have the recordset in "adOpenDynamic" mode which means you can move backwards and forwards. Here's an example which moves first all the way forward then all the way back through a single recordset:
-----------------------------------------------------------

[red]
set db = server.createObject("ADODB.Connection&quot
db.Open "yourdatabase"
set testrec = server.createObject("ADODB.recordset&quot
Const adOpenDynamic = 2
testrec.Open "SELECT name FROM table ORDER BY name", db, adOpenDynamic[/red]

'-------------FORWARDS
[red]do while NOT testrec.EOF
[tab]response.write(testrec(&quot;name&quot&&quot;<br>&quot
[tab]testrec.movenext
loop[/red]

'-------------BACKWARDS
[red]testrec.moveprevious
do while NOT testrec.BOF
[tab]response.write(testrec(&quot;name&quot&&quot;<br>&quot
[tab]testrec.moveprevious
loop
[/red]


2. The other way to achieve two-way motion would be to use &quot;recordset.getrows&quot; which transfers your recordset into an regular VBScript (or JScript) array. Then you can do whatever you want with it that you can do with an array. This option is more scaleable also. There's an explanation of how to do this at:

http://www.learnasp.com/learn/dbtablegetrows.asp

Good Luck! [sig]<p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br> [/sig]

 

[bellmd]

Cheers. That is what I tried initialy, but it didn't work, this was, as you pointed out, due to the cursor type. That has solved another problem. Thanks again.
Also the recordset.getrows will come in use some time I'm sure.

Matthew Bell [sig][/sig]

 

[wduty]

One more about setting cursor types. ADO has a huge set of constants which you can use for setting all kinds of things from cursor types to command types to lock types etc. For clarity I wrote the one constant necessary in the above script manually:

const adOpenDynamic = 2

However, if you plan to use a bunch of these constants then you might want to get a hold of a standard page called

adovbs.inc

which has all of them. If you open this page it looks like this:


'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000
Const adFind = &H00080000
Const adSeek = &H00400000
Const adIndex = &H00800000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

and so on for another few hundred lines.

This page is available at MSDN somewhere. Some people recommend putting this page in your asp pages as an include although I never do this because I don't want my script to have to process all those lines. But if you plan to do fancy stuff with recordsets or especially ADO commands you should get a hold of this page because it will be handy.

Cheers and good luck!



[sig]<p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br> [/sig]