Retrieving identity value from SQL Server

[Dave]

I have a form with a label that should show an invoice number. The invoice
number should be generated by sql Server using an autoincremented technique.
However, after reading several articles, it seems that I can only retrieve
this value after an INSERT has been done in the database. I want to find out
what this autoincremented before an insertion.--
L. A. Jones

[Peter Bromberg [C# MVP]]

Select MAX(ID) FROM MYTABLE

Assuming that ID is the IDENTITY (Autoincrement) column, this will give you
the highest number previously inserted.
--Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Dave" wrote:

I have a form with a label that should show an invoice number. The invoice
number should be generated by sql Server using an autoincremented technique.
However, after reading several articles, it seems that I can only retrieve
this value after an INSERT has been done in the database. I want to find out
what this autoincremented before an insertion.--
L. A. Jones

[Nicholas Paldino [.NET/C# MVP]]

A better way would be to use:

select @@identity

After the record is inserted. If you do the "select max" route, and you
are not in a transaction, you run the risk of getting the wrong id. With
@@identity, it will return the last identiy value produced on the
^connection^, and you don't have to worry about other values inserted into
the table.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Peter Bromberg [C# MVP]" <pb*******@yaho o.nospammin.com wrote in message
news:B8******** *************** ***********@mic rosoft.com...

Select MAX(ID) FROM MYTABLE

Assuming that ID is the IDENTITY (Autoincrement) column, this will give
you
the highest number previously inserted.
--Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Dave" wrote:

>I have a form with a label that should show an invoice number. The
invoice
number should be generated by sql Server using an autoincremented
technique.
However, after reading several articles, it seems that I can only
retrieve
this value after an INSERT has been done in the database. I want to find
out
what this autoincremented before an insertion.--
L. A. Jones

[Arne Vajhøj]

Nicholas Paldino [.NET/C# MVP] wrote:

A better way would be to use:

select @@identity

Or:

SELECT SCOPE_IDENTITY( )

After the record is inserted. If you do the "select max" route, and you
are not in a transaction, you run the risk of getting the wrong id. With
@@identity, it will return the last identiy value produced on the
^connection^, and you don't have to worry about other values inserted into
the table.

The SELECT MAX route is a disaster waiting to happen.

And transactions itself are not even enough. Transaction
isolation level needs to be set high too.

Arne

[Nicholas Paldino [.NET/C# MVP]]

Frankly, that is why I never let the database generate my ids for me. I
would rather produce them and set them myself. The problem just goes away
in that case.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Arne Vajhøj" <ar**@vajhoej.d kwrote in message
news:MyUAg.7914 5$fG3.45410@duk eread09...

Nicholas Paldino [.NET/C# MVP] wrote:

> A better way would be to use:

select @@identity

Or:

SELECT SCOPE_IDENTITY( )

> After the record is inserted. If you do the "select max" route, and
you are not in a transaction, you run the risk of getting the wrong id.
With @@identity, it will return the last identiy value produced on the
^connection^ , and you don't have to worry about other values inserted
into the table.

The SELECT MAX route is a disaster waiting to happen.

And transactions itself are not even enough. Transaction
isolation level needs to be set high too.

Arne

[Dave]

Thank you fellows. I think I'm gonna use "select SCOPE_IDENTITY( )".
--
L. A. Jones
"Arne Vajhøj" wrote:

Nicholas Paldino [.NET/C# MVP] wrote:

A better way would be to use:

select @@identity

Or:

SELECT SCOPE_IDENTITY( )

After the record is inserted. If you do the "select max" route, and you
are not in a transaction, you run the risk of getting the wrong id. With
@@identity, it will return the last identiy value produced on the
^connection^, and you don't have to worry about other values inserted into
the table.

The SELECT MAX route is a disaster waiting to happen.

And transactions itself are not even enough. Transaction
isolation level needs to be set high too.

Arne

[Peter Bromberg [C# MVP]]

Yes, but in your original post you stated that you wanted to get the value
BEFORE an insert. Actually, Nick's last response was the better one. Generate
the ID yourself and the problem goes away.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Dave" wrote:

Thank you fellows. I think I'm gonna use "select SCOPE_IDENTITY( )".
--
L. A. Jones
"Arne Vajhøj" wrote:

Nicholas Paldino [.NET/C# MVP] wrote:

A better way would be to use:
>
select @@identity

Or:

SELECT SCOPE_IDENTITY( )

After the record is inserted. If you do the "select max" route, and you
are not in a transaction, you run the risk of getting the wrong id. With
@@identity, it will return the last identiy value produced on the
^connection^, and you don't have to worry about other values inserted into
the table.

The SELECT MAX route is a disaster waiting to happen.

And transactions itself are not even enough. Transaction
isolation level needs to be set high too.

Arne

[Dave]

I now see the deficiencies, but what if I have several connections? Would
they cause a problem? Also when I use "SELECT IDENTITY_SCOPE FROM Table1", I
get several null values. The amount of null values returned is the same as
the number of rows in the table.
--
L. A. Jones
"Peter Bromberg [C# MVP]" wrote:

Yes, but in your original post you stated that you wanted to get the value
BEFORE an insert. Actually, Nick's last response was the better one. Generate
the ID yourself and the problem goes away.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Dave" wrote:

Thank you fellows. I think I'm gonna use "select SCOPE_IDENTITY( )".
--
L. A. Jones
"Arne Vajhøj" wrote:

Nicholas Paldino [.NET/C# MVP] wrote:
A better way would be to use:

select @@identity
>
Or:
>
SELECT SCOPE_IDENTITY( )
>
After the record is inserted. If you do the "select max" route, and you
are not in a transaction, you run the risk of getting the wrong id. With
@@identity, it will return the last identiy value produced on the
^connection^, and you don't have to worry about other values inserted into
the table.
>
The SELECT MAX route is a disaster waiting to happen.
>
And transactions itself are not even enough. Transaction
isolation level needs to be set high too.
>
Arne
>

[Peter Bromberg [C# MVP]]

Dave,
I'm sorry but it seems you have missed the point.
@@IDENTITY and SCOPE_IDENTITY are only available in the current scope of a
stored procedure, and ONLY after there has been a row inserted.

Once again, if you want to get the maximum value of the last IDENTITY
column, you can use the Select MAX(COLUMNNAME) I showed. If you cannot do
this or need a value BEFORE an insert is made, you need to follow Nick's
advice and consider setting up your table so that you create the value before
executing the insert, and you put the value into the table as one of the
parameters.

You can also use a GUID column (UNIQUEIDENTIFI ER) for this.

Hope that clarifies,

Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Dave" wrote:

I now see the deficiencies, but what if I have several connections? Would
they cause a problem? Also when I use "SELECT IDENTITY_SCOPE FROM Table1", I
get several null values. The amount of null values returned is the same as
the number of rows in the table.
--
L. A. Jones
"Peter Bromberg [C# MVP]" wrote:

Yes, but in your original post you stated that you wanted to get the value
BEFORE an insert. Actually, Nick's last response was the better one. Generate
the ID yourself and the problem goes away.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Dave" wrote:

Thank you fellows. I think I'm gonna use "select SCOPE_IDENTITY( )".
--
L. A. Jones
>
>
"Arne Vajhøj" wrote:
>
Nicholas Paldino [.NET/C# MVP] wrote:
A better way would be to use:
>
select @@identity

Or:

SELECT SCOPE_IDENTITY( )

After the record is inserted. If you do the "select max" route, and you
are not in a transaction, you run the risk of getting the wrong id. With
@@identity, it will return the last identiy value produced on the
^connection^, and you don't have to worry about other values inserted into
the table.

The SELECT MAX route is a disaster waiting to happen.

And transactions itself are not even enough. Transaction
isolation level needs to be set high too.

Arne