Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    Unanswered: Finding New AutoNumber Value on SQL Server after VBA AddNew

    Hi All

    I'm sure many of you have run into this one before. I'm amazed I haven't tbh

    I have just run a VBA routine that does your typical add record:

    Code:
    Dim rcsJOB as Recordset
    Set rcsJOB = ... etc, from JOB table, dboOpenDynaset, dbAppendOnly
    rcsJOB.AddNew
    rcsJOB!Field = This
    rcsJOB!Field = That
    A
    rcsJOB.Update
    B
    I can give you exact code if you like, but I don't think it's needed.

    The record is created no problem, but I want to go to this new record, or at least tell the user which job number was created, but it doesn't matter if I look at the JobID at point "A" or point "B", it is wrong. "A" gives me Null (assuming that's because SQL Server only gives autonumbers when a new record is actually saved, and "B" gives me the first JobID in the recordset. So now, the new record is essentially lost in the jobs.

    What I ended up doing was adding a "WHERE False" to the opening SQL and then after the Update command, I did a MoveFirst and then read the ID. But this seems terribly imprecise to me.

    How do you guys do it?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Hi ST

    Dim intNewID As Long

    Dim rcsJOB as Recordset
    Set rcsJOB = ... etc, from JOB table, dboOpenDynaset, dbAppendOnly
    rcsJOB.AddNew
    rcsJOB!Field = This
    rcsJOB!Field = That
    A
    rcsJOB.Update
    B

    intNewID = rcsJOB.YourAutonumberFiled

    This should give you your new automunmber
    Last edited by garethdart; 11-20-08 at 06:10.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The way I would do it is create an insert SPROC, call that and have it return the newly created IDENTITY value using T-SQL function SCOPE_IDENTITY(). I never, ever use recordsets for data modifications. I never, ever perform any DML from the client either.

    Using your current set up: you could try explicitly opening and committing a transaction - this might cause the recordset field to be populated.
    EDIT - skip the above - I suspect the UPDATE statement effectively does this - you could confirm if you can be bothered by monitoring Profiler.

    SCOPE_IDENTITY() is session scoped so you could perhaps call this function immediately after inserting the values using the same connection - in theory it should return the last generated IDENTITY for that session on the table, however I am not certain if using a recordset will b0rk things for you.

    HTH
    Last edited by pootle flump; 11-20-08 at 06:29.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If it's SQL Server 2005 (onwards) I'd use the OUTPUT clause over Scope_Identity(). It may be a tad more verbose but it is far more robust.
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    If it's SQL Server 2005 (onwards) I'd use the OUTPUT clause over Scope_Identity(). It may be a tad more verbose but it is far more robust.
    Hmmm. Might be an idea. You got a reference to back up that up? Or do you mean because it can be extended for use with sets of data? That would require an extensive rewrite too ya?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You pretty much hit the nail on the head: Scope_Identity() returns a single value only, whereas OUTPUT allows any number of return values, and other columns too!

    Not an extensive re-write at all, maybe 2 lines?
    It depends how much you want returned.
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would use the term "extensible" rather than "robust", predominantly to give me a reason to contradict you

    I mean rewrite of Startrekkers code. You can't do this using a recordset!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can a stored procedure not perform an insert and then return a recordset?
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ So suffice to say that the way I am doing it is about the only way to do it without going to extremes.

    Gareth, what you suggested, I tried both before and after the Update (at position A and position B in the code, but neither of them record the correct ID. I know that what you suggested works in native Access back-ends though.

    I have no problem with rewriting code, I'd like to learn exactly how this is done... the right way. I have, sadly, not had nearly enough exposure to SQL Server's abilities.

    If I could theorise here, a JOB table with JobID as the autonumber PKF and JobName as a text field... could you post some code showing the things mentioned?

    I have very limited understanding of all those things mentioned by you mods. SPROC, IDENTITY, T-SQL, SCOPE_IDENTITY(), DML, Profiler, OUTPUT. All of those terms I only have a basic understanding of and I want to change that fact.

    If nothing else, I at least have some stuff to go research Thanks!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Can a stored procedure not perform an insert and then return a recordset?
    Yes - but that is not what he has got. It is how I would do it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sproc = stored procedure.
    Simple T-SQL to do this:
    Code:
    USE test
    go
    
    IF NOT EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.mytable_insert')) BEGIN
        EXEC('
    CREATE PROCEDURE  dbo.mytable_insert
    AS
    BEGIN
        SELECT NULL
    END'
        )
    END
    GO
    
    ALTER PROCEDURE dbo.mytable_insert
    --WITH SCHEMABINDING
        (
              @this VARCHAR(10)
            , @that    INT
        )
    AS
    
        DECLARE   @l_error_number    AS INT
                , @l_error_message    AS NVARCHAR(2048)
    
        BEGIN TRY--Insertify data
          
            BEGIN TRAN--Insertify data
    
                INSERT INTO myTable (this, that)
                OUTPUT    myTable.myID
                SELECT      @this
                        , @that
    
            COMMIT TRAN--Insertify data
    
        END TRY--Insertify data
        BEGIN CATCH--Insertify data
    
            ROLLBACK TRANSACTION--Insertify data
    
            SELECT    @l_error_number    = ERROR_NUMBER()
                    , @l_error_message    = ERROR_MESSAGE()
    
            RAISERROR    (@l_error_message -- Message text.
                        , 16 -- Severity.
                        , 1) -- State.
    
        END CATCH--Insertify data
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Air code to call it (I'm not sure re populating the recordset - I haven't used OUTPUT before - normally I would use SCOPE_IDENTITY() and an output parameter):
    Code:
    ' ADO objects to load the data
            Dim cn As New ADODB.Connection
            Dim cmd As New ADODB.Command
            Dim prm As ADODB.Parameter
            Dim rst AS ADODB.Recordset
        
            cn.ConnectionString = CONNECTION_STRING
        
            cn.Open
        
            cmd.ActiveConnection = cn
    
        cmd.CommandText = "dbo.myTable_insert"
        cmd.CommandType = adCmdStoredProc
        cmd.NamedParameters = True
        cmd.CommandTimeout = 0
     
        Set prm = cmd.CreateParameter("@this", adVarChar, adParamInput, , This)
        cmd.Parameters.Append prm
     
        Set prm = cmd.CreateParameter("@that", asInteger, adParamInput, , That)
        cmd.Parameters.Append prm
        
        'Get & use the id
        rst = cmd.Execute
    
        rst.Close
        Set rst = Nothing
        Set cmd = Nothing
        Set prm = Nothing
     
        Set cmd = New ADODB.Command
    EDIT - corrected some errors.
    Last edited by pootle flump; 11-20-08 at 11:11.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm intrigued by your syntax flump
    Code:
    IF NOT EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.mytable_insert')) BEGIN
        EXEC('
    CREATE PROCEDURE  dbo.mytable_insert
    AS
    BEGIN
        SELECT NULL
    END'
        )
    END
    GO
    Never come across this one before!
    Is there any reason why you don't drop (if exists) and create?
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The reason is to maintain the original created date meta data. Sometimes this is useful. Rarely, mind.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh, fair point!
    I must just be lazy - never found the need before.
    George
    Home | Blog

Posting Permissions

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