| Author |
Topic |
|
jrlanders
Starting Member
15 Posts |
 Posted - 2003-09-17 : 11:19:35
|
| Okey, maybe this is an easy one, and I hope that I have found the correct forum... if not please forgiveWebdevel project using ASP and MSSQL 2000Table is SessionSessionID field is marked as Identity yes, Identity Speed 1, Identity Increment 1.Code: 'Execute the INSERT statement and the SELECT @@IDENTITY. SQL = "INSERT INTO Session (" & SQLFields & ")" SQL = SQL & " VALUES (" & SQLValues & ");" 'Response.write SQL Set Session = Conn.Execute(SQL) 'Do the insert, and then... **This is successfule and data is written in to the table Set Session = Conn.Execute( "SELECT @@Identity") 'Retrieve the @@IDENTITY value. SessionId = Session(0) ** The SessionId variable is never loaded here. I never get a return value in the select request? Anyone see anything I am doing wrong here... Please email me with your thoughts or post here Thank you in advance |
|
|
jhermiz
3564 Posts |
Posted - 2003-09-17 : 11:34:37
|
| Doesnt your insert stmnt have to return the identity right inside of the INSERT clause? Also why dont you use a SPROC?CREATE PROC insert_proposal_set @ActiveRevLevel integerASINSERT INTO ProposalSet(ActiveRevLevel) VALUES(@ActiveRevLevel)RETURN @@IDENTITYGO |
 |
