[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

Is it possible to combine both the ADODB.Command.Execute to return the @@IDENTITY value

My first Q here so, please forgive me. Im developing in VB6, using MS Access 2000. I use stored procedures to insert / update records but I need to use the Command.Execute for some in order to solve problems with saving data to memo fields, but I would like to return the @@IDENTITY value after inserting

    '*** create an object
    Set oCmd = CreateObject("ADODB.Command")
   
    '*** assigning the command object parameters
    With oCmd
        '*** Stored Procedure Name (Param 1)
        .CommandText = colParams.Item(1)
        .CommandType = adCmdStoredProc
        .ActiveConnection = moConn.ConnectionString
       
        For iCount = 0 To .Parameters.Count - 1
            If Right(colParams(iCount + 2), 3) = "#@#" Then
                '*** This is for memo fields
                .Parameters.Item(iCount).SIZE = 8000
                .Parameters.Item(iCount).Type = adLongVarWChar
                .Parameters.Item(iCount).Value = Left(colParams(iCount + 2), Len(colParams(iCount + 2)) - 3)
            Else
                .Parameters.Item(iCount).Value = colParams(iCount + 2)
            End If
        Next
       
        '*** Store the result in a recordset
        Set oRecord = .Execute(lRecords)
    End With
   '*** Get Identity (doesnt work)
   lLastIdent =  oRecord(0)

What am I missing

I also use the RecordSet.Execute and have no problems, but I need to be able to save large Memo data to certain tables and using Command.Execute is the only way I can do it.

If someone can help with either @@Identity working with Command.Execute or saving large memo fields....please be my guest. I appreciate it. Thanks in advance!
0
ellisrichardson
Asked:
ellisrichardson
  • 6
  • 5
  • 3
  • +2
1 Solution
 
muzzy2003Commented:
Are you using Access or SQL Server? You say Access, but @@IDENTITY is a SQL Server function. You refer to stored procedures, but these aren't features in Access, which uses queries instead. Could you clarify, and also please post the code of the procedure you are using? Thanks.
0
 
fds_fatboyCommented:
You can use SQL server as A backend for Access (as well as Jet).  @@identity has been in access since Access 2000.

If you are using SQL Server backend (as I guess you are), you could do this with your SPs:

CREATE PROCEDURE <your proc name>
(@id int OUT, ...
AS
INSERT INTO ...
...
SELECT @id = @@identity

go

Then set you first command parameter to be type adinteger and read it after the .Execute

Read this :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
0
 
muzzy2003Commented:
OK - not used Access for a while!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ellisrichardsonAuthor Commented:
I am using MS Access 2000 and Visual Basic 6...
0
 
fds_fatboyCommented:
Are you using Jet or SQL/Server backend?
0
 
ellisrichardsonAuthor Commented:
Am using Jet and the above code to call the Command.Execute..
0
 
leonstrykerCommented:
Why don't you have your store procedure return the @@Identity.  Just at the end of the procedure add

SELECT @@Identity

BTW, I would not even bother with a Command object, just use a Recordset and concatanate your SQL string to Execute the store procedure.

Leon
0
 
ellisrichardsonAuthor Commented:
You cannot include SELECT @@IDENTITY after an INSERT statement in an MS Access sotred procedure. MS Access will only allow you to do one thing!

At this point none of these suggestions seem to be working. I may have to look at it from another way
0
 
leonstrykerCommented:
From:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp

Microsoft Access 2000 or later does support the @@IDENTITY property to retrieve the value of an Autonumber field after an INSERT. Using the RowUpdated event, you can determine if an INSERT has occurred, retrieve the latest @@IDENTITY value, and place that in the identity column of the local table in the DataSet.

Take a look at the samples at the site.  They are for .NET, but should be adaptable to VB6.

Leon

0
 
leonstrykerCommented:
0
 
leonstrykerCommented:
fds_fatboy,

Yeap same site.
0
 
ellisrichardsonAuthor Commented:
Thanks guys
either way the MSDN site relates to .NET and I am not using .NET, but i am using MSAccess 2000 and VB6. Am i doing something fundamentally wrong?
0
 
leonstrykerCommented:
Did you look at the EE link?
0
 
ellisrichardsonAuthor Commented:
Read the link, but although that is for Access 2002, I tried it in another DB and it doesnt work. Hasnt that been written as an SQL Server Stored Proc, not an MS Access 2002 Stored Proc or am i missing something
0
 
coopzzCommented:
This is a snippet that I've used lots of times with a access db backend.. you will have to manuplate it for your own purposes.

Public Function ExecuteActionQueryReturnID(ByVal sSQL As String) As Long
    'Note: Errors are not trapped here. They should be trapped and
    'handled appropriately in the calling routine
    Dim con As ADODB.Connection
    Dim lRecordCount As Long
    Dim rs As New ADODB.Recordset

    On Error GoTo Handle_Error
   
    Set con = GetConnection() 'Fetch a connection from the pool
    con.Execute sSQL, lRecordCount
   
    'you can do this type of thing only on the same connection
    sSQL = "SELECT @@identity"
    rs.Open sSQL, con, adOpenForwardOnly, adLockReadOnly

    ExecuteActionQueryReturnID = rs.fields(0).Value 'grab the identity of added record
   
    ReturnConnection con 'Return the connection to the pool
   
    Exit Function

Handle_Error:
    LogMessage "ExecuteActionQuery('" & sSQL & "')"
End Function

Cheers

CooPzZ
0
 
ellisrichardsonAuthor Commented:
Thanks to everyone that helped, In the end I rewrote half of my app in about 20 hours to get this solution to work the right way.

I awarded this to coopzz, even though he told me what i already knew.... he made me realise that i had no other alternative than to re-do my code. He was the closest to what i asked for (Access 2000 / Vb6)

Thanks to all.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now