objrs.update not retuning @@Identity value

Hi All,

I have the following:

objRS.Open strSQL,objConn,2,3
objRS.AddNew

 ------
 ------

objRS.Update
idOfNewlyAddedRecord = objRS("JobID")

But this is not giving me the identity value of the just added record. Help!

LVL 1
newofficeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

newofficeAuthor Commented:
And I am using ASP and SQL Server.
0
peterxlaneCommented:
This is a great article that explains how to do it in different versions of SQL and even Access.  I like using a stored procedure that returns the value.

http://www.aspfaq.com/show.asp?id=2174

0
newofficeAuthor Commented:
Okay I read that article. But how do i get the identity with using objRS.addnew with SQL server. The code was designed for access and right now we just want to convert to SQL database and keep as much front end code as possible. So my question is how do i get the identity value back?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

peterxlaneCommented:
I guess if you can't convert your update to an insert, then you would have to just do this:

<%
objRS.Open strSQL,objConn,2,3
objRS.AddNew

 ------
 ------

objRS.Update
objRS.Close
objRS.Open "SELECT Max(JobID) FROM YOURTABLE", objConn
idOfNewlyAddedRecord = objRS("JobID")
%>

which unfortunately opens another recordset, but I don't see any other way...
0
newofficeAuthor Commented:
This is kind of tricky, becuase if there is an other insert it is going to give me that. What I would like to know is doesn't objRS.update returning identity doens't work with SQL server?

0
peterxlaneCommented:
From what I have read, @@IDENTITY returns the identity value after an INSERT, SELECT INTO, or a BULK COPY completes, but does not apply after an update.

For example:

<%
objRS.Open "SELECT * FROM tblTest", objConn, 2, 3
objRS.AddNew
objRS("Test") = "Hello World"
objRS.Update
Set oRS = objConn.Execute("SELECT @@IDENTITY AS NewID")
idOfNewlyAddedRecord = oRS(0).Value
Response.Write "New ID: " & idOfNewlyAddedRecord
objRS.Close


objConn.Execute("INSERT INTO tblTest (Test) VALUES ('hey')")
Set oRS = objConn.Execute("SELECT @@IDENTITY AS NewID")
idOfNewlyAddedRecord = oRS(0).Value
Response.Write "New ID: " & idOfNewlyAddedRecord
%>


The first attempt with the update fails, but the insert does return the ID.  If you wanted to use the method of:

objRS.Open "SELECT Max(JobID) FROM YOURTABLE", objConn

You could add additional criteria to the statement to make sure it is the record just updated, something like:

objRS.Open "SELECT Max(JobID) FROM YOURTABLE WHERE JOB = 'value just entered' AND USER = 'user'", objConn


My advice would be to just convert it to use a stored procedure or use an insert statement.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

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.