asked on
Testing for Null in Stored Proc Return Value
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?
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
* vb6Microsoft SQL Server
Last Comment
Edward Harford
ASKER
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I am naming "Total" when creating the Return Value parameter:
oCmd.Parameters.Append oCmd.CreateParameter("Tota
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.
oCmd.Parameters.Append oCmd.CreateParameter("Tota
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.
ASKER
I have worked it out in Stored Proc. Thanks for your suggestion.
Something like:
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.