ADO way to capture IDENTITY value with OUTPUT in an INSERT SQL (1 Viewer)

mdlueck · Aug 30, 2011

Today I learned that there is a way to obtain the auto generated ID when a record is INSERTed into SQL Server. The following code executes in the SQL Server Management Studio:

Code:

INSERT INTO dbo.auth (authid, logtimestamp, active, userid, username, permmask)
  OUTPUT Inserted.id
  VALUES (1, CURRENT_TIMESTAMP, 1, 'foo', 'Foo User', 31);

Which has output of:

Code:

id
12

I am finding many examples of how to use ADODB.Parameter objects with stored procedures, however I am not finding an example of how to use OUTPUT with an INSERT statement.

I have seen examples that show accessing the ADODB.Command object to deal with returned values from Stored Procedires. One had to name the Parameter(s) within the Stored Procedure, and that is the name you access them via after you execute the Stored Procedure.

However one does not define special names for the OUTPUT command of a SQL INSERT statement. I have tried the column name with and without leading @ character. Neither of those attempts worked.

So what is the correct Rx to using OUTPUT Paramaters with non Stored Procedure SQL code? Thanks!

mdlueck · Aug 30, 2011

This does not sound very encouraging...

"Retrieving Identity or Autonumber Values (ADO.NET)"
http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx

Some database engines, such as the Microsoft Access Jet database engine, do not support output parameters and cannot process multiple statements in a single batch. When working with the Jet database engine, you can retrieve the new AutoNumber value generated for an inserted row by executing a separate SELECT command in an event handler for the RowUpdated event of the DataAdapter.

So does this mean that INSERT OUTPUT type SQL is not possible with VBA ADO objects? This KB article was about .Net, which Access VBA is NOT .Net.

Banana · Aug 30, 2011

mdlueck- the article is talking about using ADO.NET against different backend, of which Jet may be one of the backends. The disclaimer is correct insofar talking about getting output _from_ the Jet database, as opposed from _from_ SQL Server. In your case, you are using ADO to obtain output from SQL Server.

That said, I never fiddled with OUTPUT, at least not in the sense of returning something after doing an operation but rather in sense of passing in a output parameter. One thought I have is to write an ADO command, set its Type to text, and write SQL for the INSERT INTO with OUTPUT, then use Parameters to see if it gives you any hint as to what it expects the parameter to be.

Aircode:

Code:

cmd.CommandType = adCmdText
cmd.CommandText = "INSERT INTO ... OUTPUT ... VALUES ...;"
cmd.Parameters.Refresh
For Each p In cmd.Parameters
  Debug.Print p.Name, p.Type, p.Direction, p.Value
Next
cmd.Execute
For Each p In cmd.Parameters
  Debug.Print p.Name, p.Type, p.Direction, p.Value
Next

I can't say whether this'll work since this may not be a conventional output parameter the ADO is expected but it's worth a shot.

Next thing I'd try if that was not a parameter is to open a recordset and see if it has the output values:

Code:

Set rs = cmd.Execute
Do Unitl rs.EOF
  For Each f In rs.Fields
     Debug.Print f.Name & ": " & f.Value
  Next
Loop

See if either get you closer to the solution.

mdlueck · Aug 31, 2011

Thanks for the suggestions, Banana, I will try them.

I realized I never posted the code I am working with. Here it is as-is... in other words NOT WORKING YET.

Code:

  'Define attachment to database table specifics
  Set adoCMD = New ADODB.Command
  adoCMD.ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()

  'Translate the active field from boolean to a 1/0
  If Me.active = True Then
    intTmpactive = 1
  Else
    intTmpactive = 0
  End If

  'Define a query to INSERT a record into the BE DB auth table
  strSQL = "INSERT INTO dbo.auth (authid,logtimestamp,active,userid,username,permmask) " & _
            "OUTPUT INSERTED.id " & _
            "VALUES (" & Me.authid & ",CURRENT_TIMESTAMP," & intTmpactive & ",'" & Me.userid & "','" & Me.username & "'," & Me.permmask & ");"

  'Pass the SQL to the Command object
  adoCMD.CommandText = strSQL

  'Specify the OUTPUT Param
  Set adoPRMid = adoCMD.CreateParameter("id", adInteger, adParamOutput)
  adoCMD.Parameters.Append adoPRMid

  'Execute the SQL
  adoCMD.Execute
  
  'Retrieve the ID the new record is stored at
  Me.id = adoCMD.Parameters("id")

  'Define a query to refresh the temp table with the record which was inserted in the BE DB
  strSQL = "INSERT INTO tmptblqry_auth (id,authid,logtimestamp,active,userid,username,permmask) " & _
            "SELECT a.id, a.authid, a.logtimestamp, a.active, a.userid, a.username, a.permmask " & _
            "FROM dbo_auth AS a " & _
            "WHERE (((id)='" & Me.id & "'));"

  'Call the shared API and return its return code
  Insert = dbutils_RefreshLocalTmpTbl(strSQL)

