How to go to the last row of a Table?

D

Disasterpiece

Well-known member
Joined
Apr 2, 2003
Messages
47
Location
Blacksburg, VA
Hi again its me,

My teacher is horrible in that she has barely touched on DB syntax yet she wants us to do all of this crazy advanced stuff. Anyway, what is the syntax for retrieving a value thats in the last row of a DB.

What I need to do is go to the last row of a Transactions table and retrieve the value of the Ending balance field for a specific user.

I think my sql string would be something like "SELECT * FROM Transactions WHERE Username=" & strUsername

That would allow me to get the rows for only the username entered. Is it possible to then go to the last row entered by that user assuming an autonumber as the primary key, so I can then retrieve the ending balance?

I will need the ending balance when the form loads so I can output it to a label.

Thanks
Disasterpiece
 
Did she mention where/how you have to find it?

If you return ALL rows, you can use the Rows.Count property of the DataTable to get to the last row. Make sure you sort first:
C#: 
// Assume you added "ORDER BY ID" to your SELECT statement (ascending)
DataRow row = ds.Tables["Table1"].Rows[ds.Tables["Table1"].Rows.Count-1];
// Grab the value:
decimal endingBalance = (decimal)row["EndingBalance"];

You can also use:
C#: 
// Does NOT assume you added "ORDER BY" to your SELECT statement
// Sort by column "ID" descending
DataRow[] rows = ds.Tables["Table1"].Select(string.Empty, "ID desc");

// Grab the first row - sorted descending works nice
DataRow row = rows[0];

// Grab the value:
decimal endingBalance = (decimal)row["EndingBalance"];

Or, you can write the SQL to just grab the last row:
Code: 
"SELECT TOP 1 EndingBalance FROM Transactions WHERE Username=" & strUsername & " ORDER BY ID desc"

The ORDER BY will sort in descending order. The "TOP 1" returns only one row. Together, theyll give you the last record in the table.

-Ner
 
Well I dont just need the last row, I need all rows that go with a certain username. And that means that all rows for a username wont necessarily have IDs that are in order. So what if I do a SQL string that just gets the rows for a particular username, and then sort it by Date, would that work do you think?

Im having trouble following your C# code as Ive never been exposed to it before. It seems almost like VB but then on a second look Im lost *lol*
 
Hey dp,
We know you know how to create and fill a dataset.
Been there, done that.

Use the sql you mentioned above and your transaction table as your datasource to create a new dataset.
The dataset will include all the transactions from that user.
You can sort by ID if the IDs are added sequentially.
Then, like Ner said, get the row count and go to the last row added.
Get the item of interest from that row and you should be done.

Jon
 
Back
Top