dcsimg
Results 1 to 5 of 5

Thread: help finding last Item saved n MS SQL DB

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    307

    help finding last Item saved n MS SQL DB

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

    Code:
    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

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,016

    Re: help finding last Item saved n MS SQL DB

    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.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,793

    Re: help finding last Item saved n MS SQL DB

    Never mind that he has the wrong Sorting Order.
    The question is more: Does he use IDENTITY in his Table?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,295

    Re: help finding last Item saved n MS SQL DB

    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
    Code:
    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,241

    Re: help finding last Item saved n MS SQL DB

    Quote Originally Posted by Alfarata View Post
    I need to find the autofill # named WorkOrderID in table Workorders

    Code:
    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:
    Code:
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width