mdlueck · Aug 31, 2011

I added the debugging you suggested as follows:

Code:

For Each p In adoCMD.Parameters
  Debug.Print p.Name, p.Type, p.Direction, p.Value
Next

  'Execute the SQL
  adoCMD.Execute
 
For Each p In adoCMD.Parameters
  Debug.Print p.Name, p.Type, p.Direction, p.Value
Next

  'Retrieve the ID the new record is stored at
  Me.id = adoCMD.Parameters("id")

The output in the Immediate window is as follows:

Code:

id             3             2            
id             3             2

And hovering my mouse over attribute Me.id shows that it has a "0" as its value.

mdlueck · Aug 31, 2011

Aaaahhh!!! Your second suggestion seems to be the solution! Indeed the adoCMD.Executes returns an adoRS object.

Code:

  Set adoRS = adoCMD.Execute
  Do Until adoRS.EOF
    For Each f In adoRS.Fields
      Debug.Print f.Name & ": " & f.Value
    Next
  Loop

Immediate window output:

Code:

id: 20
id: 20
id: 20
id: 20

The code gets stuck looping... EOF never rings true.

Anyway, I think I know how to fix up my code in this case.

Thank you!!!

mdlueck · Aug 31, 2011

Success at last!!!

My working code:

Code:

Public Function Insert() As Boolean
On Error GoTo Err_Insert

  Dim adoCMD As ADODB.Command
  Dim adoPRMid As ADODB.Parameter
  Dim adoRS As ADODB.Recordset
  Dim strSQL As String
  Dim intTmpactive As Integer

  'Define attachment to database table specifics
  Set adoCMD = New ADODB.Command
  adoCMD.ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()

  'Translate the active field from boolean to a 1/0
  If Me.active = True Then
    intTmpactive = 1
  Else
    intTmpactive = 0
  End If

  'Define a query to INSERT a record into the BE DB auth table
  strSQL = "INSERT INTO dbo.auth (authid,logtimestamp,active,userid,username,permmask) " & _
            "OUTPUT INSERTED.id " & _
            "VALUES (" & Me.authid & ",CURRENT_TIMESTAMP," & intTmpactive & ",'" & Me.userid & "','" & Me.username & "'," & Me.permmask & ");"

  'Pass the SQL to the Command object
  adoCMD.CommandText = strSQL

  'Specify the OUTPUT Param
  Set adoPRMid = adoCMD.CreateParameter("id", adInteger, adParamOutput)
  adoCMD.Parameters.Append adoPRMid

  'Execute the SQL
  Set adoRS = adoCMD.Execute
  
  'Retrieve the ID the new record is stored at
  Me.id = Nz(adoRS!id, 0)

  'Define a query to refresh the temp table with the record which was inserted in the BE DB
  strSQL = "INSERT INTO tmptblqry_auth (id,authid,logtimestamp,active,userid,username,permmask) " & _
            "SELECT a.id, a.authid, a.logtimestamp, a.active, a.userid, a.username, a.permmask " & _
            "FROM dbo_auth AS a " & _
            "WHERE (((id)=" & Me.id & "));"

  'Call the shared API and return its return code
  Insert = dbutils_RefreshLocalTmpTbl(strSQL)

Exit_Insert:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoPRMid = Nothing
  Set adoRS = Nothing

  Exit Function

Err_Insert:
  Call errorhandler_MsgBox("Class: clsObjAuthTbl, Function: Insert()")
  Insert = False
  Resume Exit_Insert

End Function

Banana · Aug 31, 2011

mdlueck -

I'm happy you got it worked out.

Sorry about the infinite loop - that's because it's missing a MoveNext in the loop which is why you got same id again and again.

However, I have to point out that you don't need Parameter at all for your code. You're getting it from your cmd's recordset which isn't a parameter at all, so you can safely chuck it out. Though it happen to use same keyword "OUTPUT" that's used in stored procedures' parameters, your working code proves that it's not a parameter at all but something more analogous to:

