ellisrichardson
asked on
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.Comman
'*** 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).S
.Parameters.Item(iCount).T
.Parameters.Item(iCount).V
Else
.Parameters.Item(iCount).V
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!
'*** create an object
Set oCmd = CreateObject("ADODB.Comman
'*** 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).S
.Parameters.Item(iCount).T
.Parameters.Item(iCount).V
Else
.Parameters.Item(iCount).V
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!
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.
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
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
OK - not used Access for a while!
ASKER
I am using MS Access 2000 and Visual Basic 6...
Are you using Jet or SQL/Server backend?
ASKER
Am using Jet and the above code to call the Command.Execute..
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
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
ASKER
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
At this point none of these suggestions seem to be working. I may have to look at it from another way
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
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
Take a look here as well:
https://www.experts-exchange.com/questions/20803355/IDENTITY-return-after-insert-using-access-2002.html
Leon
https://www.experts-exchange.com/questions/20803355/IDENTITY-return-after-insert-using-access-2002.html
Leon
LeonStryker:
I wrote
Read this :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
You wrote:
From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
...
I wrote
Read this :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
You wrote:
From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconRetrievingIdentityOrAutonumberValues.asp
...
fds_fatboy,
Yeap same site.
Yeap same site.
ASKER
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?
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?
Did you look at the EE link?
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.