Stored Pocedure Error 1

[mspelly]

Hi all,

I have a stored procedure that I am returning a value.However, when I run the following code I receive the following errors:

Error 9
Description: An SQLParameter with ParameterName '@Error'
is not contained by this SQLParameterCollection.

and

Error 5
Description: Procedure 'usp_CheckForDuplication' expects parameter @CountID, which is not supplied.

Stored Procedure:-
ALTER PROCEDURE [dbo].[usp_CheckForDuplication]
@CheckID TINYINT,
@TechTypeAbbv VARCHAR(5),
@Count INT OUTPUT
AS
Declare @Error AS INT

IF @CheckID = 1 --tb1TechnologyType
BEGIN
SELECT @Count = COUNT(TechAbbv)
FROM tb1TechnologyType
WHERE TechAbbv = @TechTypeAbbv
SET @Error = @@ERROR
RETURN @Error
END

VB Code:

Public Function TST_fnCheckForDuplication(ByVal strStoredProcedureName As String, ByVal iCheckID As Integer, Optional ByVal strTechAbbv As String = vbNullString) As Integer
Dim cmd As SqlCommand
Dim prm As SqlParameter
Dim iCount As Integer
Try
cn = New SqlConnection(gstrDatabaseConnection)
'create the command object and pass the procedure name and connection string
cmd = New SqlCommand(strStoredProcedureName, cn)
cmd.CommandType = CommandType.StoredProcedure
prm = New SqlParameter("@CheckID", SqlDbType.Int, ParameterDirection.Input)
cmd.Parameters.Add(prm).Value = iCheckID
prm = New SqlParameter("@TechTypeAbbv", SqlDbType.VarChar, 5, ParameterDirection.Input)
cmd.Parameters.Add(prm).Value = strTechAbbv
prm = New SqlParameter("@Count", SqlDbType.Int, ParameterDirection.Output)
cmd.Parameters.Add(prm)
cmd.Parameters("@Error").Direction = ParameterDirection.ReturnValue
Call TST_fnOpen_Database()
cmd.ExecuteNonQuery()
If cmd.Parameters("@Error").Value = 0 Then
iCount = CInt(cmd.Parameters("@Count").Value)
Call TST_fnClose_Database()
Return iCount
Else
Call TST_fnErrHandler("mDatabase", "TST_fnCheckForDuplication")
End If
Catch ex As Exception
Call TST_fnErrHandler("mDatabase", "TST_fnCheckForDuplication")
End Try
End Function
Any answer greatly appreciated

Thanks
mspelly

 

[RiverGuy]

One way is to make @Error an output parameter.

ALTER PROCEDURE [dbo].[usp_CheckForDuplication]
    @CheckID TINYINT,
    @TechTypeAbbv VARCHAR(5),
    @Count INT OUTPUT,
    @Error INT OUTPUT
AS

    IF @CheckID  = 1    --tb1TechnologyType
    BEGIN
      SELECT @Count = COUNT(TechAbbv)
          FROM tb1TechnologyType
          WHERE TechAbbv = @TechTypeAbbv
      SET @Error = @@ERROR
      RETURN @Error
    END

 

[mspelly]

Thanks for reply RiverGuy.

But when I set the @Error as an output and then set up my VB code to set the @Error parameter I get the error message

Error 5: Procedure 'usp_CheckForDuplication' expects parameter @Error, which is not supplied.

I tried setting the value of the @Error as follows:
prm = New SqlParameter("@Error", SqlDbType.Int, ParameterDirection.Output)
cmd.Parameters.Add(prm).Value = System.DBNull.Value

But I get nothing back.

When I run the stored procedure in the SQL pane it works by setting the @Error and the @CountID output values to null.

Any Ideas - Thanks

 

[pmegan]

I don't see that format as an SqlParameter constructor. It looks like you may be passing the Direction as a size argument.

Try putting the parameter direction on its own line and getting rid of the DBNull.Value assignment. Something like this:

prm = New SqlParameter("@Error", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)

 

[mspelly]

Hi pmegan

Thank you very much - that worked and now see my mistake