Posted by Susanne Klemm on 07/11/06 12:55

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

[Back to original message]