473,386 Members | 1,886 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 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 13938
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: ree32 | last post by:
I am inserting a record into a table that automatically generates unique ids (i.e. Primary Key). Is there anyway to return this id. As I am using this on ASP.net page and I really need the ID to...
6
by: Tony Stoker | last post by:
I have a .Net web app that adds a record to a SQL database. After the user adds their record I want to have a link that will link them to their new record! The recordID is a AutoNumber in the...
33
by: Steven Taylor | last post by:
Hope someone can help. This is half an Access question. The half I'm using is Access Xp as a backend data file. I'm using ODBC to connect to the data file. All commands are via SQL type...
5
by: tony collier | last post by:
To break out of a loop i have seen some people use RETURN instead of BREAK I have only seen RETURN used in functions. Does anyone know why RETURN is used instead of BREAK to kill loops?
6
by: Hardy Wang | last post by:
Hi all, I have the following codes, but SCOPE_IDENTITY() just returns NULL to me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works fine!! Since I have a trigger on the...
9
by: eagle | last post by:
How can I return the new id that is created when an insert command is used? for example: qry = "insert into tblClients (lname, fname) values ('smith', 'joe')" Dim xyz as string =...
8
by: BigJohnson | last post by:
We're using ASPUpload as a tool to upload files to our server and save the details to SQLServer. However, I have an application where I need to return the pkID of the just saved file. I'm assuming...
0
by: Ben Dewey | last post by:
Guys, I am new to the System.Transactions namespace, but I am trying to use it to process powerpoint document into a database. I have a strongly typed DataSet with Talks and Slides. Both...
4
by: Dabbler | last post by:
I'm using an SQLCommand to insert row using a text command. Is there a way to return the IDENTITY key value after the insert ? Thanks much!
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

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.