468,241 Members | 1,634 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,241 developers. It's quick & easy.

Problem with stored procedure and retrieving inserted identity value

Hello!

I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.

My Procedure:

create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

My C# code:
SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@NEWID",
System.Data.SqlDbType.Int)).Direction =
System.Data.ParameterDirection.Output;

try
{
SqlDataReader sqlRead = comm.ExecuteReader();
object o = comm.Parameters["@NEWID"].Value;
//...
}
catch ( Exception ex ){ throw ex;}

The object o is alwaya System.DbNull. I also tried to use bigint.

Any hints are welcome

Ciao
Susanne
Jul 11 '06 #1
3 2585
Susanne Klemm (sk****@gmx.de) writes:
I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.
There is nothing magic around 700000. Overall, everything you have is 32-bit
integer, so there should be no reason for a clash.
create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end
In this case, it's better to use scope_identity(), which returns the most
recently generated identity value in the current scope. If PRODUCT has a
trigger which inserts data into a table with another IDENTITY column,
@@identity will return the value for that latter table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 11 '06 #2
Erland Sommarskog wrote:
Susanne Klemm (sk****@gmx.de) writes:
create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

In this case, it's better to use scope_identity(), which returns the most
recently generated identity value in the current scope. If PRODUCT has a
trigger which inserts data into a table with another IDENTITY column,
@@identity will return the value for that latter table.
I tried scope_identity() but got no changes. But indeed there was a
trigger on the product table. After removing it both variants worked.

Thank you!
Susanne

Jul 11 '06 #3
rmk
select @NEWID = @@IDENTITY

should be changed to

SET @NEWID = SCOPE_IDENTITY()
"Susanne Klemm" <sk****@gmx.dewrote in message
news:44*********************@reader.news.celox.de. ..
Hello!

I use a procedure to insert a new row into a table with an identity
column. The procedure has an output parameter which gives me the
inserted identity value. This worked well for a long time. Now the
identity value is over 700.000 and I get errors whiles retrieving the
inserted identitiy value. If I delete rows and reset the identity
everything works well again. So I think it is a data type problem.

My Procedure:

create procedure InsertProduct
@NEWID int output
as
begin
set nocount on
insert into PRODUCT(D_CREATED)values(getdate()+'')
set nocount off
select @NEWID = @@IDENTITY
end

My C# code:
SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@NEWID",
System.Data.SqlDbType.Int)).Direction =
System.Data.ParameterDirection.Output;

try
{
SqlDataReader sqlRead = comm.ExecuteReader();
object o = comm.Parameters["@NEWID"].Value;
//...
}
catch ( Exception ex ){ throw ex;}

The object o is alwaya System.DbNull. I also tried to use bigint.

Any hints are welcome

Ciao
Susanne

Jul 11 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Tavish Muldoon | last post: by
3 posts views Thread by Jason Callas | last post: by
3 posts views Thread by DarthMacgyver | last post: by
4 posts views Thread by Newbie | last post: by
6 posts views Thread by Leon Shaw | last post: by
6 posts views Thread by rn5a | last post: by
8 posts views Thread by Tom P. | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.