?
Solved

SCOPE_IDENTITY from Insert on ADO connection

Posted on 2005-05-04
10
Medium Priority
?
1,159 Views
Last Modified: 2013-11-23
I haven't had any luck asking a question yet, but thought I might give it another try.

I need to return the identity from an insert statement that is executed on an ado connection.

My code is as follows.

                  strSQL = "INSERT INTO People_Earl ("
                  strSQL = strSQL & "CompanyID, "
                  strSQL = strSQL & "LastName, "
                  strSQL = strSQL & "FirstName, "
                  strSQL = strSQL & "Suffix, "
                  strSQL = strSQL & "AddressCodeID, "
                  strSQL = strSQL & "LanguageTypeID, "
                  strSQL = strSQL & "JobTitleID, "
                  strSQL = strSQL & "Remarks, "
                  strSQL = strSQL & "EmailAddr, "
                  strSQL = strSQL & "WorkPhone, "
                  strSQL = strSQL & "MobilePhone, "
                  strSQL = strSQL & "FaxPhone, "
                  strSQL = strSQL & "PeopleTypeID, "
                  strSQL = strSQL & "PeopleStatusID, "
                  strSQL = strSQL & "DepartmentID, "
                  strSQL = strSQL & "PhoneExt, "
                  strSQL = strSQL & "SexMale, "
                  strSQL = strSQL & "LocationID "
                  strSQL = strSQL & ") VALUES ("
                  strSQL = strSQL & mlngCompanyID & ", "
                  strSQL = strSQL & Str2Field(mstrLastName) & ", "
                  strSQL = strSQL & Str2Field(mstrFirstName) & ", "
                  strSQL = strSQL & Str2Field(mstrSuffix) & ", "
                  strSQL = strSQL & mlngAddressCodeID & ", "
                  strSQL = strSQL & mlngLanguageTypeID & ", "
                  strSQL = strSQL & mlngJobTitleID & ", "
                  strSQL = strSQL & Str2Field(mstrRemarks) & ", "
                  strSQL = strSQL & Str2Field(mstrEmailAddr) & ", "
                  strSQL = strSQL & Str2Field(mstrWorkPhone) & ", "
                  strSQL = strSQL & Str2Field(mstrMobilePhone) & ", "
                  strSQL = strSQL & Str2Field(mstrFaxPhone) & ", "
                  strSQL = strSQL & mlngPeopleTypeID & ", "
                  strSQL = strSQL & mlngPeopleStatusID & ", "
                  strSQL = strSQL & mlngDepartmentID & ", "
                  strSQL = strSQL & Str2Field(mstrPhoneExt) & ", "
                  strSQL = strSQL & Bool2Field(mboolSexMale) & ", "
                  strSQL = strSQL & mlngLocationID & "); "
                  strSQL = strSQL & "SELECT SCOPE_IDENTITY as 'Identity'"

            moDataConn.Connection.Execute strSQL, mlngRowsAffected

Now I need to get the identity without getting an error, and assign it to a variable

mlngIdentity = Identity

If I could get an answer quickly that works and doesn't require me significantly rewrite my code or write a stored procedure (I can't get that done for two weeks and I need to test), I will give max points.


0
Comment
Question by:Sheritlw
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

Expert Comment

by:iHadi
ID: 13930209
Open a new recordset with the following Sql string

"SELECT @@IDENTITY AS Last_ID"

This will return a single row, single column recordset

MyID= Rs.fields("Last_ID").Value

Thats it
0
 

Author Comment

by:Sheritlw
ID: 13930402
I wanted to use scope_identity so that I can be sure to return the id created during the execution.

0
 
LVL 18

Expert Comment

by:Sethi
ID: 13934572
Ok...first thing - it is a bad practice to use the variable name again and again for concatenation and the following statement:

strSQL = "INSERT INTO People_Earl ("
                  strSQL = strSQL & "CompanyID, "
                  strSQL = strSQL & "LastName, "

should be written as:
strSQL = "INSERT INTO People_Earl (" & _
                  "CompanyID, " & _
                  "LastName, "
...and so on till it completes.

Secondly:
I generally seperate the execution of my SQL Statement and getting the SCOPE_IDENTITY value.

I execute the Insert statement through Execute method of the connection object and then immediately after that, I get the value of the recently added autonumber like this:

cn.execute strSQL

strSQL = "SELECT SCOPE_IDENTITY as CurrentValue from People_Earl"
rsGetdata.Open cn, strSQL

mlngIdentity = clng(rsGetData(0))

I am assuming that you will initilaize the recordset and connection object before you use this code.



0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:iHadi
ID: 13935014
the Sql string I wrote in my previous comment returns the last value of an autonumber column that has been added using the connection object

any other connection objects inserts is not visible to it so don't get worried about mixing up your numbers due to another insert to the same db by another user

In your situation you should do a seperate query after you add a new record

Insert statment (1) ; Connection.execute (Your Insert String)
Query ID Statment (1) ; Rs = Connection.execute ("SELECT @@IDENTITY  AS Last_ID")
                                  MyID= Rs.fields("Last_ID").Value <- this will hold the new number the db created in the autonumber
                                                                                       column of insert statment (1) if any and returns 0 if none
Insert statment (2) ; Connection.execute (Your next Insert String)
Query ID Statment (1) ; Rs = Connection.execute ("SELECT @@IDENTITY  AS Last_ID") <- the same query
                                  MyID= Rs.fields("Last_ID").Value <- this will hold the new number the db created in the autonumber
                                                                                       column of insert statment (2) if any and returns 0 if none
@@IDENTITY is a variable in the DBMS (Access, Sql Server...) that holds the last ID the DBMS creates in any table of the database and all we are doing is getting its value

So you should query the @@IDENTITY value after the insert immediatly before any other insert or it will return the new insert's ID
0
 

Author Comment

by:Sheritlw
ID: 13936232
My concern is that there will be hundreds of people pounding on the same database at the same time.
I use classes to execute my inserts and already have a recordset open.  I would like to be sure that I am receiving the correct identity.
I know I can get it from a stored procedure, but isn't there a way to return the identity value from the actual insert statement?
0
 
LVL 18

Expert Comment

by:Sethi
ID: 13936288
Your concern is right. The method I suggested would work in either desktop applications or network with low no. of users. In high load servers, you need to return the value of SCOPE_IDENTITY from the SP. I however can help you with the Code and you will find some real good help in the following forums:
http://www.sqlteam.com/forums/
http://www.sql-server-performance.com/forum/default.asp
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13936405
Sethi's way is the way to go.

Leon
0
 

Author Comment

by:Sheritlw
ID: 13936457
So how would I retrieve the Scope_Identity through an insert statement that is not a stored procedure.
Remember the connection and recordset are still open after the insert.
0
 

Author Comment

by:Sheritlw
ID: 13939382
OK, since I can't figure out how to return the actual id from an insert statement and  I am not the db designer in this project, I decided to attempt a stored procedure that returns the id.

Can anyone help me design one according to the insert statement above and tell me how to call it from VB6?

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question