I don't know how to use storedProcedure that retur

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

I don't know how to use storedProcedure that retur

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-05-10 : 20:55:35

My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: An SqlParameter with ParameterName 'total' is not contained by this SqlParameterCollection.

Here is my stored Proc and my VB program is right below

I- Stored Proc:

CREATE PROCEDURE dbo.totalsub
@account bigint,
@total bigint output
AS
select total=sum(SubPhnNmbr) from tblsub where SubAccNmbr=@account
return
GO
II- And my pogram in VB is:

Dim totsub As Int64
Dim cm As New SqlCommand
Dim cn As New MyConnection
cn.open
'my connection is defined by me don't worry about it
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "totalsub"
cm.Connection = cn
Dim pm As SqlParameter
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Account", System.Data.SqlDbType.BigInt))
pm.Value = 100000165
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.Output
totsub = cm.Parameters("total").Value
cm.ExecuteScalar()
totsub = cm.Parameters("total").Value
I also tried using @total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output

No Luck, someone pls can help

dfiala
Posting Yak Master

116 Posts

Posted - 2006-05-10 : 21:10:55

You're close...

pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.InputOutput

is what you need.

And change this
@total bigint output
to
@total bigint output = null

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-05-10 : 22:15:41

Cool I added the @ before total. now it seemes to be working, but I get a conversion error:
Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type int

Thanks alot for your help

dfiala
Posting Yak Master

116 Posts

Posted - 2006-05-10 : 22:25:42

try this
select @total=sum(SubPhnNmbr) from tblsub where SubAccNmbr=@account

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-05-11 : 09:15:31

that s what i m doing, may be there is a mistake in eiher the declaration of the parameter @total type or my SubPhnNmbr column type (bigint) in the database.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources