jaydwest
asked on
CREATE NZ FUNCTION WITH VALUE OF NOTHING
I wated to create an NZ Function similar to MS Access' NZ Function. A sample is attached. It returns a second parameter value if the the first parameter is null. Since there is not null variant in VB.net, I overloaded the function. For example Double, Double, DbNull, Double, String, Double ... on and on.
It has worked well until now. I pass the first variable to the function and it has a value of nothing. VB.net throws a fit because it cannot find the overloaded function to match the parameters. THere is no nothing data type.
HOw can I handle this?
THanks for your help
It has worked well until now. I pass the first variable to the function and it has a value of nothing. VB.net throws a fit because it cannot find the overloaded function to match the parameters. THere is no nothing data type.
HOw can I handle this?
THanks for your help
Public Function NZ(ByVal pasTestValue As DBNull, ByVal pasNullReturnValue As Double) As Double
Return pasNullReturnValue
End Function
Public Function NZ(ByVal pasTestValue As String, ByVal pasNullReturnValue As String) As String
'Overloaded for String
Try
If pasTestValue = "" then
Return pasNullReturnValue
Else
Return pasTestValue
End If
Catch ex As Exception
Return pasNullReturnValue
End Try
End Function
Last Comment
It seems like you are trying to re-invent the wheel. Is there any reason you cannot use the ISNULL() function?
ASKER
It sounds like you have never had experience with MS Access' NZ function. Using the NZ Function
variable1 = NZ(variable1, variable2)
you can replace a variable with another variable if the first variable is null. It is a quick and handy way to do this task which I find I do hundreds of times in a typical application.
Another benefit of using it in VB.net is that you can overload it and it will automatically Cast the return variable with the need to explicitly cast the result.
But it won't work unless there is a way to allow variable1 to be nothing.
I haven't been able to find the silver bullet. Another shortcoming of VB.net. Perhaps you can explain why VB.net does not support a variant data type . It seems a step backwards to me.
variable1 = NZ(variable1, variable2)
you can replace a variable with another variable if the first variable is null. It is a quick and handy way to do this task which I find I do hundreds of times in a typical application.
Another benefit of using it in VB.net is that you can overload it and it will automatically Cast the return variable with the need to explicitly cast the result.
But it won't work unless there is a way to allow variable1 to be nothing.
I haven't been able to find the silver bullet. Another shortcoming of VB.net. Perhaps you can explain why VB.net does not support a variant data type . It seems a step backwards to me.
Public Function NZ(ByVal S As Object, ByVal Def as Object) As Object
If IsDBNull(S) Return Def Else Return S
End Function
If IsDBNull(S) Return Def Else Return S
End Function
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS 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.
>>It sounds like you have never had experience with MS Access' NZ function.<<
It sounds like you have never had experience with the T-SQL IsNull function.
From BOL:
ISNULL
Replaces NULL with the specified replacement value.
Syntax
ISNULL ( check_expression , replacement_value )
Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
Return Types
Returns the same type as check_expression.
Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.
But perhaps you inadvertently posted in the MS SQL Server Zone, thinking that the SQL dialect for MS Access was identical to the one used by T-SQL.
It sounds like you have never had experience with the T-SQL IsNull function.
From BOL:
ISNULL
Replaces NULL with the specified replacement value.
Syntax
ISNULL ( check_expression , replacement_value )
Arguments
check_expression
Is the expression to be checked for NULL. check_expression can be of any type.
replacement_value
Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.
Return Types
Returns the same type as check_expression.
Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.
But perhaps you inadvertently posted in the MS SQL Server Zone, thinking that the SQL dialect for MS Access was identical to the one used by T-SQL.
ASKER
Perhaps you did not read my original post. I'm coding in VB.net 2005. As far as I can see there is no native ISNULL() function in VB.net 2005. Please tell me how I can get it. That is exactly what I have been looking for.
Since you had posted in the MS SQL Server 2005 Zone I assumed you were using MS SQL Server 2005 and needed the equivalent of MS Access Nz() function using T-SQL which is IsNull(). Since that is not the case, please ignore my comments.
.NET Programming
The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.
137K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
I would try to define one method as (see snippet)
then you can check for the type , user switch/case etc.
Open in new window