getting @@Identity from recordset

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

September 26th, 2003, 05:55 PM

unatratnag

Average Intelligence

Join Date: Apr 2003

Location: Ohio/Chicago

Posts: 678

Time spent in forums: 5 m 54 sec
Reputation Power: 6

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>";

September 26th, 2003, 06:31 PM

unatratnag

Average Intelligence

Join Date: Apr 2003

Location: Ohio/Chicago

Posts: 678

Time spent in forums: 5 m 54 sec
Reputation Power: 6

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, 06:42 PM

Doug G

Grumpier Old Moderator

Dev Shed God 12th Plane (10500 - 10999 posts)

Join Date: Jun 2003

Posts: 10,550

Time spent in forums: 4 Weeks 15 h 43 m 35 sec
Reputation Power: 634

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)

September 26th, 2003, 06:48 PM

unatratnag

Average Intelligence

Join Date: Apr 2003

Location: Ohio/Chicago

Posts: 678

Time spent in forums: 5 m 54 sec
Reputation Power: 6

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, 09:27 PM

Doug G

Grumpier Old Moderator

Join Date: Jun 2003

Posts: 10,550

Time spent in forums: 4 Weeks 15 h 43 m 35 sec
Reputation Power: 634

Quote:

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.

September 26th, 2003, 09:33 PM

drgroove

pushing envelopes, not pencils

Dev Shed God 2nd Plane (6000 - 6499 posts)

Join Date: Feb 2002

Posts: 6,223

Time spent in forums: 1 Day 4 h 32 m 57 sec
Reputation Power: 174

Moved from the PHP forum to the MSSQL forum, as ultimately this is an issue w/ MSSQL, not PHP.

__________________
Give a person code, and they'll hack for a day; Teach them how to code, and they'll hack forever.
Analyze twice; hack once.
The world's first existential ITIL question: If a change is released into production without a ticket to track it,
was it actually released?

About DrGroove: ITIL-Certified IT Process Engineer - Enterprise Application Architect -
Freelance IT Journalist - Devshed Moderator - Funk Bassist Extraordinaire

September 28th, 2003, 08:59 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,745

Time spent in forums: 2 Weeks 3 Days 20 h 25 m 5 sec
Reputation Power: 31

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.

February 7th, 2004, 01:08 AM

GenkiDave

Registered User

Join Date: Jan 2004

Location: Japan

Posts: 12

Time spent in forums: < 1 sec
Reputation 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" - ->


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
%>

Viewing: Dev Shed Forums > Databases > MS SQL Development > getting @@Identity from recordset

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!