Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    89

    Unanswered: Return Identity after Insert - ALMOST there

    Hi. I'm using Access 2007 with a MS SQL server backend. I've got a Stored procedure that inserts a record into a table. All I want is once inserted, I want to return the new ID to access.

    my VBA at the moment@
    Code:
     Dim qdf As QueryDef
        Dim db As Database
        Dim rs As Recordset
                ' Set database variable to current database.
                Set db = CurrentDb
     
                'Open QueryDef object.
                Set qdf = db.QueryDefs("Temp_TenancyInsert")
            
                qdf.sql = "EXEC dbo.sp_TenancyInsert " _
                        & "@TenantID = " & Me.txtTenantID & ",@LettingID = " & Me.txtLettingID & ",@Startdate = '" & Format(Me.txtContractStartDate, "yyyy-mm-dd") & "',  @GroundRent = " & Me.chkGroundRents & ""
                        
                qdf.ReturnsRecords = False
                qdf.Execute
                
                Set rs = db.OpenRecordset("SELECT @@IDENTITY")
                If Not rs.EOF Then
                    MsgBox rs.Fields(0)
                Else
                    MsgBox "No AutoNumber generated."
                End If
                
                rs.Close
                
                qdf.Close
                db.Close
                Set db = Nothing
    This inserts the field, but the message box always returns 0, so something's not right. I then tried adding @Identity INT OUTPUT in the sproc with SET @Identity = SCOPE_IDENTITY() but this gives error 1346. I'm so close, any help is appreciated.

    Here's my sproc
    Code:
    ALTER PROCEDURE [dbo].[sp_TenancyInsert]
    	-- Add the parameters for the stored procedure here
    	(@LettingID int = null, @TenantID int = null, @Rent money = null, 	etc, @GroundRent bit=null,   @IDENTITY INT OUTPUT)
    	
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        INSERT INTO [Atlas].[dbo].[Tenancies]
               ([LettingID]    ,[TenantID]   ,[Rent], etc ,[GroundRent]
                )
         VALUES
               (@LettingID , @TenantID , @Rent , etc, @GroundRent)
        SET @Identity = SCOPE_IDENTITY() 
    END
    Last edited by rudeboymcc; 11-30-09 at 07:26.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    would you be better off asking this question in the SQL server forum?
    if so do you want it moving there?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ALTER PROCEDURE dbo.DO_NOT_PREFIX_YOUR_SPROCS_WITH_SP_sp_TenancyInsert (
       @LettingID  int   = NULL
     , @TenantID   int   = NULL
     , @Rent       money = NULL
     , etc
     , @GroundRent bit   = NULL
    )
    AS
      BEGIN
    	  SET NOCOUNT ON
    
        INSERT INTO Atlas.dbo.Tenancies (LettingID, TenantID, Rent, etc, GroundRent)
          OUTPUT inserted.your_identity_fields_name
          VALUES (@LettingID, @TenandID, @Rent, etc, @GroundRent)
      END
    GO
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2009
    Posts
    89
    yeah sure, wasn't sure which to put it in because I'm using both and don't know which code is wrong

  5. #5
    Join Date
    Jun 2009
    Posts
    89
    @ gvee, I changed the code to this:
    Code:
    ALTER PROCEDURE [dbo].[sp_TenancyInsert]
    	-- Add the parameters for the stored procedure here
    	(@LettingID int = null, @TenantID int = null, @Rent money = null, etc,GroundRent bit=null)
    	
    AS
    BEGIN
    
    	SET NOCOUNT ON;
    
        INSERT INTO [Atlas].[dbo].[Tenancies]
               ([LettingID]   ,[TenantID]   ,[Rent]       etc       ,[GroundRent]        )
         OUTPUT inserted.ID
         VALUES
               (@LettingID , @TenantID , @Rent , etc, @GroundRent)
    
    END
    Still getting "0" as the ID returned. Do I not need to add some output in the Sproc? also, what's wrong with adding sp in front of sprocs?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you run the sproc directly against SQL Server (i.e. not through Access) to see what is returned?
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is an example. The procedure returns a recordet with 2 columns (Status and ReturnValue). If it succeeds, Status = -1 (True) and ReturnValue = Primary key of the new created row.
    if it fails, Status = 0 (False) and ReturnValue = SQL error code.
    Code:
    USE [Sales]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ---------------------------------------------------------------
    -- Database:    Sales
    -- Objet:       Procedure [dbo].[Proc_Tbl_Credentials_Insert]
    -- Author:      René FRANÇOIS
    -- Date:        2009-11-02 12:05
    ---------------------------------------------------------------
    CREATE PROCEDURE [dbo].[Proc_Tbl_Credentials_Insert]
    (
        @FK_Tbl_Users int,
        @Credential_Feature nvarchar(128),
        @User_Credentials int
    )
    AS
    BEGIN
        DECLARE @ReturnValue INT
        DECLARE @Status INT
        BEGIN TRY
            INSERT INTO [Tbl_Credentials]
            (
               FK_Tbl_Users,
               Credential_Feature,
               User_Credentials
            )
            SELECT  
                @FK_Tbl_Users,
                @Credential_Feature, 
                @User_Credentials
            SET @ReturnValue = @@IDENTITY
            SET @Status = -1
        END TRY
        BEGIN CATCH
            SET @ReturnValue = @@ERROR
            SET @Status = 0
        END CATCH
        SELECT @ReturnValue AS ReturnValue, @Status AS [Status]
        RETURN @Status
    END
    Have a nice day!

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A SQL Server trigger keeps popping into my head (for some odd reason) but I don't want to steer you wrong. I usually create the record in vba using code such as:

    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    dim NewID as variant
    strSQL = "Select * from MyTable"
    rs.open strSQL,currentproject.connection,adopendynamic,adl ockoptimistic
    rs.addnew
    rs!SomeField = somevariable
    rs.update
    NewID = rs!IDField
    rs.close
    set rs = nothing

    and then I use the NewID variable in my other coding.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jun 2009
    Posts
    89
    If I run the query using Sinndho's code in SSMS, I get the values returned (the "ReturnValue" and the "Status").



    I have set "Returns records" to false, as if I enable this then it says that it can't use a select query.

    Question now is how to get them in Access through a SQL pass through query??

    @pkstormy, I have no idea how your code works, and haven't used ADODB before so not very comfortable with it.
    Last edited by rudeboymcc; 12-01-09 at 07:29.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just be aware that you should not be using @@Identity.

    At the very least use Scope_Identity(), but better still use the OUTPUT clause.


    @rudeboymcc Have you run my code in SSMS? Have you ever calle da sproc from within Access that returns a recordset? That's all my code needs; one database call that does the insert as well as return the recordset.
    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
  •