Record Set Problems

[Guest_imported]

hi friends,
I have been working with Recordset.. and i have studied the function called
rs.Movefirst
rs.movelast
rs.movenext and
rs.moveprevious

But i could'nt use the rs.movelast since it gives the error
Run time error...
The rowset does not support fetching backwards....

What i want to do is that i want to go to the last record in the recordset and then obtain its value...
if any of u are having another way to handle this problem please let me know about you

 

[sunaj]

Hi,

You don't mention if you use DAO or ADO.
how you can navigate your recordset depends on the recordsettype. e.g. if its 'forward only' you can only go one way (movenext and movelast). Lets see your code...

However all recordsets should be able to move to the last record and read that value, if you don't do anything else with the recordset.

Sunaj

 

[Guest_imported]

hi
I am using ADO......and the code is..


Private Sub Form_Load()

Dim str_rightnum As String
Dim rsfind As New ADODB.Recordset
Dim str_filenumber As String



str_filenumber = Left(frmMain.ListView1.SelectedItem.Text, 8)

txtfilenumber.Text = strfilenumber

strfilenumber = strfilenumber & "%"

Set rsfind = conn.Execute("select patient_no from patient where patient_no like '" & str_filenumber & "' order by patient_no &quot

rsfind.MoveLast *********** :-Q This is not working

int_rightnum = Right(rsfind!patient_no, 2)
txtpatientnumber.Text = int_rightnum


End Sub

 

[sunaj]

Hi,

set rst = con.execute is a DAO way to open a recordset. Try this instead:
------------------------------------------------------
rsfind.open "select patient_no from patient where patient_no like '" & str_filenumber & "' order by patient_no ", conn
if not (rsfind.eof and rsfind.bof) then
rsfind.movelast
'code here...
else
msgbox "no entries in recordset"
endif
rsfind.close
set rsfind = nothing
------------------------------------------------

after the ', conn' in the open statement you can define the recordtype and the locktype, as I remember default is forward only which is fast but not very navigable. You can use 'static' or 'keyset' for more navigable recordsets.

Sunaj

 

[Guest_imported]

hi sunaj,
Sorry to say but on the line rsfind.movelast in your code is also generating the same error..
The rowset does not support fetching backwards....

Please tell me how can i get rid of it..

 

[sunaj]

Hi,

I've just tested it and you're absolutely right.
You have to set the recordtype to use .movelast. Use recordtype 'static' or 'keyset'.


----------------------------------------------------------
Private Sub Command1_Click()
'Set references to Microsoft ActiveX Data Objects 2.5 Library (MSADO25.tlb)
Dim con As ADODB.Connection
Dim rst As Recordset, StrCon As String

StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\tmp\test.mdb"
Set con = New ADODB.Connection
con.Open StrCon

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM TblTest", con, adOpenStatic, adLockOptimistic
If Not (rst.EOF And rst.BOF) Then
rst.MoveLast
MsgBox rst.Fields(0)
Else
MsgBox "Empty recordset"
End If
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
End Sub
----------------------------------------------------------

Sunaj

 

[Guest_imported]

Thanks sunaj,
it worked.......