Scope_identity equal to 0 from a stored proc as well

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.



Answer this question

Scope_identity equal to 0 from a stored proc as well

  • sud

    that helped perfectly thanks

  • 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





  • Scope_identity equal to 0 from a stored proc as well