Testing for Null in Stored Proc Return Value

eharford
eharford used Ask the Experts™
on
I have a Function which might return a Null Value. As you can see I am using vbNull which is failing "Invalid use of Null".
How should I test for this?
Private Function CreInvData(ByVal sStoredProcName As String) As Long
Dim oCmd As New ADODB.Command
Dim oParam As ADODB.Parameter
Set oCmd.ActiveConnection = invConn
oCmd.CommandText = sStoredProcName
oCmd.CommandType = adCmdStoredProc
oCmd.Parameters.Append oCmd.CreateParameter("clCode", adVarChar, adParamInput, 4, sClCode)
oCmd.Parameters.Append oCmd.CreateParameter("chgRate", adInteger, adParamInput, , i)
oCmd.Parameters.Append oCmd.CreateParameter("clStRate", adCurrency, adParamInput, , clStRate)
oCmd.Parameters.Append oCmd.CreateParameter("W", adVarChar, adParamInput, 1, W)
oCmd.Parameters.Append oCmd.CreateParameter("Total", adCurrency, adParamReturnValue)
oCmd.Execute
If oCmd.Parameters("Total") = vbNull Then
    total = 0
Else
    total = oCmd.Parameters("Total")
End If

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
sammySeltzer
Top Expert 2011

Commented:
Why not handle this inside your stored procedure?

Something like:

SELECT ISNULL(Total, 0) AS Total

Open in new window


Total gets 0 if it is null.

The way you are using it, you are treating NULL as a value and it is not.
eharford

Author

Commented:
My Stored Proc is as follows how would I phrase it?
ALTER PROCEDURE [dbo].[zInvCount]
		@clCode nvarchar(4),
		@chgRate int,
		@clStRate float,
		@W nvarchar(1),
		@Val float OUTPUT
AS
BEGIN
 SELECT @Val = SUM((Depth * Height * Width)/100000) * @clStRate from PartInvent WHERE clCode = @clCode and chgRate = @chgRate and WIssued = 0 and Left(Plocation,1) = @W 
	SET NOCOUNT ON;
END

Open in new window

Top Expert 2011
Commented:
I don't see where you are using Total in your stored proc.

So, try this in your original post


If oCmd.Parameters("Total") = System.DbNull.Value Then
    total = 0
Else
    total = oCmd.Parameters("Total")

Open in new window


Sql server understands that better
eharford

Author

Commented:
I am naming "Total" when creating the Return Value parameter:
oCmd.Parameters.Append oCmd.CreateParameter("Total", adCurrency, adParamReturnValue)
The value coming back is Null (or a Currency Value).
As to your suggestion I am using vb6 and I don't think System.dbNull.Value is vb6 syntax. I would have thought vbNull would work but it doesn't for some reason. Is it perhaps the adCurrency datatype I am using?
Thanks for your help.
eharford

Author

Commented:
I have worked it out in Stored Proc. Thanks for your suggestion.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial