Calling stored procedure with an output variable

Hi, I'm trying to call a stored procedure that takes an input variable and returns an output variable.  Here's what I've got:

       Dim SQL_CONNECTION_STRING As String = "Server=ga1cdc02;DataBase=Production1;Integrated Security=SSPI"

        Dim conn As New SqlClient.SqlConnection(SQL_CONNECTION_STRING)
        Dim cmd As New SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader
        Dim returnVal As Integer

        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "{? = CALL PTS_ChkStatus(?,?)}"

        'Send in parameters
        cmd.Parameters.Add(New SqlClient.SqlParameter("@ip_ContID", SqlDbType.VarChar)).Value = txtContID.Text
        cmd.Parameters("@op_Status").Direction = ParameterDirection.Output

        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()

            reader = cmd.ExecuteReader()

            'Analyze Results
            If reader.Read Then
                returnVal = reader("@op_status")
            End If

            reader.Close()
            cmd.Connection.Close()
        Catch ex As SqlClient.SqlException
            lblInfo.Text = "SQL Error 101 - Contact IT"
            'MessageBox.Show(ex.Message, "SQL Error")
        End Try

        If returnVal = 1 Then
            lblInfo.Text = "Process is already started."
            Exit Sub
        End If

        lblInfo.Text = Str(returnVal)


These lines are new:
        cmd.CommandText = "{? = CALL PTS_ChkStatus(?,?)}"
        cmd.Parameters("@op_Status").Direction = ParameterDirection.Output

I was told that instead of cmd.CommandText = "PTS_ChkStatus" wouldn't work.  Also they told me to use the second line to define the direction of the output variable.  He programs in C# though so I don't know if this applies.

JP
gleznovAsked:
Who is Participating?
 
b1xml2Connect With a Mentor Commented:
returnVal = DirectCast(reader("@op_status").Value, Integer) <--- wrong

returnVal = DirectCast(cmd.Parameters("@op_status).Value,Integer)

0
 
gleznovAuthor Commented:
OK I made a couple little changes that seem to give me a result, but unfortunately I'm still getting a 0 returned when the correct value should be 1.  I've tested the stored procedure for this item and gotten a 1 in sql query analyzer, so I know it works.

        cmd.Connection = conn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "{? = CALL PTS_ChkStatus(?,?)}"

        'Send in parameters
        cmd.Parameters.Add(New SqlClient.SqlParameter("@ip_ContID", SqlDbType.VarChar)).Value = txtContID.Text
        cmd.Parameters.Add(New SqlClient.SqlParameter("@op_Status", SqlDbType.VarChar)).Direction = ParameterDirection.Output
        cmd.Parameters("@op_status").Size = 3

Those are the changes.

JP
0
 
b1xml2Commented:
if the CommandType is set to CommandType.StoredProcedure, then you can use the name of the stored procedure:

cmd.CommandText = "PTS_ChkStatus"
cmd.CommandType = CommandType.StoredProcedure
'the first parameter is always @RETURN_VALUE
cmd.Parameters.Add("@RETURN_VALUE",SqlDbType.Int,4)
cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add("@ip_ContID",SqlDbType.VarChar).Value = txtContID.Text
cmd.Parameters.Add("@op_Status",SqlDbType.Int,4)
cmd.Paremeters(2).Direction = ParameterDirection.Output


Try
      cmd.Connection.Open()
      cmd.ExecuteNonQuery()
      returnVal = DirectCast(reader("@op_status").Value,Integer)
      
Catch ex As SqlClient.SqlException
      lblInfo.Text = "SQL Error 101 - Contact IT"
      'MessageBox.Show(ex.Message, "SQL Error")
Finally
      cmd.Connection.Close()
End Try







0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
b1xml2Commented:
By the way, there's no difference in using ADO.NET in C# and VB.NET. This is a .NET Framework issue and not a language-specific implementation.
0
 
gleznovAuthor Commented:
I'm getting an "Object reference not set to an instance of an object" - here's what I've got now:

        Dim SQL_CONNECTION_STRING As String = "Server=ga1cdc02;DataBase=Production1;Integrated Security=SSPI"

        Dim conn As New SqlClient.SqlConnection(SQL_CONNECTION_STRING)
        Dim cmd As New SqlClient.SqlCommand
        Dim reader As SqlClient.SqlDataReader
        Dim returnVal As Integer

        cmd.Connection = conn
        cmd.CommandText = "PTS_ChkStatus"
        cmd.CommandType = CommandType.StoredProcedure
        'the first parameter is always @RETURN_VALUE
        cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4)
        cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
        cmd.Parameters.Add("@ip_ContID", SqlDbType.VarChar).Value = txtContID.Text
        cmd.Parameters.Add("@op_Status", SqlDbType.Int, 4)
        cmd.Parameters(2).Direction = ParameterDirection.Output

        Try
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            returnVal = DirectCast(reader("@op_status").Value, Integer)

        Catch ex As SqlClient.SqlException
            lblInfo.Text = "SQL Error 101 - Contact IT"
            'MessageBox.Show(ex.Message, "SQL Error")
        Finally
            cmd.Connection.Close()
        End Try

        lblInfo.Text = Str(returnVal)

what is @RETURN_VALUE and why doesn't it correspond later to my returnVal variable where I store the reader("@op_status")?  (op = output)

JP
0
 
gleznovAuthor Commented:
Just for a quick breakdown, the stored procedure:

PTS_ChkStatus

takes in one parameter: @ip_ContID which is text from a textbox, and returns one parameter, @op_Status, which I try to set to a label.text to view.  

JP
0
 
b1xml2Commented:
I was copying and pasting and changing your oiriginal code..that slipped past me
0
 
gleznovAuthor Commented:
You rock dude :)

JP
0
All Courses

From novice to tech pro — start learning today.