CODEWITHSUNDEEP
sqlvb.netstored-proceduresparameterssql-server-2008-r2
StealthRT
StealthRT

Reputation: 10552

Getting response from a stored procedure

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

Answers (2)

marc_s
marc_s

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

Mr Moose
Mr Moose

Reputation: 6344

I'd be more inclined to have your procedure return an INT and perform a ExecuteScalar to get the result.

Upvotes: 1

Related Questions