Here is my stored proc: InsertAccount
Create PROCEDURE dbo.InsertAccount
@AccRisAsse bit,
@AccHldBll bit,
@Acctaxexm bit
AS
insert into TblAccount(AccRisAsse,AccHldBll,Acctaxexm)
values(@AccRisAsse,@AccHldBll,@Acctaxexm)
return scope_identity()
GO
And here is the code in .Net to run the proc and get back the scope_identity:
I delcare my sqlCommand sqlcmdInsNeAcc and connection from
system.data.Sqlclient, I open my connection. all goes well then I do:
sqlcmdInsNeAcc.type=commandtype.storedprocedure
sqlcmdInsNeAcc.CommandText = "InsertAccount"
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber",
SqlDbType.BigInt))
pm.Direction = ParameterDirection.ReturnValue
Dim kAccountNumber as integer
kAccountNumber = sqlcmdInsNeAcc.Parameters("@AccountNumber").Value
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccRisAsse",
SqlDbType.Bit))
pm.Value = myvalue1
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccHldBll",
SqlDbType.Bit))
pm.Value = myvalue2
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@Acctaxexm",
SqlDbType.Bit))
pm.Value = myvalue3
sqlcmdInsNeAcc.executenonquery
then when I try to display the value of my returned scope identity from the variable: kAccountNumber
ALL I GET IS ZERO although my row is created in the table with the right Account number
Thanks a lot.

Scope_identity equal to 0 from a stored proc as well
sud
Thomas Wolfram
try one of the following:
1:set kaccountnumber after the execution of the query...
sqlcmdInsNeAcc.executenonquery
Dim kAccountNumber as integer
kAccountNumber = sqlcmdInsNeAcc.Parameters("@AccountNumber").Value
or 2: Since you procedure is returning your scope identity then you could try executing scalar
kAccountNumber = sqlcmdInsNeAcc.executeScalar