Scope_identity equal to 0 from a stored proc as we

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Scope_identity equal to 0 from a stored proc as we

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-21 : 21:11:13

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: 100000056

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-21 : 21:13:43

How about executing the stored procedure in Query Analyser ? Do you get the identity value ?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-21 : 22:06:27

It's generally bad practice to return values like this anyway. You really should be declaring @AccountNumber as an output parameter in your stored proc. Return values are generally for error codes/status codes

But if you insist on doing it this way, change line

 pm = sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber", SqlDbType.BigInt))

 pm = sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.BigInt))

Tim

ps - please don't create two threads for the same issue. It just wastes time of the people attempting to help you.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-21 : 22:08:01

And another thing:
You need to check the return value AFTER you've executed the stored proc.

ADO.NET doesn't data-bind these things as it looks like you're trying to do.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-22 : 01:21:23

Even using a stored proc and output param instead of returnValue doesn't help.
I go declaring the account number as an output like this :

Create PROCEDURE dbo.InsertAccount
@AccRisAsse bit,
@AccHldBll bit,
@Acctaxexm bit,
@AccountNumber bigint output
AS
insert into TblAccount(AccRisAsse,AccHldBll,Acctaxexm)
values(@AccRisAsse,@AccHldBll,@Acctaxexm)
set @AccountNumber=scope_identity()
return

then I doi in my code just like I did above but changing the direction to output like this:

sqlcmdInsNeAcc.type=commandtype.storedprocedure
sqlcmdInsNeAcc.CommandText = "InsertAccount"
pm = sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber", SqlDbType.BigInt))
pm.Direction = ParameterDirection.output
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

Am I doing something wrong
Thanks for help

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-06-22 : 02:31:01

Yes.

Read my second post.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-22 : 10:59:18

u re so right movingt it after executenonquery resolved the pb perfectly. am thankful timmy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources