Parameter of type 'Date' with no input value RRS feed

  • Question

  • User-1323270716 posted
    The SQLHelper AttachParameters method will assign a value of DBNull to any parameters to which the user did not provide for an input value (e.g. 'p.value = nothing' ). This works fine with parameters of type String. My problem is with parameters of type 'Date' where I'm not able to set the parameter value to 'nothing'. (e.g. my assignment: '_endDate = nothing' results in: '_endDate = "#12:00:00 AM#)'. When the parameter with this value is passed to the SQLHelper , the Server Error 'SqlDateTime overflow' is triggered. Can anyone explain where this "#12:00:00 AM#" value comes from and how I can get rid of it? Thanks for your help
    Monday, October 20, 2003 4:05 AM

All replies

  • User-1907454718 posted
    Are you assigning nothing to a variable or to the SQLParameter? If you are assigning to a variable, why not try initializing to dbnull then if there is nothing entered don't even attempt to assign a value to the variable? Does that work, or what else are you looking for?
    Monday, October 20, 2003 1:58 PM
  • User-1323270716 posted
    Thank you for your answer. I was trying to mantain the data access approach used in the 'TimeTracker ASP.NET StarterKit'. That is private variables passed as parameters in the SQLHelper string. For example: Public Class ActivityClasses Private _activityClassID As Integer Private _name As String Private _description As String '. Public Property Name() As String Get Return _name End Get Set(ByVal Value As String) _name = Value End Set End Property '. Private Function Insert() As Boolean _activityClassID = Convert.ToInt32(SqlHelper.ExecuteScalar(ConfigurationSettings.AppSettings(Web.Global.CfgKeyConnString), "spAddActivityClass", _name, _description)) Return ActivityClassID > 0 End Function When the private variable is of type Date (e.g. _endDate), and NO value is assigned, it gets the default value "#12:00:00 AM#". It must be that something is set somewhere ... I do not know where. PS. Everithing works fine if I assign 'nothing' to the SQLParameter ..
    Tuesday, October 21, 2003 3:48 AM
  • User-1907454718 posted
    Can you check in your property and set it to null if it's actually nothing (check the value before setting: Set(ByVal Value As String) if value = nothing then _name = System.dbnull Else _name = Value End If End Set HTH
    Tuesday, October 21, 2003 2:00 PM
  • User-1323270716 posted
    Thank you Eric. I did try your suggestion. The line _name=System.dbnull triggers the following error: 'DBNull' is a type in 'System' and cannot be used as an expression Any more ideas?
    Tuesday, October 21, 2003 3:04 PM
  • User-1907454718 posted
    I think it's System.dbNull.Value or something like that.
    Tuesday, October 21, 2003 3:33 PM