Reputation: 10552
Hey all i am looking to recieve a response back from the SP that i call out using VB.net. This is my code for doing just that:
connection.Open()
command.CommandType = CommandType.StoredProcedure
command.CommandText = "RPT_C_S_H"
command.Parameters("@theOutput").Direction = ParameterDirection.Output 'returns 0 if bad, 1 if good
Dim dataReader As SqlDataReader = command.ExecuteReader()
So now do i just do a
dim returnedValue as integer = 0
Do While dataReader.Read()
returnedValue = dataReader(0)
Loop
in order to get the returned value?
Any help would be great! Thanks!
David
UPDATE
connection.Open()
command.CommandType = CommandType.StoredProcedure
command.CommandText = "RPT_C_S_H"
Dim sqlParReturn As System.Data.SqlClient.SqlParameter = command.Parameters.Add("@theOutput", SqlDbType.SmallInt)
sqlParReturn.Direction = ParameterDirection.ReturnValue
command.ExecuteNonQuery()
Dim returnedValue As Integer = CInt(sqlParReturn.Value)
UPDATE #2
Dim sqlParReturn1 As System.Data.SqlClient.SqlParameter = command.Parameters.Add("@Error_Code", SqlDbType.Int)
Dim sqlParReturn2 As System.Data.SqlClient.SqlParameter = command.Parameters.Add("@Err_Msg", SqlDbType.VarChar)
Dim sqlParReturn3 As System.Data.SqlClient.SqlParameter = command.Parameters.Add("@result", SqlDbType.Bit)
sqlParReturn1.Direction = ParameterDirection.Output
sqlParReturn2.Direction = ParameterDirection.Output
sqlParReturn3.Direction = ParameterDirection.Output
command.ExecuteNonQuery()
Dim returnedValue1 As String = sqlParReturn1.Value
Dim returnedValue2 As String = sqlParReturn2.Value
Dim returnedValue3 As String = sqlParReturn3.Value
The SP looks like this:
ALTER PROCEDURE [dbo].[CSS_SP_Job_Return] (
@job INT,
@UserID VARCHAR(50),
@Printer_Name VARCHAR(30),
@Error_Code int OUTPUT,
@Err_Msg varchar (1000) OUTPUT,
@result BIT OUTPUT
)
I keep getting the error: String[4] the size property has an invalid size of 0
What does that mean?
Upvotes: 0
Views: 2360
Reputation: 755207
You would do something like this to read out the value from the SQL parameter:
Do While dataReader.Read()
'' do something with your data here!
Loop
Dim value = command.Parameters("@theOutput").Value
But this will only work after you've completed reading all the rows from your data reader (assuming your stored procedure does indeed return some data using a SELECT
statement - otherwise use command.ExecuteNonQuery()
instead of the data reader)
Upvotes: 1
Reputation: 6344
I'd be more inclined to have your procedure return an INT and perform a ExecuteScalar to get the result.
Upvotes: 1