SCOPE_IDENTITY from Insert on ADO connection

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.


SheritlwAsked:
Who is Participating?
 
iHadiCommented:
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
 
SheritlwAuthor Commented:
I wanted to use scope_identity so that I can be sure to return the id created during the execution.

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SethiCommented:
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
 
iHadiCommented:
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
 
SheritlwAuthor Commented:
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
 
SethiCommented:
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
 
leonstrykerCommented:
Sethi's way is the way to go.

Leon
0
 
SheritlwAuthor Commented:
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
 
SheritlwAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.