• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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!

0
newoffice
Asked:
newoffice
  • 3
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now