"INSERT INTO ....; SELECT * FROM ...;"

and thus, it returns as a recordset instead of a parameter.

EDIT: I should say I'm curious about id being returned as a parameter - you didn't add it yourself but used Refresh, correct? If it returned 0 still, then it wouldn't have worked but seems odd that it'd return a parameter correctly but not supply the value.

mdlueck · Aug 31, 2011

You're getting it from your cmd's recordset which isn't a parameter at all, so you can safely chuck it out.

Confirmed. I commented out the parameter LOCs and the code still works properly.

hhhmmm....

I had been having a fight trying to leverage my apparent parameter success and provide two inputs to the INSERT query via parameter (Host Variable). The code is fighting me tooth and nail. So, affirmation that it was not output parameter success I was having.

Do you have a suggestion of how to be able to provide this query with the values for Me.userid and Me.username via parameters (Host Variables)?

This is the first time I have seen "Host Variable" support in a RDBMS other than DB2, so I think that is slick-O!

mdlueck · Sep 1, 2011

@Banana: Suggestions how to be able to use adParamInput variables to supply data for an INSERT / UPDATE statement? Thanks!

Banana · Sep 1, 2011

Disclaimer: I've not worked on DB/2 before but "host variables" sounds very similar to what is used in MySQL client. You're talking about something like this:

Code:

DECLARE @myVariable INT;

SET @myVariable = 1;

INSERT INTO aTable (aNumber) VALUES (@myVariables);

The above is not possible via ADO. In specific case of MySQL, host variable is only available within the client. Ditto for SQL Server's SSMS.

What you'd do is either create a stored procedure or function. SQL Server example:

Code:

CREATE PROCEDURE InsertNumber (@myNumber INT) AS
INSERT INTO dbo.aTable (aNumber)
VALUES @myNumber;

In VBA:

Code:

With cmd
  .CommandType = acCmdStoredProc
  .CommandText = "InsertNumber"
  Set p = .CreateParameter("@myNumber", adInteger, adParamInput, , 1)
 .Parameters.Append p
 .Execute
End With

(untested aircode)

If you want to use OUTPUT (as part of INSERT INTO, not a parameter), then I think the same process you used above should work, but once again, I've never tested/done it so you may need to play with cmd.Parameters.Refresh to validate how ADO expect you to name your parameters and how it should be defined. (Note that in production, you definitely don't want to do a Parameters.Refresh - that's expensive operation; better that you create & append but Refresh is very useful during development when you need to know how you need to create the parameter.)

