Calling SQL Server stored procedure from VB.NET application. Output parameter is coming back blank

Question

I am trying to call a simple stored procedure with that returns an OUTPUT parameter from a VB.NET application, but when i try to display the result, it appears blank. The code is not throwing any exceptions and when i execute the SP in SQL Server Mgmt studio, it returns as I expect. I am probably just overlooking something. Appreciate any suggestions. SP and VB code are below:

SP Code :

create procedure [dbo].[get_number_potential_matches] @deductnRecId int, @numberPotentialMatches int output as 
begin 
    select numberPotentialMatches = count(*) 
    from potential_match pm 
    where pm.deductn_rec_id = @deductnRecId;
    return
end

VB.Net Procedure:

Sub getPotentialMatchCount(ByVal deductnRecId As Integer)
    Try
        SqlCmd = New SqlCommand("get_number_potential_matches", SqlConn)
        SqlCmd.CommandType = CommandType.StoredProcedure
        SqlCmd.Parameters.Add("@deductnRecId", SqlDbType.Int).Value = deductnRecId
        SqlCmd.Parameters("@deductnRecId").Direction = ParameterDirection.Input

        SqlCmd.Parameters.Add("@numberPotentialMatches", SqlDbType.Int)
        SqlCmd.Parameters("@numberPotentialMatches").Direction = ParameterDirection.Output

        SqlConn.Open()
        SqlCmd.ExecuteNonQuery()

    Catch ex As Exception
        lbl_pm.Text = ex.Message
    Finally
        SqlCmd.Dispose()
        SqlConn.Close()
    End Try
    lbl_pm.Text = "deductnRecId: " & deductnRecId.ToString & " has " & SqlCmd.Parameters("@numberPotentialMatches").Value.ToString() & " matches"
End Sub

You are disposing the SqlCmd object in Finally, but then trying to get the value of the parameter after that. As an aside, you are better off initializing your Connection and Command objects in Using statements, which will implicitly dispose of the objects. — HardCode
– HardCode, Commented Dec 14, 2015 at 20:49

Jakub Lortz · Accepted Answer · 2015-12-14 20:56:47Z

2

You missed '@' in the parameter name in the SP. It should be

select @numberPotentialMatches = count(*) ...

What you did was selecting count(*) and setting numberPotentialMatches as column alias.

Also, don't access the SqlCmd after it's disposed.

answered Dec 14, 2015 at 20:56

Jakub Lortz

14.9k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joel Coehoorn Over a year ago

He's also using the SqlCmd object after disposing it, which is not good.

Collectives™ on Stack Overflow

Calling SQL Server stored procedure from VB.NET application. Output parameter is coming back blank

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related