Newbie question

Hi all,
I need an autonumber field in a table.
I set the following parameters for the field:
data type - int,
identity - yes (not for replication)
identity seed - 1
identity increment - 1
Is this the right thing to do?
TIA
CSharpHi,
You are right.
Thanks
Hri
MCDBA
"CSharp" <smitha@.asianetindia.com> wrote in message
news:OMXoyYuFEHA.3180@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I need an autonumber field in a table.
> I set the following parameters for the field:
> data type - int,
> identity - yes (not for replication)
> identity seed - 1
> identity increment - 1
> Is this the right thing to do?
> TIA
> CSharp
>

newbie needs help with @@identity

I have a form that submits to multiple tables. After insertion into the first table I need to access the identity key from the record and use is to associate a record in another table. The form element I'm inserting into the second table however, is not a required field so I think I need to check IS NOT NULL first. In my code below I have copied the insert statement for the first table and the conditional and subsequent insert into the 2nd table. I am uncertain where and how I get and use @.@.identity. The error I'm getting when I run the Check Syntax button is: 'incorrect syntax near @.@.identity.'

I appreciate someone telling me how to correct my syntax.

INSERT INTO GPRA_Activities
(
SubmitDate,
StaffId,
GPRAId,
FreedomID,
DocumentDesc,
ActivityTitle,
ActivityDesc

)
VALUES
(
getDate(),
@.StaffId,
@.GPRAId,
@.FreedomID,
@.DocumentDesc,
@.ActivityTitle,
@.ActivityDesc

SELECT @.@.identity
)

if @.KeywordId1 IS NOT NULL

@.@.identity smallint,

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.@.identity,
@.KeywordId1
)

GO

You need to get the value of @.@.IDENTITY Into a local variable and use it. You cannot use the @.@.IDENTITY by itself.

Declare @.valintINSERT INTO GPRA_Activities(SubmitDate,StaffId,GPRAId,FreedomID,DocumentDesc,ActivityTitle,ActivityDesc)VALUES (getDate(),@.StaffId,@.GPRAId,@.FreedomID,@.DocumentDesc,@.ActivityTitle,@.ActivityDesc)SELECT @.val = SCOPE_IDENTITY()if @.KeywordId1ISNOT NULL-- @.@.identity smallint, I dont know what you are trying to do hereINSERT INTO GPRA_KeywordsUsed ( ActivityId, KeywordId )VALUES ( @.val, @.KeywordId1 )GO

|||

I finally got the SQL code below not to error (though I haven't been able to submit my form yet. Keep getting error message about expected number of parameters. That one will be my nemesis.

What is the difference between @.@.identy and SCOPE_IDENTITY?

DECLARE
@.ActivityId smallint
SELECT @.ActivityId = @.@.Identity

if @.KeywordId1 IS NOT NULL

INSERT INTO GPRA_KeywordsUsed
(
ActivityId,
KeywordId
)
VALUES
(
@.ActivityId,
@.KeywordId1
)

|||

SCOPE_IDENTITY and @.@.IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

|||

I'm new at this so please forgive my ignorance.

So, if I use @.@.identity and there are multiple users of the application at once, could the wrong identity get "grabbed"?

|||

Possible. HEre's some info from Books on line:

For example, there are two tables,T1 andT2, and an INSERT trigger is defined onT1. When a row is inserted toT1, the trigger fires and inserts a row inT2. This scenario illustrates two scopes: the insert onT1, and the insert onT2 by the trigger.

Assuming that bothT1 andT2 have identity columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement onT1. @.@.IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted inT2. SCOPE_IDENTITY() will return the IDENTITY value inserted inT1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

|||thank you. I'll change it.

Newbie - What is the CLR Keyword/Method for @@IDENTITY - thanks!

What is the CLR Keyword/Method for @.@.IDENTITY? I'm doing some master detail inserts and when doing the insert for the child records, i need the new key that was generated for the parent records. Thanks!
Think I may have found the answer at the following site,

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

It basically says that on your insert statement to append the following statement onto the end of the insert statement:

"SET @.MyParameter = SCOPE_IDENTITY()"

And then on your command object to pass in a parameter with the name of @.MyParameter (with Direction set to output) and then access the parameter after executing the command. Haven't tried it yet but hopefully it works. Is this correct as far you guys know? Is there a better way, lemme know, thanks!