Identity In SQL VS Autonumber In Access

[Steve]

Hi

I wonder if anyone can help with the following:

when using an autonumber in access when you use .addnew you automatically
get the field in an autonumber field i.e.

rs.addnew
jno=autonofld
rs.update

jno will have a value. however in SQL you have to update first and then find
the record (I may be wrong) is there a way to get
the field value before doing the update in SQL Server ?

TIA
Steve

[David Portas]

Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
INSERT. Could you explain why you want the IDENTITY value before
insertion? How do you intend to use the returned value? There are some
strategies you could use, such as generating a value first and then
INSERTing it. If you want to use an IDENTITY column though I'm not sure
what benefit you would gain by knowing the value beforehand.

Serializing INSERTs isn't recommended because that approach doesn't
scale well. It shouldn't be necessary with an IDENTITY column anyway.

--
David Portas
SQL Server MVP
--

[Steve]

Thanks for the response

I need the Identity value at the time of insertion so I can display a Job
Number to the user as soon as the record is
added. The reason I used the Access example is to illustrate the retrieving
of the value before .update was to show what
I wanted to do (sorry it gave the wrong idea).

I will try the SCOPE_IDENTITY.

Thanks Again
Steve

"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
INSERT. Could you explain why you want the IDENTITY value before
insertion? How do you intend to use the returned value? There are some
strategies you could use, such as generating a value first and then
INSERTing it. If you want to use an IDENTITY column though I'm not sure
what benefit you would gain by knowing the value beforehand.

Serializing INSERTs isn't recommended because that approach doesn't
scale well. It shouldn't be necessary with an IDENTITY column anyway.

--
David Portas
SQL Server MVP
--

[Steve]

SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
forgot to mention which version of
SQL Server I am using) do you have any other suggestions ?

Steve

"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

Normally you use SCOPE_IDENTITY to return the IDENTITY value after the
INSERT. Could you explain why you want the IDENTITY value before
insertion? How do you intend to use the returned value? There are some
strategies you could use, such as generating a value first and then
INSERTing it. If you want to use an IDENTITY column though I'm not sure
what benefit you would gain by knowing the value beforehand.

Serializing INSERTs isn't recommended because that approach doesn't
scale well. It shouldn't be necessary with an IDENTITY column anyway.

--
David Portas
SQL Server MVP
--

[Madhivanan]

Steve,

Select Max(IdentityField)+1 from TableName

Madhivanan

[Erland Sommarskog]

Madhivanan (ma************@gmail.com) writes:

Select Max(IdentityField)+1 from TableName

That's not a good thing, since the value you get may not actually be
that value, if another process comes in between.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Steve (st****@ufrmsa1.uniforum.org.za) writes:

SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
forgot to mention which version of
SQL Server I am using) do you have any other suggestions ?

Use @@identity instead. If you have a trigger on the table that inserts
into a secont identity table, @@identity will have the value from that
table. This is why scope_identity() is usually recommended, since it
returns the most recently used identity value in the current scope.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Steve]

Thanks, but I have to agree with Erland

Steve
"Madhivanan" <ma************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Steve,

Select Max(IdentityField)+1 from TableName

Madhivanan

[Steve]

Thanks

I am going this route, luckily I do not need to use triggers on the table.

Steve

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Steve (st****@ufrmsa1.uniforum.org.za) writes:

SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I
forgot to mention which version of
SQL Server I am using) do you have any other suggestions ?

Use @@identity instead. If you have a trigger on the table that inserts
into a secont identity table, @@identity will have the value from that
table. This is why scope_identity() is usually recommended, since it
returns the most recently used identity value in the current scope.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp