How do I get the last row in a datatable?

Hi,
I'm new to using datatables...working great for my needs so far...but I have an issue I wonder if there's a quick answer to.

I have a function that returns a datatable.  The datatable is basically all records for a *single* userID.

I'd like to create another function that uses my first function to loop through all the userID's and then puts the *last* record into a new datatable, then return that datatable.

Example:
function1 accepts a userID and date then returns a datatable something like this:
UserID     Month     Year  ....otherStuff
1             1            2005
1             2            2005
1             3            2005

function2 I would like to accept only the date and then loop through a dataset of userID's and get me only the last row of each function1 call.
Example:
UserID     Month     Year  ....otherStuff
1             3            2005
2             3            2005
3             3            2005









LVL 37
samtran0331Asked:
Who is Participating?
 
b1xml2Connect With a Mentor Commented:
try this:

'userTable = the table that has the singular user's information
'usersTable holds information of all users
Sub AddUserRow(ByVal userTable As DataTable,ByVal usersTable As DataTable)
'creating a view that sorts the data by year and then month
Dim view As New DataView(userTable,String.Empty,"Year,Month",DataViewRowState.CurrentRows)
If view.Count > 0 Then
   usersTable.Rows.Add(view(view.Count - 1).Row.ItemArray)
End If
End Sub
0
 
b1xml2Commented:
Function GetNewTable(ByVal table As DataTable) As DataTable
Dim view As New DataView(table,String.Empty,"Year,Month",DataRowViewState.CurrentRows)
Dim newTable As DataTable = table.Clone()
If view.Count > 0 Then
   newTable.Rows.Add(view(view.Count - 1).Row.ItemArray)
End If
Return newTable
End Function
0
 
samtran0331Author Commented:
b1xml2,

Thanks for the speedy response.
Not sure I understand the logic ...guess that's why I'm here.  =0)

VS is telling me *DataRowViewState* is undeclared?

Will read up on DataView in mean time...but just to make sure my goal is clear....
I have function1(userID,date)
Which returns:

UserID     Month     Year  ....otherStuff
1             1            2005
1             2            2005
1             3            2005 <---- Row I need in new datatable

The "other stuff" is a bunch of "cascading" calcs (next row depends on data from previous row).
So the last record was built upon data from all the records above it.

So if I run function1 again for a different userID, I'd get:
UserID     Month     Year  ....otherStuff
4             1            2005
4             2            2005
4             3            2005 <---- Row I need in new datatable

then again...
UserID     Month     Year  ....otherStuff
9             1            2005
9             2            2005
9             3            2005  <---- Row I need in new datatable

etc...

What I had in mind was...
function2()
'Get dataset of valid userID's from user table
'loop through the userID dataset and
'for each userID, call Function1
    'get the last row of datatable from function1
    'put it into datatable2
'next
'return datatable2
end function

Is this inefficient?





0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
b1xml2Commented:
again
====

Function GetNewTable(ByVal table As DataTable) As DataTable
Dim view As New DataView(table,String.Empty,"Year,Month",DataViewRowState.CurrentRows)
Dim newTable As DataTable = table.Clone()
If view.Count > 0 Then
   newTable.Rows.Add(view(view.Count - 1).Row.ItemArray)
End If
Return newTable
End Function
0
 
samtran0331Author Commented:
Sorry for my ignorance b1xml2, In the 2nd line of your function, the "DataViewRowState.CurrentRows",
VS is telling me it is undeclared...?
0
 
b1xml2Commented:
dont copy and paste directly into code behind.
copy and paste into notepad and then into VS.

DataViewRowState is an enum list
CurrentRows is a valid item

0
 
samtran0331Author Commented:
Hi again b1xml2,

Forgot the web to notepad to vs "copy path".

I tried calling your function from a page like this:

        Dim testTable As DataTable = New DataTable
        testTable = GetNewTable(function1(startdate, strUserID))

My function1 works fine when called from the page...but when nested in your function, I get a syntax error from my function1...

also, again...forgive my ignorance...but I still don't quite follow your function logic.....it is getting and should return the last row of my datatable and returning this one row in a new datatable right?

So I still need to loop from the page to get my function to get each userID and then to add each row to your datatable?


0
 
b1xml2Commented:
what is does is just pass a datatable to that function.
we create a view of that data table indicating that the data is to be sorted in ascending order by year and then month.
we then pick the last record
we make a clone of the table sent in and load the last row values into this new table
we then send it as the returned value/

Isnt this more or less what you wanted?
0
 
samtran0331Author Commented:
ummm...yes....but I need to keep adding to the new table that is passed out from your function...
I don't know datatables that well...and the cloning is new to me....it clones the "structure" then?

Mebbe instead of your code being in a function...I should have it on the page?

on the page:
dim a datatable
your code to get that last row and put it into the datatable
then loop through userID's with your code and add each userID's last row into the table?

doesn't your brain hurt getting so many points helping dummies like myself??? =0)
0
 
b1xml2Commented:
there are no dumb questions.... only dumb answers...

it is good to ask questions.
0
 
samtran0331Author Commented:
b1xml2,
I've got to work on some other stuff for a bit but will try your code as soon as I get a chance!
Even though I haven't got it working...the code you've provided at least shows me some of the stuff I can do with datatable and dataviews.
Thanks bro!
0
 
b1xml2Commented:
no probs!!!
0
 
samtran0331Author Commented:
AWESOME!

Thanks b1xml2, for helping with this specific issue, but also for opening some new doors with working with datatables and dataviews!!!
0
 
samtran0331Author Commented:
This question is closed...but b1xml2....I had an opinion question to ask you...

I work with md arrays a lot...but after what you showed me with datatables today....do you think that datatable can handle everything an md array does?

My main reason for using arrays is that a lot of the calculation programming I do is spreadsheet-like calcs...where in one row, there is math between the fields and then the next row is dependent on data in the previous row...etc....

so I do a lot of   array(i,i+1) kinda stuff...so I'm mimicking a datatable with an array...
are there performance differences that you know of?

If you think it's worth while, I'll start a new question with points to get more opinions.
0
 
b1xml2Commented:
there would be but with today;s computing, you;d be better off using DataTables
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.