moving through records in .net

[deano1]

Hi All,

I am having some trouble moving through records using .net. What im trying to do is move through the records stored in my dataset one at a time (each time i click a button), and then display certain fields. I would also like to be able to go back through records one at a time. maybe theres a .next and .previous function i can use. Any help would be appreciated.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim flag As Integer = 0
Dim db As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\helpdesk.mdb;")
Dim faultsDA As New OleDbDataAdapter("Select * from faults WHERE jobReview = 0 ", db)
Dim faultsDS As DataSet = New DataSet()

faultsDA.Fill(faultsDS, "faults")

Dim faultsRow As System.Data.DataRow

Dim temp As String
For Each faultsRow In faultsDS.Tables("faults").Rows(0)
temp = faultsRow("currentStatus")
Label2.Text = temp
Next

End Sub

[funkyd77]

one thing:

this line

For Each faultsRow In faultsDS.Tables("faults").Rows(0)

should be like:

For Each faultsRow In faultsDS.Tables("faults").Rows

because Rows returns the collection of System.Data.DataRow's, whereas Rows(0) returns one specific row.

Next, as for .Next and .Previous methods, this is what the help says:

http://msdn.microsoft.com/library/de...onDataSets.asp

Record Position and Navigation in Datasets
Because a dataset is a fully disconnected container for data, datasets (unlike ADO recordsets) do not need or support the concept of a current record. Instead, all records in the dataset are available.

Because there is no current record, there is no specific property that points to a current record and there are no methods or properties for moving from one record to another. (In contrast, ADO recordsets support an absolute record position and methods to move from one record to the next.) You can access individual tables in the dataset as objects; each table exposes a collection of rows. You can treat this like any collection, accessing rows via the collection's index or using collection-specific statements in your programming language.

So...to do what you want to do you are best to use the Rows collection. Keep a variable that is the index of the current row you are looking at. Then when someone clicks the "Next" button or whatever, you want to do something like:

currentRow += 1
if currentRow < faultsDS.Tables("faults").Rows.Count then
    faultsRow = faultsDS.Tables("faults").Rows(currentRow)
    'Add the code here to populate your form with the data
    'from the current row, ie faultsRow
else
    'Set current row to the last row
    currentRow = faultsDS.Tables("faults").Rows.Count - 1
end if

notice that we make sure that we are not going past the end of the recordset.

hope this helps...

funky

[deano1]

Thanks Funky, that works great!

just out of interest, why is it so important not to go past end of dataset?

[funkyd77]

Hey, no probs.

just looking back at that code sample i wrote last night and it's not the best, basically the logic would be as follows:

1) User clicks next
2) you check if you can increment currentRow (by comparing it to faultsDS.Tables("faults").Rows.Count)
3) you display the details for the row at currentRow

translated to code (not that this is off the top of my head)

'See if we can increment the currentRow variable
if currentRow < faultsDS.Tables("faults").Rows.Count - 1 then
    'Note that the Rows collection is indexed from 0 to Count - 1
    'ok, we can increment the currentRow variable as we are not
    'at the end of the Rows collection yet
    currentRow += 1
else
    'Set current row to the last row
    currentRow = faultsDS.Tables("faults").Rows.Count - 1
end if
'Now we know currentRow is good and ready to go go go!
faultsRow = faultsDS.Tables("faults").Rows(currentRow)
'Add the code here to populate your form with the data
'from the current row, ie faultsRow

I think this is better code. Similar logic could be implemented for a click of the "Previous" button.

Now, because Rows is a collection, as i said above, it is indexed from 0 to Count - 1, meaning you can only pass values of "x" between 0 and Count - 1 when you go Rows(x). For example if there were 10 rows (Count = 10, so valid indexes are between 0 and 9) and you went Rows(10), Rows(11), Rows(12) etc etc you would get an error - ie your program would crash.

Hopw this helps,

funky

[deano1]

Hey Funky, thanks again.

Thing is i can't see how this could work for Previous button, using the count method. Maybe if you don't mind you could change the code so i can see how its done.

Thanks

[funkyd77]

Exactly, you wouldn't use the Count property.

If you think about what we were really doing in the example i put up before, we were making sure (with the If statement) that the index that we were using (currentRow) was valid, that is, that it was not greater than Count - 1.

Now, that was for the Next operation...the Previous operation is effectively navigating thru the rows of the table in the opposite direction.

So instead of making sure that currentRow is not greater than Count - 1, we want to make sure that currentRow is not less than 0 (remember that the index for the collection goes from 0 to Count - 1).

Also, instead of incrementing currentRow, we want to decrement it.

That should be enough i hope, have a tinker with the code you should be able to get it - the concept is that we are doing the same as for the Next operation, but instead of incrementing we are decrementing the currentRow, and instead of making sure it is < Count - 1 before incrementing, we want to make sure that is is > 0 before decrementing. Otherwise we set it to the index bound (0 this time instead of Count - 1).



funky