help finding last Item saved n MS SQL DB

[Alfarata]

I need to find the autofill # named WorkOrderID in table Workorders

SQL.ExecQuery(" SELECT * " &
                      "FROM WorkOrders " &
                     " ORDER BY WorkOrderID " &
                     " TOP 1;")

I tried the command limit but its not working with MS SQL,
I only want the last record entered

Thanks

[jmcilhinney]

I just searched the web for "sql server select top" and the third result was the official documentation for the TOP statement.

https://docs.microsoft.com/en-us/sql...ql-server-2017

That took me a couple of seconds and then not much more time to read the examples provided there. None of them look like your code.

[Zvoni]

Never mind that he has the wrong Sorting Order.
The question is more: Does he use IDENTITY in his Table?

[GaryMazzone]

First: Why are you asking for all the row data if you only need the ID of the last thing inserted into the table? You should only be asking for the thing you need to be returned.
Next: How is the record inserted? Is it by Stored Procedure? In-line code? If by Stored Procedure and the column is an IDENTITY then add a variable to hold the result of Scope_Identity() to be run right after the insert and return that from the proc

Declare @var1 INT 
SELECT @var1 = SCOPE_IDENTITY()

You can add that to you inline code also and get a return if you did it that way.
You can also get the MAX(WorkOrderId) from the table if you want. This of course might not be your insert since someone else could have inserted a record there after you did if more that one person can use the system

[techgnome]

I need to find the autofill # named WorkOrderID in table Workorders

SQL.ExecQuery(" SELECT * " &
                      "FROM WorkOrders " &
                     " ORDER BY WorkOrderID " &
                     " TOP 1;")

I tried the command limit but its not working with MS SQL,
I only want the last record entered

Thanks

Well, for starters that'sbecaue the SQL is wrong... the TOP goes with the select:

SELECT TOP 1 * from WorkOrders ORDER BY WorkOrderID Desc

that would have woked... it also would have needlessly selected everything in the row when all you want it the id...

That said, there is no such thing as MSSQL ... MS is Microsft, which is a company, and SQL is an acronym for Structured Query Language. Microsoft produces multiple database systems. Access is one. SQL Server is another. FoxPro is yet another (albiet a dying one). Usually people mean SQL Server when they use MS SQL, but there really isn't a such thing.


-tg