[2008] SQL CMD get last row in a column

[Icyculyr]

I want to get the last row in my colum 'ID'

such as:
Dim sqlCommand As New SqlCommand("SELECT LASTROW of ID FROM Table4")

however, LASTROW, or LAST is not exactly a valid command, basically I want to count how many entries have been added, since ID has AutoIncrement, then the value of the last row, will be the correct amount.

Can anyone tell me how to do this?

Thanks

[Hack]

How about "SELECT MAX(ID) FROM blah blah"

[killer7k]

OR

SELECT Top 1 *  FROM YourTable order by ID desc

[jmcilhinney]

That's actually the not the best approach. In some cases it will give the correct result but in others it will not. If you delete a row or roll back a transaction at any point then it will give you an incorrect result. Even if you aren't deleting any rows or using any transactions you should still do it the "correct" way from the start. If you want to know how many rows there are in a table then you should count them:

SELECT COUNT(*) FROM MyTable

[Icyculyr]

Perfect, thanks

Edit:
Thanks for the correct jmcilhinney.

[GaryMazzone]

If the ID is a indentity column then the count(*) is definitely not the correct way to go. The only truly valid reason to use count is to see how many rows are there or the number of rows that meet a specified condition. If you want the ID of the last inserted row and the ID column is an Identity you should issued the Select Scope_Identity() as a second command in the insert statement

[szlamany]

So many different answers to such a vague question.

Gary might have picked up on the real reason it's being asked - but again - the OP should be a lot more specific so that the proper response can be given.

What do you need this "last" value for? When are you trying to get it - right after you do an INSERT? Is this a multi-user application?

And of course - what is your backend DATABASE??

[Icyculyr]

when I retrieve the values, I need to know how many there are, so I don't go past the amount there are..

For example, a list of strings, (40) to be precise, you click the "R" button, and it shows you the next string, so if you click R 41 times, then problems occur, I want to be able to count the rows, to see how far I am going, or get the last value in my ID column, whichever is most suitable.

Thanks

[szlamany]

Well - with that clarification then I don't believe that any of the solutions fit properly.

What is the code you are using to move "R" - and where do your records reside - what kind of UI control are you using?

[Icyculyr]

R is a button, and you click it.

Records are in a database, in Table4, in Column textValueCol,

what kind of UI? a label, that shows the string lol.

Cheers

[jmcilhinney]

It seems to me that this doesn't warrant a query at all. Get all your data into a DataTable and bind it to your Label via a BindingSource. You then call the MoveNext method of the BindingSource to advance to the next record until Position is equal to (Count-1).

[Icyculyr]

I see, so a Table in a database can't do that, but the data from a datatable can, What is a binding source? and what does that do?

Thanks

[jmcilhinney]

What did MSDN tell you when you looked there?

[Icyculyr]

It told me ask jmcilhinney because he knows more.

I'll go check MSDN now.

[szlamany]

I see, so a Table in a database can't do that, but the data from a datatable can, What is a binding source? and what does that do?

Thanks

You visit the database to get data. You limit the visits.

It's always best to grab data from the DB (that's the server) and bring it to the client for processing.

[Icyculyr]

Ok, so you store the data in the DataSet?, and access the DataSet correct?

Cheers