finding absolute row in a dataset

[brews]

VB6 used movenext and moveprevious to increment/decrement the absolute record in a dataset. VS 2008 does not seem to have those features.

This is what I have to start. iMemberID finds the current record, next find the total number of rows (iMaxRows), then run through the database looking for the iMemberID to find that row number using the For i, next loop.

    Public Function GetAbsRec(ByVal iMemberID As Integer, ByRef absPos As Integer) As Integer 'iMemberID is current record
        Dim rw As DataRow
        ds = New DataSet()
        da = New OleDb.OleDbDataAdapter
        cn.Open()
        cmd = New OleDbCommand("SELECT MemberID, Lastname, Firstname FROM Members" & _
                               " WHERE MemberID=" & iMemberID & _
                               " ORDER BY Lastname, Firstname", cn) 'Finds the current record (starting point)
        da.SelectCommand = cmd

        da.Fill(ds, "Members")
        da = New OleDbDataAdapter

        cmd = New OleDbCommand("SELECT * FROM Members", cn)
        da.SelectCommand = cmd
        da.Fill(ds, "Members")
        iMaxRows = ds.Tables("Members").Rows.Count - 1 'iMaxRows determines # of rows in table

        For i = 0 To ds.Tables("Members").Rows.Count - 1
            rw = ds.Tables("Members").Rows(i)
            If rw.Item("MemberID") = iMemberID Then
                abspos = i
            End If
        Next
        If abspos = iMaxRows - 1 Then ' At the end of the table
            abspos = 0 'this should be the first record
            rw = ds.Tables("Members").Rows(abspos)
            iMemberID = rw.Item("MemberID")
        End If
        cn.Close()
        Return absPos
    End Function

Using a known row number from the database of 30 and choosing that record (iMemberID=12), this function does not do what is intended.

Any ideas would be great. thanx.

 

[Andrzejek]

This is just my opinion here, but...
It is wrong to assume that if you ask "SELECT * FROM Members" (without Order By) and you have a record in your Members table with MemberID = 4321 that this record will always be at the 12th row/position.
Records in the table(s) are NOT in any particular order, they are like marbles on the back of the truck, they are all over – as far as order goes - unless YOU order them when asking for them.


Have fun.

---- Andy

 

[brews]

Isn't that what is being done?

 

[SkipVought]

What is the purpose of returning the relative position of the member row?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Yes, it is 'being done' here.
My point here is: you run this code and let's say it returns 12.
A few seconds later, or a few minutes later, or next day, (or whatever) you run the same piece of code and it returns 18, or 256, or 123456

Like Skip says: what good does it do for you to know that?

Have fun.

---- Andy

 

[brews]

What I am trying to do is to increment/decrement by one (1) record alphabetically. Threrefore, knowing the datarow, I could go forward/backward by one row. Is there a better way? thankx.

 

[SkipVought]

increment/decrement by one (1) record alphabetically

For what reason?

A relational table has ABSOLUTLY NO INTERNAL ORDER! Some fields may have an index built, but that is defined in the schema and is handled, behind the scene.

All you need do is ADD a row. You might need to check to see if a key field(s) exist before adding, but there is no need to "increment/decrement by one"!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Brews, people here at TT ARE trying to help others, and ask additional questions not to annoy but to understand the problem and point to the solution, or to another way of solving the original problem.

It would be helpful if you would state what you are trying to do, give some examples of the problem and how the solution should look like.

Unless, of course, ‘this IS what I want and I don’t want it any other way’. Period. Which is OK, too.


Have fun.

---- Andy

 

[brews]

It appears that this was perferctly clear to nobody but me. Let me start again.

When a record is being edited, then saved, it would be convenient to be able to scroll through the database, alphabetically to view and if necessary make corrections, save the record and move on.

To me that was somewhat easy to accomplish in vb6 with this code:

Public Sub NextRecord(ByVal iID As Integer, NM As CMember)
    Dim iCount As Integer
    Dim iAbsolute As Integer
    sSql = "Select * FROM Members ORDER BY Lastname, Firstname, ID"
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
    rs.Open sSql, cData, adOpenForwardOnly, adLockReadOnly, adCmdText
    Do Until rs.Fields("ID").Value = iID
        rs.MoveNext
    Loop
    rs.MoveNext
           
    If rs.EOF Then
        rs.MoveFirst
        If rs.BOF Then
            Exit Sub
        End If
    End If
End Sub

Since that is not available, I thought finding the datarow number and then either advancing 1 or going back 1 would serve my purposes.

I never for a moment thought that your questions were nothing more than to clarify the situation. It just took some time for the bulb to come on for me. I hope this is now clearer.

Thanks for your patience.