|
|
jhermiz
3564 Posts |
Posted - 2003-09-17 : 12:28:05
|
| I got your email...Here's the SPROC:CREATE PROC insert_session @SessionID bigint, @SessionDate datetime, @IPAddress varchar(50)ASINSERT INTO Session(SurveyId, SessionDate, IPAddress) VALUES (@SessionID, @SessionDate, @IPAddress)Return @@IDENTITYGOThanks,Jon |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-09-17 : 16:40:36
|
| You should be returning Scope_Identity() rather than @@Identity.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
jhermiz
3564 Posts |
Posted - 2003-09-18 : 11:58:30
|
quote:
Originally posted by chadmat
You should be returning Scope_Identity() rather than @@Identity.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime.
Why? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-18 : 12:14:30
|
| @@IDENTITY returns the last identity value set. if you append a row to table "A" with an identity, but that append causes a trigger which adds a row to table "B" which ALSO has an identity column, it returns the identity from table "B". Which is probably not what you want.SCOPE_IDENTITY always returns the identity from the current table and never from a trigger.- Jeff |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-09-18 : 12:16:04
|
| Just to avoid getting the identity value of another table, that was accessed within a trigger. For example:You insert a row into T1, an insert trigger fires on T1, and inserts a row in T2. Assuming both T1 and T2 have identity columns, @@IDENTITY will return T2's identity value and SCOPE_IDENTITY() will return T1's identity value.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
jhermiz
3564 Posts |
Posted - 2003-09-18 : 12:44:58
|
quote:
Originally posted by jsmith8858
@@IDENTITY returns the last identity value set. if you append a row to table "A" with an identity, but that append causes a trigger which adds a row to table "B" which ALSO has an identity column, it returns the identity from table "B". Which is probably not what you want.SCOPE_IDENTITY always returns the identity from the current table and never from a trigger.- Jeff
What if you dont have a trigger? |
|
|
|
jhermiz
3564 Posts |
Posted - 2003-09-18 : 12:54:50
|
| I also thought most people were avoiding triggers and sticking to referential integrity?No ? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-18 : 13:08:11
|
quote:
Originally posted by jhermiz
I also thought most people were avoiding triggers and sticking to referential integrity?No ?
I think that was a 6.5 thing where you had no choice...Triggers do a lot of different things...history is one of my primaryBrett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-09-18 : 13:51:51
|
quote:
Originally posted by jhermiz
What if you dont have a trigger?
You should still do it with Scope_Identity(). For the sake of consistency. The first time you implement this type of system with @@Identity, and someone puts a trigger on your table, and you spend a week trying to figure out why everything is all wrong, you will know what I mean.Why would you want to risk using @@Identity with the knowledge that it could possibly give inaccurate results? -Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-09-18 : 13:54:16
|
| Also, without any knowledge of jrlanders' database, you may have given him a very wrong solution. Another reason why it should be Scope_Identity.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
jhermiz
3564 Posts |
Posted - 2003-09-18 : 14:18:51
|
| *shrug*Wrong is so heavy...I dont use triggers...Whose to say its wrong? Yes if a trigger is added and IF that other table has an IDENTITY column than it fails..but thats IF it has an identity column. From his e-mail which I received his table was a general table to add session data, there was no trigger, and may never be one. So the solution is not WRONG (which you have said), the solution is not as GOOD..is the correct terminology.So before you go on a rant pointing fingers maybe you need to sit back..you'll get a lot more respect that way. That is the original reason why I asked why...Jon |
|
|
|
jhermiz
3564 Posts |
Posted - 2003-09-18 : 14:25:05
|
| Brett,Is there more overhead using Scope_Identity(). Also when would it be a good idea to just use @@IDENTITY? It seems to me if this were the case that Scope_Identity() is always used...No?Thanks,Jon |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-18 : 14:39:43
|
| jhermitz :Using scope_identity() is like doing error checking. You don't have to do it. No one is forcing you, and your code may just run fine if you don't do it. But when an error occurs ....A good programmer learns that you need to handle all possible situations, and you must anticipate the future and all things that can happen which you may have no control over. Such as a file being deleted that your app depends on, or someone adding a trigger to a table for whatever reason.@@IDENTITY is my opinion was not implemented correctly in SQL Server (in fact, i think this "trigger effect" is commonly classified as a bug), which is why they introduced scope_identity() in 2000. But of course, for things to be consistant from version to version, they left the behavoir of @@IDENTITY as is.- Jeff |
|
|
|
jhermiz
3564 Posts |
Posted - 2003-09-18 : 14:42:38
|
| Understandable...I still dont think its wrong...its only wrong IF those cases were true.However, back to my original question...does that mean @@IDENTITY has no use anymore?Jon |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-18 : 15:00:35
|
Acutally, here's a key thing you wrote:quote:
I still dont think its wrong...its only wrong IF those cases were true.
And that's the problem ... because the correct statement might be:I still dont think its wrong...its only wrong IF those cases BECOME true.And that is the part you have no control over, and that's what I was saying a good programmer will try to anticipate and plan for.I would avoid @@IDENTITY. it's poorly implemented, stick with what works in ALL cases.- Jeff |
|
|
|
jhermiz
3564 Posts |
Posted - 2003-09-18 : 15:05:09
|
| UnderstandableThanks for the info.Jon |
|
|
|
jrlanders
Starting Member
15 Posts |
Posted - 2003-09-18 : 15:37:32
|
Great responses from all of you... Jon thank you for the initial assistance... that got the code working... I will now go back as switch the Identity to Scope_Identity()Thanks a bunch James |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-09-18 : 15:50:34
|
quote:
Originally posted by jhermiz
*shrug*Wrong is so heavy...I dont use triggers...Whose to say its wrong? Yes if a trigger is added and IF that other table has an IDENTITY column than it fails..but thats IF it has an identity column. From his e-mail which I received his table was a general table to add session data, there was no trigger, and may never be one. So the solution is not WRONG (which you have said), the solution is not as GOOD..is the correct terminology.So before you go on a rant pointing fingers maybe you need to sit back..you'll get a lot more respect that way. That is the original reason why I asked why...Jon
Calm down. I am not pointing fingers, I am trying to help you understand why you should suggest Scope_Identity() rather than @@Identity. I didn't claim that your answer WAS wrong, I said that since you have limited knowledge of the database that the question was asked about, there is potential for it to be wrong. You are correct, @@Identity, will work 95% of the time without a problem, I am just trying to help you, and anyone else who reads this, avoid the situation when it doesn't.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-18 : 16:08:08
|
| Ahhh...backward compatability...since when was MS so concerned..I guess with their flagship product..."If it ain't broke...don't fix it...unless of course it breaks...then fix it...unless you don't know it broke, which is highly likely, until it becomes a MAJOR problem...then try to fix it or pump gas"Murphys...I mean Bill's LawBrett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
Next Page
|
Select @@Identity