Get Output of Stored Procedure 1

[Turpis]

I am trying to get output of my Stored Procedure. When I check the stored procedure in SQL it returns 8, but I get 0 in my VB.NET function. Confused...

my stored procedure

CREATE PROCEDURE spCofC_GetRecordCount
(
	@JobNum int,
	@TotalCount int OUTPUT
)
AS
	DECLARE @Count1 int
	SELECT @Count1 = COUNT(anCofC)
	FROM tblShipping_CofC
	WHERE CertNumber = @JobNum

	DECLARE @Count2 int
	SELECT @Count2 = COUNT(anInvTrack)
	FROM tblShipping_InvTrack
	WHERE woNum = @JobNum

	DECLARE @Count3 int
	SELECT @Count3 = COUNT(anMaterial)
	FROM tblShipping_MaterialSup
	WHERE woNum = @JobNum

	DECLARE @Count4 int
	SELECT @Count4 = COUNT(anProcessSup)
	FROM tblShipping_ProcessSup
	WHERE woNum = @JobNum

	DECLARE @Count5 int
	SELECT @Count5 = COUNT(anMaterialHeatLots)
	FROM tblShipping_MaterialHeatLots
	WHERE woNum = @JobNum

	DECLARE @Count6 int
	SELECT @Count6 = COUNT(anProcess)
	FROM tblShipping_ProcessItem
	WHERE woNum = @JobNum
	
	SELECT @TotalCount = (@Count1 + @Count2 + @Count3 + @Count4 + @Count5 + @Count6)
	RETURN @TotalCount
GO

my function

    Public Function GetRecordCount(ByVal iJob As Integer) As Integer
        Dim myConn As New SqlClient.SqlConnection
        Dim myComm As New SqlClient.SqlCommand
        myConn.ConnectionString = sConn
        myComm.Connection = myConn
        myComm.CommandType = CommandType.StoredProcedure
        myComm.CommandText = "dbo.[spCofC_GetRecordCount]"
        Dim parmJob As New SqlClient.SqlParameter("@JobNum", SqlDbType.Int)
        Dim parmTotal As New SqlClient.SqlParameter("@TotalCount", SqlDbType.Int)
        parmJob.Value = iJob
        parmTotal.Direction = ParameterDirection.Output
        myComm.Parameters.Add(parmTotal)
        myComm.Parameters.Add(parmJob)
        Try
            With myComm
                .Connection.Open()
                Dim i As Integer = .ExecuteScalar
                .Connection.Close()
                Return i
            End With
        Catch ex As Exception
            Return -1
        End Try
    End Function

Can anybody tell me what I have done wrong. This is my first time to try and use an OUTPUT in a Stored Procedure.


Charles
Quality Assurance/Developer

 

[jbenson001]

First change .ExecuteScalar to .ExecuteNonQuery
Remove the assignment to a variable

Change
Dim i as Integer - .ExecuteScalar
To
.ExecuteNonQuery

Second
Dim i as Integer
i = sqlComm.Parameters("@parmTotal").Value


so you need:

dim i as Integer (somewhere in your sub)
.ExecuteNonQuery
i = sqlComm.Parameters("@parmTotal").Value
[code]

Hope this helps..

Jim

 

[Turpis]

Thank you Jim, I spent hours looking for an example in VB of how to get that output and never found anything concrete. That worked perfectly.

Thank you again!!

Charles
Quality Assurance/Developer

 

[jbenson001]

You are welcome. The problem you had was the ExecuteScalar returns only one value, the first col of the first row, so in your case always 0 since there was no resultset.

Glad you got it...

Jim