Tom Garth
7/4/2007 1:58:00 PM
Kerry is right on the money. It works well with stored procedures too. In the
example I give below, the stored procedure returns the new identity value.
However the example will retrieve whatever return value is coded into the
stored procedure, so it's utility isn't just for new identies.
cmd.CommandText = ""DECLARE @RETURN int; EXEC @RETURN = CustInsert 'John
Smith', '3/15/1959'; SELECT @RETURN [RETURN]"
It's actually a generic wrapper that looks like -
"DECLARE @RETURN int; EXEC @RETURN = " & s & "; SELECT @RETURN [RETURN]"
where s is the procedure with parameters built outside the function.
If you are writing tons of DB code, you don't want to have to deal with
parameter objects, since they don't offer any performance advantage anyway.
--
Tom Garth
"Kerry Moorman" wrote:
> Bill,
>
> With SQL Server, Scope_Identity is more reliable than @@Identity.
>
> One technique is to batch a Select Scope_Identity() statement with the
> Insert statement. For a command object named cmd:
>
> cmd.CommandText = "Insert Into ... ; Select Scope_Identity()"
>
> Then, instead of using ExecuteNonQuery, use ExecuteScalar. To retrieve the
> identity value into an ID variable:
>
> ID = cmd.ExecuteScalar
>
> Kerry Moorman
>
>
> "Bill Gower" wrote:
>
> > I am using a ExecuteNonQuery() to insert a record into a SQL Server
> > database. How do I retrieve the identity value of the row just inserted?
> >
> > Bill
> >
> >
> >