Return identity

[Filips Benoit]

Sorry I'm new in this,

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

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

Msgbox NewID triggers an error !

Filip

[Guinness Mann]

Re: Return identity

In article <ZQzlb.98407$sC 7.4689000@phobo s.telenet-ops.be>,
benoit.filips@p andora.be says...[color=blue]
> objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES ('012345678')
> SELECT @@IDENTITY as 'NewID'"[/color]

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

-- Rick

[Filips Benoit]

Re: Return identity

There was no semicolon in the code!I followed an example in a Book!
Where should i put the semicolon?

Filip

"Guinness Mann" <GMann@dublin.c om> wrote in message
news:MPG.1a0094 5d5712d80498971 5@news.newsguy. com...[color=blue]
> In article <ZQzlb.98407$sC 7.4689000@phobo s.telenet-ops.be>,
> benoit.filips@p andora.be says...[color=green]
> > objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES[/color][/color]
('012345678')[color=blue][color=green]
> > SELECT @@IDENTITY as 'NewID'"[/color]
>
> Did you forget to put the semicolon in, or did you mistype it when you
> posted?
>
> -- Rick
>[/color]

[Guinness Mann]

Re: Return identity

In article <E%Jlb.99955$R_ 6.4649061@phobo s.telenet-ops.be>,
benoit.filips@p andora.be says...[color=blue]
> There was no semicolon in the code!I followed an example in a Book!
> Where should i put the semicolon?[color=green][color=darkred]
> > > objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES[/color][/color]
> ('012345678')[color=green][color=darkred]
> > > SELECT @@IDENTITY as 'NewID'"[/color][/color][/color]

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

-- Rick

[Filips Benoit]

Re: Return identity

I try this but it keeps returning zero !

Dim newid As Long
CurrentProject. Connection.Exec ute "INSERT INTO [ORDER] (ORD_P_ID,
ORD_CREATION_DA TE) VALUES ('4004', CONVERT(DATETIM E,'" & 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" <GMann@dublin.c om> wrote in message
news:MPG.1a02f6 c4f0a2559998971 8@news.newsguy. com...[color=blue]
> In article <E%Jlb.99955$R_ 6.4649061@phobo s.telenet-ops.be>,
> benoit.filips@p andora.be says...[color=green]
> > There was no semicolon in the code!I followed an example in a Book!
> > Where should i put the semicolon?[color=darkred]
> > > > objConn.Execute "INSERT INTO CONTACT (CNT_PHONE) VALUES[/color]
> > ('012345678')[color=darkred]
> > > > SELECT @@IDENTITY as 'NewID'"[/color][/color]
>
> Between statements, I believe. Where the "Go" would go. Otherwise you
> must send two commands.
>
> -- Rick
>[/color]

[Guinness Mann]

Re: Return identity

In article <podmb.103924$y O.4810825@phobo s.telenet-ops.be>,
benoit.filips@p andora.be says...[color=blue]
> I try this but it keeps returning zero !
>
> Dim newid As Long
> CurrentProject. Connection.Exec ute "INSERT INTO [ORDER] (ORD_P_ID,
> ORD_CREATION_DA TE) VALUES ('4004', CONVERT(DATETIM E,'" & Year(Date) & "-" &
> Month(Date) & "-" & Day(Date) & " 00:00:00', 102)); select @@identity as
> 'newID'"
> Me.Requery
> MsgBox newid >> returns 0[/color]
I think the zero means that it didn't affect any rows.
[color=blue]
> My question stays: is it possible to return the newid into a VB-variable
> directly?[/color]

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

-- Rick