#1
  1. No Profile Picture
    Average Intelligence
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2003
    Location
    Ohio/Chicago
    Posts
    678
    Rep Power
    12

    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

    PHP Code:
               $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");
               echo 
    "$Identity<br>"
  2. #2
  3. No Profile Picture
    Average Intelligence
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2003
    Location
    Ohio/Chicago
    Posts
    678
    Rep Power
    12
    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?
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,443
    Rep Power
    4539
    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:
    Code:
    strSql = "INSERT INTO table blah blah ..."
    con.execute strSql
    strSql = "SELECT @@IDENTITY as MyID"
    set rs = con.execute(strSql)
    MyIDVar = rs(0)
  6. #4
  7. No Profile Picture
    Average Intelligence
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2003
    Location
    Ohio/Chicago
    Posts
    678
    Rep Power
    12
    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
  8. #5
  9. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,443
    Rep Power
    4539
    Ooh, hey doug, when'd you get promoted to moderator?
    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.
  10. #6
  11. Moderator Emeritus
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    7,186
    Rep Power
    2265
    Moved from the PHP forum to the MSSQL forum, as ultimately this is an issue w/ MSSQL, not PHP.
    DrGroove, Devshed Moderator | New to Devshed? Read the User Guide | Connect with me on LinkedIn
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    The insert statement will return a recordset as well so the select result is in a separate recordset.

    Code:
    $strSQL = "set nocount on insert into [presenters] (Last_Name, First_Name, Quicklook) values ('$hln','$hfn','$hql') select @@Identity ";
    Adding set nocount on means that the insert will not create a recordset.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Japan
    Posts
    12
    Rep Power
    0
    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??

    //////////////////////The Code//////////////////////////

    <%

    <%@ 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")
    objConn.Open strConnection
    Set objRS = objConn.Execute(strSQL)
    varNewID = objRS("NewID")
    objConn.Close()
    Set objConn = Nothing
    Set objRS = Nothing
    %>

IMN logo majestic logo threadwatch logo seochat tools logo