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.Exec
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.
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.Exec
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.
ASKER
I wanted to use scope_identity so that I can be sure to return the id created during the execution.
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.
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.
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
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
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
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
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
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
ASKER
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?
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?
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
http://www.sqlteam.com/forums/
http://www.sql-server-performance.com/forum/default.asp
Sethi's way is the way to go.
Leon
Leon
ASKER
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.
Remember the connection and recordset are still open after the insert.
ASKER
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?
Can anyone help me design one according to the insert statement above and tell me how to call it from VB6?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"SELECT @@IDENTITY AS Last_ID"
This will return a single row, single column recordset
MyID= Rs.fields("Last_ID").Value
Thats it