471,092 Members | 1,561 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Return identity

Sorry I'm new in this,

Add a record using T-SQL and Connection.Execute
How can i insert the identity in a VB-variable

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn = CurrentProject.Connection
objConn.Open
objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')
SELECT @@IDENTITY as 'NewID'"

Msgbox NewID triggers an error !

Filip

Jul 20 '05 #1
5 13834
In article <ZQ*********************@phobos.telenet-ops.be>,
be***********@pandora.be says...
objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')
SELECT @@IDENTITY as 'NewID'"


Did you forget to put the semicolon in, or did you mistype it when you
posted?

-- Rick

Jul 20 '05 #2
There was no semicolon in the code!I followed an example in a Book!
Where should i put the semicolon?

Filip

"Guinness Mann" <GM***@dublin.com> wrote in message
news:MP************************@news.newsguy.com.. .
In article <ZQ*********************@phobos.telenet-ops.be>,
be***********@pandora.be says...
objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678') SELECT @@IDENTITY as 'NewID'"


Did you forget to put the semicolon in, or did you mistype it when you
posted?

-- Rick

Jul 20 '05 #3
In article <E%*********************@phobos.telenet-ops.be>,
be***********@pandora.be says...
There was no semicolon in the code!I followed an example in a Book!
Where should i put the semicolon?
objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678') SELECT @@IDENTITY as 'NewID'"


Between statements, I believe. Where the "Go" would go. Otherwise you
must send two commands.

-- Rick

Jul 20 '05 #4
I try this but it keeps returning zero !

Dim newid As Long
CurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,
ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &
Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as
'newID'"
Me.Requery
MsgBox newid >> returns 0
Msgbox DMax("ORD_ID","[ORDER]") >> returns the new id

My question stays: is it possible to return the newid into a VB-variable
directly?

Filip
"Guinness Mann" <GM***@dublin.com> wrote in message
news:MP************************@news.newsguy.com.. .
In article <E%*********************@phobos.telenet-ops.be>,
be***********@pandora.be says...
There was no semicolon in the code!I followed an example in a Book!
Where should i put the semicolon?
> objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES

('012345678')
> SELECT @@IDENTITY as 'NewID'"


Between statements, I believe. Where the "Go" would go. Otherwise you
must send two commands.

-- Rick

Jul 20 '05 #5
In article <po*********************@phobos.telenet-ops.be>,
be***********@pandora.be says...
I try this but it keeps returning zero !

Dim newid As Long
CurrentProject.Connection.Execute "INSERT INTO [ORDER] (ORD_P_ID,
ORD_CREATION_DATE) VALUES ('4004', CONVERT(DATETIME,'" & Year(Date) & "-" &
Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as
'newID'"
Me.Requery
MsgBox newid >> returns 0 I think the zero means that it didn't affect any rows.
My question stays: is it possible to return the newid into a VB-variable
directly?


No. You must create a recordset, retrieve the results into the
recordset and then query the recordset.

-- Rick

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Tony Stoker | last post: by
5 posts views Thread by tony collier | last post: by
reply views Thread by Ben Dewey | last post: by
4 posts views Thread by Dabbler | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.