Objects, Nothings and Nulls!!!!

[Alex Stevens]

Hi All,

I'm working on my Data access class which has an .AddParamter Function.
(Code is at the end of the message).
One of the parameters of this Function is the value which should entered
into an SQLClient.SQLParameter object.

This is declared as Object because I could be accepting dates, string, ints
into the parameter.
However, I wish to be able check if the value supplied is Nothing and set it
to System.DBNull.Value, so that it is passed to the SQL server correctly as
a Null.

When I pass an int (set to Nothing) into the Value Parameter and check the
varValue object like this in the command window:

varValue = Nothing 'This evaluates to True
varValue Is Nothing 'This evaluates to False.

But I can't use "If varValue = Nothing" in the function the compiler won't
allow it - "Use the IS operator to test for object identity"
But if I use "If varValue Is Nothing" in the function it is incorrect for an
integer.

How can I get my code to correctly recognise an integer passed into an
object and test "varValue = Nothing" and recognise a object passed in and
text "varValue Is Nothing"?????

Many Thanks

Alex


***** CODE START*****
Public Function AddParameter(ByVal strParamName As String, _
ByVal sqlParamType As System.Data.SqlDbType, _
Optional ByVal sqlParamDir As System.Data.ParameterDirection =
ParameterDirection.Input, _
Optional ByVal intSize As Integer = 0, _
Optional ByVal varValue As Object = Nothing, _
Optional ByVal ConvertNothingToNull As Boolean = False) As
SqlClient.SqlParameter

Dim sqlParam As New SqlClient.SqlParameter

If varValue Is Nothing And ConvertNothingToNull Then

End If

With sqlParam
.ParameterName = strParamName
.SqlDbType = sqlParamType
.Direction = sqlParamDir
.Size = intSize
.Value = varValue
End With

m_cmdCommandSQL.Parameters.Add(sqlParam)

Return sqlParam

End Function
***** CODE END*****

 

[Cor Ligthert]

Hi Alex

I think you can do it like this sample below, but in my opinion a lot of
work to keep a variable variable, it takes as well a more time to use that
boxed variable.

However, I hope this helps?

Cor
\\\
Dim myobject As Object
if Not myobject Is Nothing Then
If TypeOf myobject Is Integer Then
If DirectCast(myobject, Integer) <> Nothing Then
'do something by instance
dim myrealinteger = CInt(myobject)
'I show you with this two different methods of the cast
End If
ElseIf TypeOf myobject Is String Then
'etc
End If
End If
///

 

[Jay B. Harlow [MVP - Outlook]]

Alex,

One of the parameters of this Function is the value which should entered
into an SQLClient.SQLParameter object.

This is declared as Object because I could be accepting dates, string, ints
into the parameter.

I would seriously consider Overloading instead of passing an Object
parameter.

Public Sub AddParamter(..., value as Date)
Public Sub AddParamter(..., value as String)
Public Sub AddParamter(..., value as Integer)
Public Sub AddParamter(...)

The last one would set the parameter to DBNull.Value, while the first three
sets the parameter to the value passed.

Note that its tricky to Overload when you have Optional parameters, I would
stick with Overloading and not use the Optional parameters. In fact I rarely
use Optional parameters, especially in cases like this. I will use Optional
parameters when its clear what the missing Optional really means...

When I pass an int (set to Nothing) into the Value Parameter and check the
varValue object like this in the command window:

When you set an Integer to Nothing, you are setting the Integer to its
default value, which is 0. Seeing 0 is a valid value for Integer, you cannot
check the object parameter for Nothing, as the Integer will never be the
Nothing reference.

Hope this helps
Jay

 

[Guest]

further to Jays comment, consider using the SqlInt32 instead of Integer as this IS nullable

hth

guy