Pass NULL value to stored procedure using Date property value 1

[BG12424]

I have a textbox on a form and am setting the [optional] value in this textbox. The value is a date and is set to a property. This property is then used as a parameter value for a stored procedure. Now, the date value is optional on the user form, so how can I get this date to be a null value in the database since the sql server table allows null values for this date field. Thanks

aspx file code:
dim o_cls as new classname
o_cls.StartDate = txtTheDate.Text

vb file code:
Public Property StartDate() As Date
Get
Return _startdt
End Get
Set(ByVal value As Date)
_startdt = value
End Set
End Property

regards,
Brian

 

[link9]

if not txtTheDate.Text = string.empty then
o_cls.StartDate = txtTheDate.Text
end if

Only assign it to the class if the text box has a value. Then when you execute the sproc in the class, just check to see if it has been initialized to some value.

If not, then don't assign the parameter.

In the sproc, just give the parameter a default value of NULL, and you should be off and running

The answer to getting answered -- faq855-2992

 

[BG12424]

Thank you, I do have the NULL value set in the sproc already. I will give this a go and test it out. Thanks regards,
Brian

 

[BG12424]

Ok, I did that and that got me past the step where it was bombing when I was trying to set the property value. Ok, now that I am not setting my property value due to the fact that I have no value in the textbox, I am running into another error, which is in the VB file where my property and stored proc calls are at.

I am attempting to check the value of the property that was not set due to no value supplied. What is the syntax for this check. I am really struggling in the conversion area. Thanks

Here is the code block:

This is the property that is not set because no value is supplied
Public Property StartDate() As Date
Get
Return _startdt
End Get
Set(ByVal value As Date)
_startdt = value
End Set
End Property



' Create Instance of Connection and Command Object
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("connectionString&quot)
Dim myCommand As SqlCommand = New SqlCommand("NLT_AddPipeline", myConnection)

' declarations
Dim parmReturnValue As SqlParameter = Nothing
Dim parmId As SqlParameter = Nothing

With myCommand
' set command type to be a stored procedure
.CommandType = CommandType.StoredProcedure

.Parameters.Add(New SqlParameter("@iStatus", SqlDbType.VarChar, 50)).Value = _status
.Parameters.Add(New SqlParameter("@iCompany", SqlDbType.VarChar, 100)).Value = _comp
.Parameters.Add(New SqlParameter("@iClient", SqlDbType.VarChar, 100)).Value = _client
.Parameters.Add(New SqlParameter("@iStage", SqlDbType.VarChar, 50)).Value = _stage
.Parameters.Add(New SqlParameter("@iFollowUpName", SqlDbType.VarChar, 200)).Value = _followname
.Parameters.Add(New SqlParameter("@iFollowUpNum", SqlDbType.VarChar, 50)).Value = _follownum
.Parameters.Add(New SqlParameter("@iFollowUpEmail", SqlDbType.VarChar, 200)).Value = _followemail
.Parameters.Add(New SqlParameter("@iNextMtgDt", SqlDbType.VarChar, 100)).Value = _nextmtgdt
.Parameters.Add(New SqlParameter("@iDuration", SqlDbType.VarChar, 100)).Value = _dur
.Parameters.Add(New SqlParameter("@iRate", SqlDbType.VarChar, 50)).Value = _rate
.Parameters.Add(New SqlParameter("@iTech", SqlDbType.VarChar, 200)).Value = _tech
.Parameters.Add(New SqlParameter("@iTypeOfWork", SqlDbType.VarChar, 100)).Value = _typeofwork
.Parameters.Add(New SqlParameter("@iConditions", SqlDbType.VarChar, 500)).Value = _cond
.Parameters.Add(New SqlParameter("@iWorkDetails", SqlDbType.VarChar, 8000)).Value = _workdet
.Parameters.Add(New SqlParameter("@iNextSteps", SqlDbType.VarChar, 8000)).Value = _nextstp
.Parameters.Add(New SqlParameter("@iGotWork", SqlDbType.VarChar, 50)).Value = _gotwork


If Not _followdt.ToString = String.Empty Then
.Parameters.Add(New SqlParameter("@iFollowUpDt", SqlDbType.DateTime)).Value = _followdt
End If

If Not _startdt.ToString = String.Empty Then
.Parameters.Add(New SqlParameter("@iStartDt", SqlDbType.SmallDateTime)).Value = _startdt
End If

' the return value from the stored procedure.
parmReturnValue = .Parameters.Add("ReturnValue", SqlDbType.Int)
parmReturnValue.Direction = ParameterDirection.ReturnValue

' the primary key value of the new record
parmId = .Parameters.Add(New SqlParameter("@oID", SqlDbType.Int, 4))
parmId.Direction = ParameterDirection.Output

' open connection, execute stored procedure, then close connection
Try
myConnection.Open()
.ExecuteNonQuery()
If myConnection.State = ConnectionState.Open Then
myConnection.Close()
End If
If parmReturnValue.Value = 0 Then
Return True
End If
Catch ex As Exception
Return False
End Try

End With regards,
Brian

 

[link9]

yea, I think the issue is that a date object sets itself to a default date. Maybe you should set a flag in the set operation of the property to indicate a user specified date, and then check the true/false status of that var when you assign the var:

dim userDate as boolean
~~~~~

Set(ByVal value As Date)
_startdt = value
userDate = true
End Set

~~~~~~~

If userDate Then
.Parameters.Add(New SqlParameter("@iStartDt", SqlDbType.SmallDateTime)).Value = _startdt
End If

I suppose you could also figure out what the date is that it sets itself to (12midnight 1/1/1900, I **THINK** ) and check against that if you don't want another var floating around memory (but remember that a bool var is the smallest ;-))

good luck -
paul

The answer to getting answered -- faq855-2992

 

[BG12424]

Thanks, I will apply the boolean check. good idea. keeping it simple. regards,
Brian