Tuesday, February 21, 2012

Scope_identity equal to 0 from a stored proc as we

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 fromsystem.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

you should move

kAccountNumber = sqlcmdInsNeAcc.Parameters("@.AccountNumber").Value

to blow the

sqlcmdInsNeAcc.executenonquery

Hope this help

|||that helped perfectly. am thankful

No comments:

Post a Comment