Need help Navigating through ADO Recordset

[EdRev]

Can anybody help me with the code below. I'm expecting the query to return at leat 100 records, it displayed the first record but when I click on the next button, it just doesn't do anything. And the previous button generates a run-time error of "3219" - The operation requested by the application is not allowed in this context.

Any help will be greatly appreciated

Public cn As ADODB.Connection
public adors As ADODB.Recordset

Private sub qryCase()

Set cn = New ADODB.Connection
cn.Provider = "Microsoft.Jet.OLEDB.3.51"
cn.ConnectionString = "C:\Program Files\Microsoft Visual Studio\VB98\cases.mdb"

cn.OpenSet adors = cn.Execute(strCriteria)

txtCase.text = adors!case_no
txtOwning.text = adors!owning_area
txtRLoc.text = adors!area_no
...some more codes...

end sub

private sub cmdNext_click
if not adors.eof
adors.movenext
end if

Private Sub cmdPrevious_Click()
If Not adors.BOF Then
adors.MovePrevious
End If
End Sub

 

[cyberdyne]

Hi !

I saw yr code. I am giving you a sample code for the same. Pls modify table name accordingly when u actually implement it.
------------------------------------------------------------

Option Explicit
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
-------------
'create arrays of 4 command buttons.
' 1 button = Move First
' 2 button = Move Next
' 3 button = Move Previous
' 4 button = Move Last

Private Sub Command1_Click(Index As Integer)
If Index = 0 Then
rs.MoveFirst
ElseIf Index = 1 Then
If Not rs.EOF Then
rs.MoveNext
End If

ElseIf Index = 2 Then
If Not rs.BOF Then
rs.MovePrevious
End If
ElseIf Index = 3 Then
rs.MoveLast
End If

If rs.BOF And rs.EOF Then
Exit Sub
ElseIf rs.BOF Then
rs.MoveFirst
ElseIf rs.EOF Then
rs.MoveLast
End If
'MsgBox (rs(0))
Text1.Text = rs(0)
Text2.Text = rs(1)

End Sub

-------------------------
Private Sub Form_Load()

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb"
con.Open
rs.Open "select * from products", con, adOpenDynamic, adLockPessimistic
End Sub

 

[cyberdyne]

try it and let me know . if u need n e more help. mail me at cyberdyne@softhome.net

 

[EdRev]

thanks cyberdyne,

sorry didn't reply to your post sooner. Anyway, the code didn't work for me. for now, i've just been trying to play around with the connectionstring.

if you have any ideas, let me know. At this point I'll try anything.

thanks again,

 

[cyberdyne]

thats was not the actual code. that was an example for u . u need to modify my code little such as table name etc.
this was to give u an idea how to go about.

 

[koppelsteel]

Check what type of cursor you are using. If you don't specify a type I believe that ADO defaults to Forward-only. This would explain why you can't do a readprev.

 

[MelissaEvans]

It looks like you are setting the text, but not binding the column to the text box. Whenever you move through the recordset, you'll need to update the text box's text also.

The previous button won't work becuase you're already at the beginning of the recorset. You'll have to do some error checking to prevent the user from going out of bounds. Use &quot;If RS.EOF Then <disable move next>&quot; and &quot;If RS.BOF Then <disable move previous>&quot;

FYI - there is a database forumn for VB also; you might want to check them out for questions. =)

Happy programming!

 

[EdRev]

thanks eveerybody for the feedback,

yes, cyberdyne, I modified your code to my specs. It only retrieved one record and then it's EOF (although I was expecting at least 100).

I checked for EOF and BOF for my movenext and moveprevious function. I guess it worked, it's just that there's only one record to display.

I specified the cursor type as adOpenDynamic which I believe can let me navigate the recordset forward and backward.

I'm still stuck on this, I'll try any suggestions you might have.

 

[RMDL]

You didn't make set de datasource of the textboxes...

Try this:

set txtCase.datasource = adors
set txtOwning.datasource = adors
set txtRLoc.datasource = adors

And in design mode for every text box you indicate de data field of the textbox.

 

[EdRev]

thanks RMDL,

it's a very long process but it worked!!