Nullable object must have a value.

Discussion in 'ASP .Net' started by scparker, Jan 16, 2007.

  1. scparker

    scparker Guest

    I have yet to find a satisfactory solution to this problem. It involves
    VB.NET 2.0 and datetime issues.

    I have a form that asks for a Date to be submitted in dd/mm/yyyy
    format. When this is submitted it then is then dealt with as follows:

    ------------------------------------

    Public Sub Update(ByVal sender As Object, ByVal e As System.EventArgs)
    Handles ConfirmEdit.Click

    '*** Declare
    Dim RequestedDate As Nullable(Of Date)

    '*** Populate
    RequestedDate=ERequestedDate.Text

    Result1 = DataTable.Update(Id, RequestedDate, ConfirmedDate)

    End Sub

    --------------------------------------

    The DataTable.Update represents the datatable used and the method is
    calling a stored procedure that updates a particular record based on an
    id given to it.

    Now, the above code is a simplification - but the principle si the
    same. The ConfirmedDate may have nothing in it - and causes the
    "Nullable object must have a value. " error.

    The DataTable allows for DBNull.
    ---------------------------------------------
    DataType=System.DateTime
    AllowDBNull=True
    DefaultValue=<DBNull>
    ---------------------------------------------

    The Stored Procedure allows for Nulls:
    ---------------------------------------------
    CREATE PROCEDURE [dbo].[Update]
    (@Id int,
    @RequestedDate datetime = null,
    @ConfirmedDate datetime = null)
    AS
    BEGIN
    ~~~~~~~
    END
    GO
    ---------------------------------------------

    The Database allows For Nulls:
    ---------------------------------------------
    DataType=datetime (8)
    Null=True
    DefaultValue=Null
    ---------------------------------------------


    But yet the update will not work when the ConfirmedDate value has
    nothing in it. Does anybody have any ideas how to get round this?
    scparker, Jan 16, 2007
    #1
    1. Advertising

  2. When you have an empty date have you tried to pass DBNull.Value to the
    Stored Procededure instead of a null date?

    Regards,
    Brian K. Williams


    "scparker" <> wrote in message
    news:...
    >I have yet to find a satisfactory solution to this problem. It involves
    > VB.NET 2.0 and datetime issues.
    >
    > I have a form that asks for a Date to be submitted in dd/mm/yyyy
    > format. When this is submitted it then is then dealt with as follows:
    >
    > ------------------------------------
    >
    > Public Sub Update(ByVal sender As Object, ByVal e As System.EventArgs)
    > Handles ConfirmEdit.Click
    >
    > '*** Declare
    > Dim RequestedDate As Nullable(Of Date)
    >
    > '*** Populate
    > RequestedDate=ERequestedDate.Text
    >
    > Result1 = DataTable.Update(Id, RequestedDate, ConfirmedDate)
    >
    > End Sub
    >
    > --------------------------------------
    >
    > The DataTable.Update represents the datatable used and the method is
    > calling a stored procedure that updates a particular record based on an
    > id given to it.
    >
    > Now, the above code is a simplification - but the principle si the
    > same. The ConfirmedDate may have nothing in it - and causes the
    > "Nullable object must have a value. " error.
    >
    > The DataTable allows for DBNull.
    > ---------------------------------------------
    > DataType=System.DateTime
    > AllowDBNull=True
    > DefaultValue=<DBNull>
    > ---------------------------------------------
    >
    > The Stored Procedure allows for Nulls:
    > ---------------------------------------------
    > CREATE PROCEDURE [dbo].[Update]
    > (@Id int,
    > @RequestedDate datetime = null,
    > @ConfirmedDate datetime = null)
    > AS
    > BEGIN
    > ~~~~~~~
    > END
    > GO
    > ---------------------------------------------
    >
    > The Database allows For Nulls:
    > ---------------------------------------------
    > DataType=datetime (8)
    > Null=True
    > DefaultValue=Null
    > ---------------------------------------------
    >
    >
    > But yet the update will not work when the ConfirmedDate value has
    > nothing in it. Does anybody have any ideas how to get round this?
    >
    Brian Williams, Jan 16, 2007
    #2
    1. Advertising

  3. scparker

    scparker Guest

    Brian Williams wrote:
    > When you have an empty date have you tried to pass DBNull.Value to the
    > Stored Procededure instead of a null date?
    >
    > Regards,
    > Brian K. Williams
    >
    >


    I have tried to see how I would be able to dictate the DBNull.Value in
    my code but every attempt has failed so far. Given that I am using a
    stored procedure, how would I be able to dictate the ConfirmedDate
    value to be of DBNull.Value?

    Sincerely,


    Stephen
    scparker, Jan 17, 2007
    #3
  4. scparker

    Mark Rae Guest

    "scparker" <> wrote in message
    news:...

    > Brian Williams wrote:
    >> When you have an empty date have you tried to pass DBNull.Value to the
    >> Stored Procededure instead of a null date?
    >>
    >> Regards,
    >> Brian K. Williams
    >>
    >>

    >
    > I have tried to see how I would be able to dictate the DBNull.Value in
    > my code but every attempt has failed so far. Given that I am using a
    > stored procedure, how would I be able to dictate the ConfirmedDate
    > value to be of DBNull.Value?


    DateTime? dtmDate;
    SqlParameter objSqlParameter;

    objSqlParameter = new SqlParameter("@pdtmDate", SqlDbType.DateTime);
    objSqlParameter.IsNullable = true;
    objSqlParameter.Value = dtmDate ?? (object)DBNull.Value;
    Mark Rae, Jan 17, 2007
    #4
  5. scparker

    scparker Guest

    Mark Rae wrote:
    > "scparker" <> wrote in message
    > news:...
    >
    > > Brian Williams wrote:
    > >> When you have an empty date have you tried to pass DBNull.Value to the
    > >> Stored Procededure instead of a null date?
    > >>
    > >> Regards,
    > >> Brian K. Williams
    > >>
    > >>

    > >
    > > I have tried to see how I would be able to dictate the DBNull.Value in
    > > my code but every attempt has failed so far. Given that I am using a
    > > stored procedure, how would I be able to dictate the ConfirmedDate
    > > value to be of DBNull.Value?

    >
    > DateTime? dtmDate;
    > SqlParameter objSqlParameter;
    >
    > objSqlParameter = new SqlParameter("@pdtmDate", SqlDbType.DateTime);
    > objSqlParameter.IsNullable = true;
    > objSqlParameter.Value = dtmDate ?? (object)DBNull.Value;



    I am sorry - that does not make sense to me at all. Could you consider
    the code that I published to begin with and adapt your scripting that
    you have done there to be applicable to what you have written? I have
    tried putting your code in but Visual Studio 2005 does not like it at
    all.
    scparker, Jan 17, 2007
    #5
  6. scparker

    Mark Rae Guest

    "scparker" <> wrote in message
    news:...

    > I am sorry - that does not make sense to me at all. Could you consider
    > the code that I published to begin with and adapt your scripting that
    > you have done there to be applicable to what you have written? I have
    > tried putting your code in but Visual Studio 2005 does not like it at
    > all.


    Apologies - your OP had disappeared from my newsreader...

    Try this:

    If RequestedDate Is Null Then
    RequestedDate=DbNull.Value
    Else
    RequestedDate=ERequestedDate.Text
    End If

    I haven't tested this, as I never use VB.NET...
    Mark Rae, Jan 17, 2007
    #6
  7. I don't know VB.Net verry well, but I don't see in your code example where
    you add your sql parameters.

    This is an example of how I do it in C#:
    if(contactDTO.LockoutDateTime == ConstantsDB.NullDateTime)
    cmd.AddInParameter(ConstantsDB.Contact.LockoutDateTime, DbType.DateTime,
    DBNull.Value);
    else
    cmd.AddInParameter(ConstantsDB.Contact.LockoutDateTime, DbType.DateTime,
    contactDTO.LockoutDateTime);

    Regards,
    Brian K. Williams


    "scparker" <> wrote in message
    news:...
    >
    > Brian Williams wrote:
    >> When you have an empty date have you tried to pass DBNull.Value to the
    >> Stored Procededure instead of a null date?
    >>
    >> Regards,
    >> Brian K. Williams
    >>
    >>

    >
    > I have tried to see how I would be able to dictate the DBNull.Value in
    > my code but every attempt has failed so far. Given that I am using a
    > stored procedure, how would I be able to dictate the ConfirmedDate
    > value to be of DBNull.Value?
    >
    > Sincerely,
    >
    >
    > Stephen
    >
    Brian Williams, Jan 17, 2007
    #7
  8. scparker

    Mark Rae Guest

    "Mark Rae" <> wrote in message
    news:...

    > Try this:
    >
    > If RequestedDate Is Null Then
    > RequestedDate=DbNull.Value
    > Else
    > RequestedDate=ERequestedDate.Text
    > End If
    >


    Actually, on reflection, I think this may be better...

    If RequestedDate Is Null Then
    Result1 = DataTable.Update(Id, DbNull.Value, ConfirmedDate)
    Else
    Result1 = DataTable.Update(Id, RequestedDate, ConfirmedDate)
    End If
    Mark Rae, Jan 17, 2007
    #8
  9. I also don't do VB.Net but I think this will work, the only thing I think I
    can add is...

    If RequestedDate is a DateTime object then it will not be initialized as
    null rather 1/1/0001 12:00:00 AM.
    So you should test it like this:

    If RequestedDate Is DateTime.MinValue Then

    Regards
    Brian K. Williams


    "Mark Rae" <> wrote in message
    news:...
    > "Mark Rae" <> wrote in message
    > news:...
    >
    >> Try this:
    >>
    >> If RequestedDate Is Null Then
    >> RequestedDate=DbNull.Value
    >> Else
    >> RequestedDate=ERequestedDate.Text
    >> End If
    >>

    >
    > Actually, on reflection, I think this may be better...
    >
    > If RequestedDate Is Null Then
    > Result1 = DataTable.Update(Id, DbNull.Value, ConfirmedDate)
    > Else
    > Result1 = DataTable.Update(Id, RequestedDate, ConfirmedDate)
    > End If
    >
    >
    >
    >
    Brian Williams, Jan 17, 2007
    #9
  10. scparker

    Mark Rae Guest

    "Brian Williams" <> wrote in message
    news:%...

    >I also don't do VB.Net but I think this will work, the only thing I think I
    >can add is...
    >
    > If RequestedDate is a DateTime object then it will not be initialized as
    > null rather 1/1/0001 12:00:00 AM.


    Is that the case if the variable is declared like this:

    Dim RequestedDate As Nullable(Of Date)
    Mark Rae, Jan 17, 2007
    #10
  11. I wish I could answer that, I don't know VB.Net. But you can run a debug on
    it without initializing it. If the value comes back as null then the answer
    is no, if the value comes back as 1/1/0001 12:00:00 AM. then the answer is
    yes.

    Regards,
    Brian K. Williams


    "Mark Rae" <> wrote in message
    news:%...
    > "Brian Williams" <> wrote in message
    > news:%...
    >
    >>I also don't do VB.Net but I think this will work, the only thing I think
    >>I can add is...
    >>
    >> If RequestedDate is a DateTime object then it will not be initialized as
    >> null rather 1/1/0001 12:00:00 AM.

    >
    > Is that the case if the variable is declared like this:
    >
    > Dim RequestedDate As Nullable(Of Date)
    >
    Brian Williams, Jan 17, 2007
    #11
  12. I did a little research on this.. it loolks like you need to use:
    If RequestedDate.HasValue Then

    Regards,
    Brian K. Williams

    "Mark Rae" <> wrote in message
    news:%...
    > "Brian Williams" <> wrote in message
    > news:%...
    >
    >>I also don't do VB.Net but I think this will work, the only thing I think
    >>I can add is...
    >>
    >> If RequestedDate is a DateTime object then it will not be initialized as
    >> null rather 1/1/0001 12:00:00 AM.

    >
    > Is that the case if the variable is declared like this:
    >
    > Dim RequestedDate As Nullable(Of Date)
    >
    Brian Williams, Jan 17, 2007
    #12
  13. scparker

    Mark Rae Guest

    "Brian Williams" <> wrote in message
    news:e%...

    >I did a little research on this.. it loolks like you need to use:
    > If RequestedDate.HasValue Then


    D'oh! Same as C#, then...

    I guess I've just got so used to using ?? in C# that I forgot about the
    ..HasValue property of nullable datatypes... :)
    Mark Rae, Jan 17, 2007
    #13
  14. scparker

    scparker Guest

    Thanks for your considerations on this:

    I have started to use the HasValue and the error has changed!!!

    Ok - let me explain things a little here. I have a control that updates
    a certain part of information in a table. It calls an update procedure
    that handles th whole table. In order the update to work, I retrieve
    the exisitng data from that Id, and then update the record with
    existing values retrieved and the new values I wish to include.

    So - the control action goes a little something like this:

    '*** Generate Request
    Protected Sub Confirm(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles Confirm.Click
    '*** Declare
    Dim Id As String
    Dim NDelivery2 As Nullable(Of DateTime)
    Dim UpdateResult As Integer

    '*** Populate
    NId = EId.Text
    NDelivery2 = EDelivery2.Text

    '*** Data Access
    Data.SelectById(NId)

    '*** Confirm
    UpdateResult = Data.UpdateById(NId, NDelivery2,
    Data.RequestedDate, Data.ConfirmedDate)

    End Sub

    Lets imagine that the UpdateP Procedure requires all 4 variables passed
    into it. I have 2 of them (NId & NDelivery2 - but use the existing
    values in Data Record to populate the remaining two (Data.RequestedDate
    & Data.ConfirmedDate)

    These last two may have null values in them.

    This update procedure then goes to the class which will deal with it in
    the following way:

    <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update,
    True)> _
    Public Function UpdateById( _
    ByVal Id As Integer, _
    ByVal Delivery2 As Nullable(Of DateTime), _
    ByVal RequestedDate As Nullable(Of DateTime), _
    ByVal ConfirmedDate As Nullable(Of DateTime)
    ) As Boolean

    '*** Declare Variables
    Dim Deliveries As DAL3.SDDataTable = Adapter1.GetById(Id)
    Dim RowsAffected As Boolean

    '*** Ensure Record Exists
    If Deliveries.Count = 0 Then Return False

    '*** Declare
    Dim Delivery As DAL3.SDRow = Deliveries(0)

    '*** Populate
    If Delivery2.HasValue = True Then Delivery.Delivery2 =
    Delivery2
    If RequestedDate.HasValue = True Then Delivery.RequestedDate =
    RequestedDate
    If ConfirmedDate.HasValue = True Then Delivery.ConfirmedDate =
    ConfirmedDate

    '*** Update
    RowsAffected = Adapter1.Update(Delivery)

    '*** Publish Result
    Return RowsAffected
    End Function

    ------------------

    The Population does not include the Id because it is a read-only
    property (primary key) and therefore will not be writable.

    With this code in mind - I have the Id, Delivery2 value, RequestdDate
    is taken from the DB and passed back in, along with the ConfirmedDate.
    These may be null values and as a result I will end up with the
    following fault:

    Error converting data type nvarchar to datetime.

    I understand that Delivery2 is taken fro ma text value, but has a
    correct date time value and has never been a problem elsewhere. With
    this in mind, I can only assume it is to do with RequestedDate and
    ConfirmedDate. However, they are taken from a existing Data collection.
    Sorry to change things, but do you have any clues on this?

    Thanks again by the way for your thoughts on this, most generous.
    scparker, Jan 17, 2007
    #14
  15. It sounds like you are attempting to assign a string date to a datetime
    object. Try converting the string date (nVarChar) into a valid datetime
    (DateTime).

    RequestedDate = DateTime.Parse(ERequestedDate.Text)
    -Brian


    "scparker" <> wrote in message
    news:...
    > Thanks for your considerations on this:
    >
    > I have started to use the HasValue and the error has changed!!!
    >
    > Ok - let me explain things a little here. I have a control that updates
    > a certain part of information in a table. It calls an update procedure
    > that handles th whole table. In order the update to work, I retrieve
    > the exisitng data from that Id, and then update the record with
    > existing values retrieved and the new values I wish to include.
    >
    > So - the control action goes a little something like this:
    >
    > '*** Generate Request
    > Protected Sub Confirm(ByVal sender As Object, ByVal e As
    > System.EventArgs) Handles Confirm.Click
    > '*** Declare
    > Dim Id As String
    > Dim NDelivery2 As Nullable(Of DateTime)
    > Dim UpdateResult As Integer
    >
    > '*** Populate
    > NId = EId.Text
    > NDelivery2 = EDelivery2.Text
    >
    > '*** Data Access
    > Data.SelectById(NId)
    >
    > '*** Confirm
    > UpdateResult = Data.UpdateById(NId, NDelivery2,
    > Data.RequestedDate, Data.ConfirmedDate)
    >
    > End Sub
    >
    > Lets imagine that the UpdateP Procedure requires all 4 variables passed
    > into it. I have 2 of them (NId & NDelivery2 - but use the existing
    > values in Data Record to populate the remaining two (Data.RequestedDate
    > & Data.ConfirmedDate)
    >
    > These last two may have null values in them.
    >
    > This update procedure then goes to the class which will deal with it in
    > the following way:
    >
    > <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update,
    > True)> _
    > Public Function UpdateById( _
    > ByVal Id As Integer, _
    > ByVal Delivery2 As Nullable(Of DateTime), _
    > ByVal RequestedDate As Nullable(Of DateTime), _
    > ByVal ConfirmedDate As Nullable(Of DateTime)
    > ) As Boolean
    >
    > '*** Declare Variables
    > Dim Deliveries As DAL3.SDDataTable = Adapter1.GetById(Id)
    > Dim RowsAffected As Boolean
    >
    > '*** Ensure Record Exists
    > If Deliveries.Count = 0 Then Return False
    >
    > '*** Declare
    > Dim Delivery As DAL3.SDRow = Deliveries(0)
    >
    > '*** Populate
    > If Delivery2.HasValue = True Then Delivery.Delivery2 =
    > Delivery2
    > If RequestedDate.HasValue = True Then Delivery.RequestedDate =
    > RequestedDate
    > If ConfirmedDate.HasValue = True Then Delivery.ConfirmedDate =
    > ConfirmedDate
    >
    > '*** Update
    > RowsAffected = Adapter1.Update(Delivery)
    >
    > '*** Publish Result
    > Return RowsAffected
    > End Function
    >
    > ------------------
    >
    > The Population does not include the Id because it is a read-only
    > property (primary key) and therefore will not be writable.
    >
    > With this code in mind - I have the Id, Delivery2 value, RequestdDate
    > is taken from the DB and passed back in, along with the ConfirmedDate.
    > These may be null values and as a result I will end up with the
    > following fault:
    >
    > Error converting data type nvarchar to datetime.
    >
    > I understand that Delivery2 is taken fro ma text value, but has a
    > correct date time value and has never been a problem elsewhere. With
    > this in mind, I can only assume it is to do with RequestedDate and
    > ConfirmedDate. However, they are taken from a existing Data collection.
    > Sorry to change things, but do you have any clues on this?
    >
    > Thanks again by the way for your thoughts on this, most generous.
    >
    Brian Williams, Jan 17, 2007
    #15
  16. scparker

    scparker Guest

    Ok,

    It appears that the update I was using was within the tableadapters
    configurable section. When I removed it from there and used an update
    within the same adapter, but as it's own query - the process worked
    fine.

    So many hours wasted trying to resolve it, only to find it is some
    problem with the TableAdapter....

    Many thanks for your help on this and I wish you both well for 2007,
    your input has been helpful and appreciated.

    Sincerely,


    Stephen Parker.
    scparker, Jan 18, 2007
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?QURhdmlkc29u?=
    Replies:
    3
    Views:
    2,302
    =?Utf-8?B?QURhdmlkc29u?=
    Mar 4, 2005
  2. Nikola Skoric
    Replies:
    4
    Views:
    1,987
    Colin Marquardt
    Jun 15, 2006
  3. ina
    Replies:
    4
    Views:
    529
    Andy Dingley
    Jun 13, 2006
  4. Nemisis

    Nullable object types

    Nemisis, Aug 18, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    6,497
    Patrice
    Aug 21, 2006
  5. =?Utf-8?B?emlubw==?=

    Nullable object must have a value

    =?Utf-8?B?emlubw==?=, Apr 13, 2007, in forum: ASP .Net
    Replies:
    5
    Views:
    12,941
    Steven Cheng[MSFT]
    Apr 16, 2007
Loading...

Share This Page