When you have a stored procedure with an OUTPUT parameter, you use a parameter and pass in adParamInputOutput (never use adParamOutput; it just doesn't work correctly, at least not with SQL Server).

When you use a stored function, use the @RETURN_VALUE & adParamReturnValue to get the function's result. I've done this with scalar valued functions but not with table valued functions (I'm pretty sure this is not supported in ADO but I could wrong).

HTH.

mdlueck · Sep 1, 2011

Greetings Banana,

What you'd do is either create a stored procedure or function.

That is unfortunate.

When I started seeing SQL containing ? place holders I got excited as that is the syntax to use host variables with DB2. Instead of needing to send in all of the data in the SQL statement, host variables map to variables in the client program memory space, the values are morphed out of the client app and sent to the database, and the ? char is the special char to indicate where the host variables go.

They are very useful to prevent quote characters from making trouble, reducing BY FAR the SQL string length, etc...

Thank you for following up.

Banana · Sep 1, 2011

Oh, wait, you're talking about something different.

In MySQL that's "prepared statements" --

Code:

PREPARE myStmt AS INSERT INTO aTable (aNumber) VALUES ?;

EXECUTE myStmt 1;

I don't know if SQL Server has an analogous concept (I do think they do - it just occurred to me that I've never bothered trying to and do this with SQL Server - may have to find out if it's available.)

The other thing is that in case of MySQL's prepared statement, it's only good as long as the connection is opened. If you lose the connection, all prepared statements are gone. So even if ADO did let you prepare statements, you still have to execute all those preparations beforehand, that I would be asking why not just create a stored procedure ?

HTH.

EDIT: Also, note that in case of prepared statements (at least for MySQL again) you're passing in a SQL string, not a true parameterized statement. This is thus no different from doing something like this in VBA:

Code:

strSQL = "INSERT INTO aTable (aNumber) VALUES (" & myNumber & ");"

Well, to be fair, though, that's not exactly the same thing since the prepared statement does parsing only once while string manipulation in VBA would be re-parsed for every execution.

mdlueck · Sep 1, 2011

Banana said:
why not just create a stored procedure ?

Baby steps... I had not gotten the courage up to ingest a SQL UPDATE into a stored proc with SQL Server yet.

With the bad behavior of "How to detect when an UPDATE fails when using ADODB.Command Execute" --> http://www.access-programmers.co.uk/forums/showthread.php?t=214990 I might be inclided to go the extra miles and use stored procs.

hhhmmm....

mdlueck · Sep 8, 2011

Banana said:
why not just create a stored procedure ?

Well I took the time today to ingest one query into a Stored Procedure. It transitioned very smoothly. One benefit for doing so is being able to pass in args via Parameter attributes. Following is a brief Rx for others to leverage:

Access 2007 VBA code:

Code:

Public Function Insert() As Boolean
On Error GoTo Err_Insert

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset
  Dim strSQL As String

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjProjectsTbl_Insert"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@authid").Value = Me.authid
    .Parameters("@title").Value = Me.title
    Set adoRS = .Execute()
  End With

  'Retrieve the ID the new record is stored at
  Me.id = Nz(adoRS!id, 0)

  'Define a query to refresh the temp table with the record which was inserted in the BE DB
  strSQL = "INSERT INTO tmptblqry_projects (id,authid,logtimestamp,title) " & _
            "SELECT p.id, p.authid, p.logtimestamp, p.title " & _
            "FROM dbo_projects AS p " & _
            "WHERE (((id)=" & Me.id & "));"

  'Call the shared API and return its return code
  Insert = dbutils_RefreshLocalTmpTbl(strSQL)

Exit_Insert:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

Err_Insert:
  Call errorhandler_MsgBox("Class: clsObjProjectsTbl, Function: Insert()")
  Insert = False
  Resume Exit_Insert

End Function

SQL Server 2008 R2 Stored Procedure code, script which gets executed in SQL Server Management Studio in order to install the SP. Note: To run this script in the Management Studio you must click menu option "Query \ SQLCMD Mode" to allow the :setvar variable to work as needed.

Code:

-- Define name of STORED PROCEDURE for this script
:setvar SPNAME clsObjProjectsTbl_Insert
USE [Fandango]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Check if the STORED PROCEDURE exists already, DROP if found
IF EXISTS(SELECT * FROM sys.objects WHERE type_desc = 'SQL_STORED_PROCEDURE' AND name='$(SPNAME)')
  DROP PROCEDURE [dbo].[$(SPNAME)]
GO
CREATE PROCEDURE [dbo].[$(SPNAME)] (
  -- Add the parameters for the stored procedure here
  @authid AS smallint,
  @title AS varchar(100)
  )
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @id smallint

BEGIN TRAN
INSERT INTO [dbo].[projects] (authid,logtimestamp,title)
OUTPUT INSERTED.id
VALUES (@authid,CURRENT_TIMESTAMP,@title);
COMMIT TRAN

SELECT @id AS [id]

SET NOCOUNT OFF
END

ButtonMoon · Nov 13, 2012

Instead of returning a single row result it's generally simpler and faster to return values using OUTPUT parameters.

http://msdn.microsoft.com/en-us/library/aa224819(v=sql.80).aspx

Code:

CREATE PROCEDURE dbo.ProjectInsert
(
  @id INT OUTPUT,
  @authid AS SMALLINT,
  @title AS VARCHAR(100)
)
AS
BEGIN;

  SET NOCOUNT ON;

  INSERT INTO dbo.projects (authid,logtimestamp,title)
  VALUES (@authid,CURRENT_TIMESTAMP,@title);

  SET @id = SCOPE_IDENTITY();

END;

mdlueck · Nov 13, 2012

Thank you for the tip ButtonMoon.

Do you know using that technique with adoCMD objects, is the IDENTITY still returned within an adoRS object?

If not, then where?

ADO way to capture IDENTITY value with OUTPUT in an INSERT SQL (1 Viewer)

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

Banana

split with a cherry atop.

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

Banana

split with a cherry atop.

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

Banana

split with a cherry atop.

mdlueck

Sr. Application Developer

Banana

split with a cherry atop.

mdlueck

Sr. Application Developer

mdlueck

Sr. Application Developer

ButtonMoon

Registered User.

mdlueck

Sr. Application Developer

Similar threads

Users who are viewing this thread