SCOPE_IDENTITY from Insert on ADO connection

Posted on 2005-05-04
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.

Question by:Sheritlw
    LVL 13

    Expert Comment

    Open a new recordset with the following Sql string


    This will return a single row, single column recordset

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

    Thats it

    Author Comment

    I wanted to use scope_identity so that I can be sure to return the id created during the execution.

    LVL 18

    Expert Comment

    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.

    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.

    LVL 13

    Expert Comment

    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

    Author Comment

    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?
    LVL 18

    Expert Comment

    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:
    LVL 29

    Expert Comment

    Sethi's way is the way to go.


    Author Comment

    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.

    Author Comment

    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?

    LVL 18

    Accepted Solution


    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now