Re: Retrieve value of newly inserted row.

I GOT IT! WooHoo! Thanks to a co-worker, this problem has been solved! I was using scope_identity incorrectly. This is how you should use it: INSERT INTO Table1 ( Column1, Column2) VALUES ( Value1, Value2) INSERT INTO Table2 ( Column1, Column2) VALUES ( scope_identity(), Value2) Using scope_identity() for the value in insert #2 will return the value of the identity field just created from insert #1. On 2/15/07, Brad Fuller <bfuller@cpacampaigns.com> wrote:

Okay, I found the correct function and below is what I have: - Show quoted text - $insert1 = "INSERT INTO table1 ( <snip> )

          VALUES (

<snip> )

     SELECT scope_identity()

     INSERT INTO table2 (
      credit_card_id,
      case_number,
      comments)
     VALUES (
      'scope_identity',
      '$case',
      '$comments')";

echo "$insert1"; mssql_query($insert1) or die ("Query failed: <br />".mssql_get_last_message()); echo "Insert complete"; the scope_identity function is suppose to select the last inserted ID for the first insert statement. When my query executes, it "appears" to go thru all the steps correctly...it inserts the first record just fine, displays the echo of my query and returns the echo of "Insert Complete" HOWEVER, when I go to look at the data base there is NO data inserted into table2 even though my query returned that "Insert Complete" statement. Any ideas? On 2/14/07, Jim Lucas <lists@cmsws.com> wrote:

Brad Fuller wrote:

From looking on the web (MSDN) I found the @@identity and the

explanation

of what it is, but MS's "example" is horrible and does not show a good context for using this function. Could you elaborate more on its use?

$q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT LAST_INSERT_ID=@@IDENTITY"); $r = mssql_fetch_assoc($q); HTH, Brad

Might look at this http://us3.php.net/manual/en/function.mssql-query.php#46026 -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on

different

strings. But there are times for you and me when all such things

agree.

- Rush

I don't think you can do multiple inserts in a single query. Or maybe you can but the select statement after it causes the second insert not to be run? I don't know, but I've seen several examples - each use 2 separate insert queries. My reply to your original message was incomplete; I apologize $q1 = mssql_query("INSERT INTO Table1 (...) VALUES (...) SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]"); $r = mssql_fetch_assoc($q1); $insert_id = $r['SCOPE_IDENTITY']; $q2 = mssql_query("INSERT INTO Table2 (...) VALUES ({$insert_id}, ...)");