How do I get a new record ID

I am taking values entered by a user in a form and inserting them into a
table. I then would like to obtain the record ID after I have added the
record. However I do not understand why the following code does not work.
Set conn = New ADODB.Connection
conn.Open
" Provider=SQLNCLI;Server=myserver\sqlexpr
ess;Database=MyDatabase;Trusted_Con
nection=yes;"
Set rs = New ADODB.Recordset
rs.Open "tblContract", conn, adOpenDynamic, adLockOptimistic, adcmdtype
With rs
.AddNew
[value1]
[value2]
etc
.Update
End With
Dim strGetID As String
strGetID = "SELECT SCOPE_IDENTITY() AS last_identity_value"
Set rs = conn.Execute(strGetID)
lContractID = rs.Fields("last_identity_value").Value
I have also tried:
lContractID = rs.Fields("ContractID").Value
instead of the scope_identity but this doesn't work either.
The variable lContractID is '0' in both cases and "last_identity_value" is
shown as empty. However the record is successfully entered in the table with
an ID.
Can someone please let me know what I'm doing wrong.Recordset objects are for RETRIEVING data. Please do not use them for
affecting data. Use a stored procedure.
CREATE PROCEDURE dbo.AddContact
@.value1 VARCHAR(32),
@.value2 VARCHAR(32),
..etc,
@.NewContactID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.tblContact(value1, value2, etc)
SELECT @.value1, @.value2, etc;
SELECT @.NewContactID = SCOPE_IDENTITY();
END
GO
Now, call the stored procedure and retrieve the new id from the output
parameter.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:CBAEE2FD-81C6-439F-B299-AAFD30AD3D89@.microsoft.com...
>I am taking values entered by a user in a form and inserting them into a
> table. I then would like to obtain the record ID after I have added the
> record. However I do not understand why the following code does not work.
> Set conn = New ADODB.Connection
> conn.Open
> " Provider=SQLNCLI;Server=myserver\sqlexpr
ess;Database=MyDatabase;Trusted_C
onnection=yes;"
> Set rs = New ADODB.Recordset
> rs.Open "tblContract", conn, adOpenDynamic, adLockOptimistic, adcmdtype
> With rs
> .AddNew
> [value1]
> [value2]
> etc
> .Update
> End With
>
> Dim strGetID As String
> strGetID = "SELECT SCOPE_IDENTITY() AS last_identity_value"
> Set rs = conn.Execute(strGetID)
> lContractID = rs.Fields("last_identity_value").Value
>
> I have also tried:
> lContractID = rs.Fields("ContractID").Value
> instead of the scope_identity but this doesn't work either.
> The variable lContractID is '0' in both cases and "last_identity_value" is
> shown as empty. However the record is successfully entered in the table
> with
> an ID.
> Can someone please let me know what I'm doing wrong.
>|||Aaron
I'm just writing in Access VB and using Wrox Beginning Access 2002 VBA as a
reference which shows the method I used to add a new record. Unfortunately
your Create Procedure is not recognised in Access VB. Maybe I posted this to
the wrong section.
Can you offer me any other help?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Recordset objects are for RETRIEVING data. Please do not use them for
> affecting data. Use a stored procedure.
> CREATE PROCEDURE dbo.AddContact
> @.value1 VARCHAR(32),
> @.value2 VARCHAR(32),
> ...etc,
> @.NewContactID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.tblContact(value1, value2, etc)
> SELECT @.value1, @.value2, etc;
> SELECT @.NewContactID = SCOPE_IDENTITY();
> END
> GO
> Now, call the stored procedure and retrieve the new id from the output
> parameter.
>
>
>
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:CBAEE2FD-81C6-439F-B299-AAFD30AD3D89@.microsoft.com...
>
>|||No, Aaron is right, and this is the right section. Based on your connection
string, you are connecting to SQL Server, not an Access DB. With SQL
Server, it's usually best to perform modifications with stored procedures.
Invoke the Execute method on the Connection object to create the procedure
(this only needs to be done once). Invoke the Execute method on a Command
object with parameters to execute the stored procedure.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:B481AD0C-E4F8-4977-B38E-D1797D475BB2@.microsoft.com...
> Aaron
> I'm just writing in Access VB and using Wrox Beginning Access 2002 VBA as
> a
> reference which shows the method I used to add a new record. Unfortunately
> your Create Procedure is not recognised in Access VB. Maybe I posted this
> to
> the wrong section.
> Can you offer me any other help?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Brian thank you for your response. I did migrate the db from Access to SQL
Express and am now trying to amend my code. I think I have more to learn! Ar
e
there any online tutorial which you'd recommend?
"Brian Selzer" wrote:

> No, Aaron is right, and this is the right section. Based on your connecti
on
> string, you are connecting to SQL Server, not an Access DB. With SQL
> Server, it's usually best to perform modifications with stored procedures.
> Invoke the Execute method on the Connection object to create the procedure
> (this only needs to be done once). Invoke the Execute method on a Command
> object with parameters to execute the stored procedure.
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:B481AD0C-E4F8-4977-B38E-D1797D475BB2@.microsoft.com...
>
>|||I think your best bet is to hit Borders and buy a book, unless you're
comfortable with BOL and MSDN. I'm sure that there are a plethora of good
books on SQL Server, ADO, VB Database, etc. Maybe you should repost and ask
for suggestions. My library's pretty lean on introductory database books.
The only one I own is a vintage 1990 Que book, "Using SQL," that features
dBASE.
"Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in message
news:5A75C0E6-B3B3-47A5-B242-1A4D3F96EBCC@.microsoft.com...
> Brian thank you for your response. I did migrate the db from Access to SQL
> Express and am now trying to amend my code. I think I have more to learn!
> Are
> there any online tutorial which you'd recommend?
> "Brian Selzer" wrote:
>|||Brian, you are probably right there are no quick fixes.
I see that you don't part with your old books either!
"Brian Selzer" wrote:

> I think your best bet is to hit Borders and buy a book, unless you're
> comfortable with BOL and MSDN. I'm sure that there are a plethora of good
> books on SQL Server, ADO, VB Database, etc. Maybe you should repost and a
sk
> for suggestions. My library's pretty lean on introductory database books.
> The only one I own is a vintage 1990 Que book, "Using SQL," that features
> dBASE.
> "Lisa Tanenbaum" <LisaTanenbaum@.discussions.microsoft.com> wrote in messag
e
> news:5A75C0E6-B3B3-47A5-B242-1A4D3F96EBCC@.microsoft.com...
>
>