September 26th, 2003, 06:55 PM
getting @@Identity from recordset
this is my first time working with the @@identity, and i tried copying and pasting some code but can't seem to get it to work. The Identity will return a number if i run it from the MSsql command line, but can't figure out how to get it out of the recordset
$strSQL = "insert into [presenters] (Last_Name, First_Name, Quicklook) values ('$hln','$hfn','$hql');select @@Identity as NewID";
$newID = odbc_exec( $conn, $strSQL);
echo "Identity new query $strSQL<br>";
$Identity = odbc_result($newID, "NewID");
September 26th, 2003, 07:31 PM
ok, here's probably the issue, the servers are actually linked. So the @@identity i assume is local. So how does one get teh @@Identity from the remote machine?
September 26th, 2003, 07:42 PM
Try seperating the queries. @@Identity is connection based so if you maintain the connection you can do your insert, then issue another odbc_exec with the select @@Identity query.
You may be getting some other return value from the insert statement confusing the result.
Just guessing with php odbc. I use separate query statments in asp like:
strSql = "INSERT INTO table blah blah ..."
strSql = "SELECT @@IDENTITY as MyID"
set rs = con.execute(strSql)
MyIDVar = rs(0)
September 26th, 2003, 07:48 PM
Ooh, hey doug, when'd you get promoted to moderator?
yea, that solved it
but i was trying to keep it one statement i guess just to make sure i got the most recent record, is there really NO way possible to keep it one liine tha tyou know of? I don't really want to deal with locking the database...nice asp code by the way
September 26th, 2003, 10:27 PM
A couple weeks ago
There is an adExecuteNoRecords ado constant that should prevent the insert from returning anything. I'm not sure how you would implement it in php tho.
September 26th, 2003, 10:33 PM
Moved from the PHP forum to the MSSQL forum, as ultimately this is an issue w/ MSSQL, not PHP.
September 28th, 2003, 09:59 AM
The insert statement will return a recordset as well so the select result is in a separate recordset.
Adding set nocount on means that the insert will not create a recordset.
$strSQL = "set nocount on insert into [presenters] (Last_Name, First_Name, Quicklook) values ('$hln','$hfn','$hql') select @@Identity ";
February 7th, 2004, 02:08 AM
OK, I'm almost finished a project, and I believe that all I have to do is use this to add the new ID into the joined table. But I'm stumped on something so small, it might just kill me after I ask one of you to help me out here, PLEASE!!
What on earth goes into the VALUES barckets? I'm not getting the new ID from a Form's textbox where all I'd have to do is use Request.Form("txtMovieName"). So, like I said, what goes into the VALUES here? Do I put this code into the Form's asp page, or do I put into the add.asp page where all the info is added to the database after I click the Submit button??
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!- - #include file="adovbs.inc" - ->
<!-- #include file="connect.asp" -->
Dim objRS, strSQL, NewID
strSQL = "SET NOCOUNT ON INSERT INTO tblActors(fldActorID) VALUES('ABC', 123) SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = objConn.Execute(strSQL)
varNewID = objRS("NewID")
Set objConn = Nothing
Set objRS = Nothing