previous in Recordset

[ThomasBergk]

Hello,

the object Recordset offers the option to passed through a record with the help of the methods moveNext. For me I have a record and I want to go forward and backward.

Is there a option to go backwards in the record, like a method previous?

Thanks in advance,

Thomas

[Otmar]

According to my information, the recordset object support the methods:

recordset.{MoveFirst | MoveLast | MoveNext | MovePrevious}

Use the MoveFirst method to move the current record position to the first record in the Recordset.

Use the MoveLast method to move the current record position to the last record in the Recordset. The Recordset object must support bookmarks or backward cursor movement; otherwise, the method call will generate an error.

A call to either MoveFirst or MoveLast when the Recordset is empty (both BOF and EOF are True) generates an error.

Use the MoveNext method to move the current record position one record forward (toward the bottom of the Recordset). If the last record is the current record and you call the MoveNext method, ADO sets the current record to the position after the last record in the Recordset (EOF is True). An attempt to move forward when the EOF property is already True generates an error.

In ADO 2.5 and later, when the Recordset has been filtered or sorted and the data of the current record is changed, calling the MoveNext method moves the cursor two records forward from the current record. This is because when the current record is changed, the next record becomes the new current record. Calling MoveNext after the change moves the cursor one record forward from the new current record. This is different from the behavior in ADO 2.1 and earlier. In these earlier versions, changing the data of a current record in the sorted or filtered Recordset does not change the position of the current record, and MoveNext moves the cursor to the next record immediately after the current record.

Use the MovePrevious method to move the current record position one record backward (toward the top of the Recordset). The Recordset object must support bookmarks or backward cursor movement; otherwise, the method call will generate an error. If the first record is the current record and you call the MovePrevious method, ADO sets the current record to the position before the first record in the Recordset (BOF is True). An attempt to move backward when the BOF property is already True generates an error. If the Recordset object does not support either bookmarks or backward cursor movement, the MovePrevious method will generate an error.

If the Recordset is forward only and you want to support both forward and backward scrolling, you can use the CacheSize property to create a record cache that will support backward cursor movement through the Move method. Because cached records are loaded into memory, you should avoid caching more records than is necessary. You can call the MoveFirst method in a forward-only Recordset object; doing so may cause the provider to re-execute the command that generated the Recordset object.

It looks like MovePrevious would be the method you want ...

     Otmar

[Pesmith8]

Thomas,

Sounds like you have the wrong cursor type.  When you make a ADO object that allows movement in side a recordset (returned from sql query)   The cursor limits movements. The base one a forward only cursor. 

Sounds like you are wanting a backward and forward cursor, That should be a "keyset cursor"

If you search for "ado cursor" in DIAdem (2014) help it will return a example of using the keyset cursor. That should allow you to movearound in the returned recordset.

see line  

RecordSet.CursorType = adOpenKeyset

Paul

[ThomasBergk]

Hello Paul,

ok, I thought that would be the normally setting to go for- and backwards. I try to change the cursor type in Diadem 2014 in respect to the example you have send me. But Diadem did not know the method ".CursorType" of the object Recordset. If I use the method ".movePrevious" of the Recordset the error occurs: "that method is not possible in that context".

Do you know why it did not work?

Thanks in advance,

Thomas

[Pesmith8]

HI Thomas,

This sounds like it could be hard to solve from a distance.

Anyway will give it a try.

The cursor types are enums that are listed below.

When the recordset.open command is issue it also will accept two agruments after it,

So to allow movement forward and back,  use 

"recordset.open  3 3"  the other lines that refer to cursor tyupe or type of lock will not be needed when using this way.

Also if this is getting to be to much trouble, I would suggest to read the recordset once, and transfer the values to class array. That way you read it any way you like,

Note: I have not tried the recordset.open option above, but it should work.

Paul

CursorTypeEnum Constants
 

Constant	Value	Description
adOpenDynamic	2	A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
adOpenForwardOnly	0	Default, a forward scrolling only, static cursor where changes made by other users are not visible
adOpenKeyset	1	A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
adOpenStatic	3	A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
adOpenUnspecified	-1	Cursor type not specified

 
LockTypeEnum Constants
 

Constant	Value	Description
adLockBatchOptimistic	4	Multiple users can modify the data and the changes are cached until BatchUpdate is called
adLockOptimistic	3	Multiple users can modify the data which is not locked until Update is called
adLockPessimistic	2	The provider locks each record before and after you edit, and prevents other users from modifying the data
adLockReadOnly	1	Read-only data
adLockUnspecified	-1	Lock type unknown