VB.Net & SQL2005 Stored Procedures

10 Mar 2006 10:39 AM

Peter Newman

i have a simple application that is calling a storedprocedure from a SQL 2005
server. It executes the stored procdure fine, however when i added a @RTN
INT OUTPUT to my stored procedure and tried to get the result , i fell into
all sorts of trouble

        Dim objSQLConn As New SqlConnection("SERVER=.; UID=BossSystem;
PWD=AE93lo175; DATABASE=BossData;")
        Dim SQLCMD As SqlClient.SqlCommand
        SQLCMD = New SqlClient.SqlCommand
        SQLCMD.CommandType = CommandType.StoredProcedure
        SQLCMD.CommandText = "Bossdata.dbo.VBNET_XML_ARUCSAS"
        SQLCMD.Connection = objSQLConn
'---------------------------------------
' New code Added to get return value
        Dim myParm As SqlParameter = SQLCMD.Parameters.Add("@RTN",
SqlDbType.Int)
        myParm.Direction = ParameterDirection.ReturnValue
'---------------------------------------
        Dim Reader As SqlClient.SqlDataReader
        objSQLConn.Open()
        Reader = SQLCMD.ExecuteReader
        ' Insert code to read through the datareader.
        Reader.Close()
        objSQLConn.Close()

when exacuting this the application fails with

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.

yet if i remove the output parameter from the stored proc and remove the two
lines of code from my vb application it works fine... I need to get a return
code from the stored procedure ..

Thank you in advance for any help

10 Mar 2006 9:35 PM

EC

I think you want ParameterDirection.Output instead of
ParameterDirection.ReturnValue

ReturnValue is usually used for Rows Affected, you're trying to capture
an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

Peter Newman wrote:
Show quoteHide quote

> i have a simple application that is calling a storedprocedure from a SQL 2005
> server. It executes the stored procdure fine, however when i added a @RTN
> INT OUTPUT to my stored procedure and tried to get the result , i fell into
> all sorts of trouble
>
>         Dim objSQLConn As New SqlConnection("SERVER=.; UID=BossSystem;
> PWD=AE93lo175; DATABASE=BossData;")
>         Dim SQLCMD As SqlClient.SqlCommand
>         SQLCMD = New SqlClient.SqlCommand
>         SQLCMD.CommandType = CommandType.StoredProcedure
>         SQLCMD.CommandText = "Bossdata.dbo.VBNET_XML_ARUCSAS"
>         SQLCMD.Connection = objSQLConn
> '---------------------------------------
> ' New code Added to get return value
>         Dim myParm As SqlParameter = SQLCMD.Parameters.Add("@RTN",
> SqlDbType.Int)
>         myParm.Direction = ParameterDirection.ReturnValue
> '---------------------------------------
>         Dim Reader As SqlClient.SqlDataReader
>         objSQLConn.Open()
>         Reader = SQLCMD.ExecuteReader
>         ' Insert code to read through the datareader.
>         Reader.Close()
>         objSQLConn.Close()
>
>
> when exacuting this the application fails with
>
> An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
> in system.data.dll
>
> Additional information: System error.
>
> yet if i remove the output parameter from the stored proc and remove the two
> lines of code from my vb application it works fine... I need to get a return
> code from the stored procedure ..
>
> Thank you in advance for any help