move Next And Move Move Previous

[Jaseem Ahmed]

i m using sql server 2000 with vb 6. i m having some problem in navigating recors..
when i m pressing Move Next,it moves only one record, and when i m moving Move previous, samely it move one previous record...
here is a code...

Option Explicit
Private con As New ADODB.connection
Public rs As New ADODB.Recordset
Private Sub MyDatacon()
If con.State = 1 Then con.Close
con.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ISIS_157;Data Source=JASEEMAHMED"
con.Open
End Sub

Private Sub cmdnext_Click()
Call MyDatacon
Set rs = New ADODB.Recordset
rs.Open "SELECT * FRom ORGANIZATION", con, adOpenStatic, adLockOptimistic
If rs.EOF Then
MsgBox "Last Record"
Exit Sub
Else
rs.MoveNext
Text1.Text = rs!Orgcode
Text2.Text = rs!Organizationname
Text3.Text = rs!Shortname
rs.Update
rs.Close
con.Close
End If
End Sub
Private Sub Comdpre_Click()
Call MyDatacon
Set rs = New ADODB.Recordset
rs.Open "SELECT * FRom ORGANIZATION", con, adOpenStatic, adLockOptimistic
If rs.BOF Then
MsgBox "first Record"
Exit Sub
Else
rs.MovePrevious
Text1.Text = rs!Orgcode
Text2.Text = rs!Organizationname
Text3.Text = rs!Shortname
rs.Update
rs.Close
con.Close
End If
End Sub
Private Sub Form_Load()
Call MyDatacon
Text1.Enabled = False
Text2.Enabled = False
Text3.Enabled = False
Set rs = New ADODB.Recordset
rs.Open "SELECT * From ORGANIZATION", con, adOpenStatic, adLockOptimistic
rs.MoveFirst
Text1.Text = rs!Orgcode
Text2.Text = rs!Organizationname
Text3.Text = rs!Shortname
rs.Update
rs.Close
con.Close
End Sub

[Jx_Man]

hmm..so what the question here?

[Jaseem Ahmed]

QUESTION is that, when i press next, on every click it should show next next record...this code just showing one next record, then not working, i want to see every next record on every click...
e.g first record is 238000
when i press next i should show 238001
on other click it should show 238002
on third click, 238003...
so on....
samely the previous button should work...

[Jx_Man]

so what do you want?
showing next record and previous record its how move next and previous works.

[debasisdas]

That is because you are opening the recordset everytime.

You need to open the recordset only once (in the form load).

[AndreRet]

You have two options here, MoveNext and MovePrevious, as in your code, will ONLY move to the next or the previous record, ONE at a time until the BOF or the EOF is reached. Option two, which I think is what you are looking for, is to move directly to the first or the last record.

You can do this by using MoveFirst or MoveLast instead of Move Previous or MoveNext.

There is no other way to "jump" two records at a time.:)

[Jaseem Ahmed]

@ Andre Ret...i dont to move first and last record...i have its button aleardy..tht are working fine..
i exactly want next and previous...
i want these buttons to perform action of next and previous..
click by click it should show next,next,next records...till EOF is reacche
and previous button shuold perfrm pervious action,click by click, it should show previous records,till BOF is reached..

[AndreRet]

I see why your code is not working, it updates and then it is moving to the first record automatically. AFTER your MoveNext/MovePrevious, remove the rs.Update lines completely.

[Jaseem Ahmed]

@ debasisdas it is not working as u suggested, i tried...u may try this...

[Jaseem Ahmed]

i have removed rs.update from navigations...still not working...

[AndreRet]

To clarify some confusion here. When is it not working. Does it move to the next record only once and then not again? I am not sure where the problem lies, it is all working fine when I tested it just now.

[Jaseem Ahmed]

next is moving one record next, after that it is not working....
and previous is erroring of BOF or EOF is true, or current rfecorsd is deletd...

[debasisdas]

please clarify why you need to open the same recordset each time ?

[Jaseem Ahmed]

Does it move to the next record only once and then not again?

YES Andre....it is just moving record once not again...
i want to work these buttons like adodc have...
just as adodc's button works, first,previous,next,last...
i alsso want these buttons to work like them,..,,
last and first are working fine, but next and previous are not..
they just move once,not again...

[Jaseem Ahmed]

i dont want to open same record each time...i just want to buttons to work fine as adodc's buttons work...the next button should work everytime,not once...

[debasisdas]

Why can't you understand that since you are opening the same record set again and again on each click event of next and previous button you are always at the same record.

What you need to do is call the move methods of the recordset object that you have opened in the form load event.

[Jaseem Ahmed]

Call MyDatacon
If rs.EOF Then
MsgBox "Last Record"
Exit Sub
Else
rs.MoveNext
Text1.Text = rs!Orgcode
Text2.Text = rs!Organizationname
Text3.Text = rs!Shortname
rs.Update
End If
End Sub

@debasisdas now i m not opening recordset again and again...i used this code, but still it moves once only...

[debasisdas]

Remove rs.Update from move methods.

that needs to be handled separately.

[AndreRet]

Change your code to the following. Use the same for the previous sub -

Option Explicit

Dim xBookmark As Integer

Private con As New ADODB.Connection
Public rs As New ADODB.Recordset

Private Sub MyDatacon()

If con.State = 1 Then con.Close

con.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ISIS_157;Data Source=JASEEMAHMED"
con.Open
End Sub

Private Sub cmdnext_Click()

On Error GoTo Err

Call MyDatacon
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM ORGANIZATION", con, adOpenStatic, adLockOptimistic

'Set the recordset to the absolute position required to move next...
'Remember that the first record was loaded when you loaded your form!
'This will move to record 2, 3, 4 and so on...
rs.AbsolutePosition = xBookmark
rs.MoveNext
Text1.Text = rs!Orgcode
Text2.Text = rs!Organizationname
Text3.Text = rs!Shortname

'Add the next value to xBookmark to go to the next record....
xBookmark = xBookmark + 1

rs.Close
con.Close

Exit Sub

Err:

'End of file was reached, exit sub
MsgBox "Last Record"
Exit Sub
End Sub

Private Sub Form_Load()

xBookmark = 1
End Sub

[Jaseem Ahmed]

SUPERB........
Thanks Alot Alot Alot Alot.....
Love u dear AndreRet...

[AndreRet]

SUPERB........
Thanks Alot Alot Alot Alot.....
Love u dear AndreRet...

Hahaha, this is why I help here for free. Thanks for the praise, it was a pleasure Jaseem. If you have problems with the previous button, open a new thread, I'll post a solution there. The code is a bit different than above, but fairly obvious.:)

[Jaseem Ahmed]

You are really Genious one here...
Really Thanks alot Andre...
i have done pervious...and that is working very fine..
now all navigations are working fine..
will u please help me in treeview?

[AndreRet]

I saw the post. Will jump in as soon as I get a chance.:)

[Jaseem Ahmed]

Thanks alot....thanks....bundle of thanks...