MOVELAST - CURSOR HELP

[sarahjane28]

Hi,

I am trying to move through a recordset and keep getting the error "Microsoft OLE DB Provider for SQL Server: Rowset does not support fetching backwards"

Here is my code,
Dim myconn, rs, SQL
Dim MyPage
Dim String

MyPage = Item.GetInspector.ModifiedFormPages("CustomerInformation&quot
Set myconn = CreateObject("ADODB.Connection&quot
myconn.Open "Provider=SQLOLEDB;Data Source=Sutherland3;UID=sa;PWD=;DATABASE=cmf"
Set rs = CreateObject("ADODB.Recordset&quot
SQL = "SELECT * FROM Incident"
rs.Open SQL, myConn, adOpenDynamic,, adCmdText

rs.MoveLast
rs.MoveFirst
For i = 1 to rs.RecordCount
MyString = rs("IncidentID&quot & ";"
rs.MoveNext
Next

MyPage.ComboBox1.PossibleValues = MyString

Can anyone help me?

Thanks!,
Sarah

 

[petermeachem]

This

rs.MoveLast
rs.MoveFirst
For i = 1 to rs.RecordCount
MyString = rs("IncidentID&quot & ";"
rs.MoveNext
Next

Could be replaced by

if not( rs.eof and rs.bof ) then
do until rs.eof
MyString = rs("IncidentID&quot & ";"
rs.MoveNext
loop
endif



Peter Meachem
peter @ accuflight.com

 

[stnkyminky]

First thought would be that the cursor type doesn't support movement through the recordset. However, I thought adOpenDynamic supported that feature.

Might want to check out the link below for ADO

http://msdn.microsoft.com/library/d...us/ado270/htm/dasdkadooverview.asp?frame=true

also if your trying to speed up reading records you might want to set the record count eq to a variable and then reference that in the loop.

ex.
intNumRec = rs.RecordCount

for i=1 to intNumRec

'Code
next

Other wise I don't think your for loop is any different than writing do until rs.eof Scott
Programmer Analyst
<{{><

 

[vb5prgrmr]

I also do not see a check for the actual return of records.

[tt]
If Rs.RecordCount <> 0 And Rs.Eof = False And Rs.Bof = False Then
Rs.MoveLast
MaxCnt = Rs.RecordCount
Rs.MoveFirst
End If

Do While Not Rs.Eof ...

[/tt]

Good Luck

 

[petermeachem]

The original question pointed out the fact that it didn't like movefirst. The other two suggestions both still have a movefirst.

Sorry to sound smug, but I've had some sort of a virus (personal not computer) and I feel ok now! Peter Meachem
peter @ accuflight.com

 

[stnkyminky]

Would it not have failed on MoveLast first?
I still say it's the cursor.......... Scott
Programmer Analyst
<{{><

 

[vb5prgrmr]

Ah, you are so right petermeachem, I missed that part, but my post does point out that there is no check to see if there are any records to begin with so if they want to fore go retrieving the record count they could also forget about movelast and movefirst and just go with the...

[tt]
Do While Not Rs.Eof

'Do Stuff
Rs.MoveNext

Loop

[/tt]

as per your suggestion. But none of the posts determine why the recordset is not allowing them to navigate the recordest forward and backwards, so I will suggest the missing parameter in the open statement should be adLockOptimistic...

[tt]
rs.Open SQL, myConn, adOpenDynamic, adLockOptimistic
[/tt]

and remove the options parameter.

I Hope This Helps

 

[petermeachem]

Well i suggested if not( rs.eof and rs.bof ) then

I wonder if you are correct with the missing adLockOptimistic. I've never tried it without. Peter Meachem
peter @ accuflight.com

 

[sarahjane28]

THANK YOU SO MUCH EVERYONE!!!! : )

Using Peter's orginal suggestion worked, it's odd, I can use Bof & eof but not movefirst or movelast.

 

[vb5prgrmr]

petermeachem,

As neither have I but I did look it up on help and...

adLockReadOnly (Default) Read-only—you cannot alter the data.

And I remember from somewhere that readonly can be forward only (depending on version and method) but I cannot find that reference...

Maybe some day I will run a test to see...

And, yes, you did

And sarahjane28, whereas Bof and Eof are tests of the current position they do not actually require a reposiioning which is why you can use them.

I Hope this helps

 

[stnkyminky]

Cursor Type
adOpenForwardOnly(default) - supports only forward movement through the recordset.

adOpenDynamic - supports full movement through the recordset.

Lock Type
adLockReadOnly(default) - specifies that the record in the recordset object can only be read.

Cursors controls how the recordset is navigated and how the data is updated.

I would display the recordsets CursorType, LockType and CursorLocation to see what it is using.
rs.CursorType, rs.CursorLocation, rs.LockType Scott
Programmer Analyst
<{{><