Nullable object must have a value.

[scparker]

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.EventArg s)
Handles ConfirmEdit.Cli ck

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

'*** Populate
RequestedDate=E RequestedDate.T ext

Result1 = DataTable.Updat e(Id, RequestedDate, ConfirmedDate)

End Sub

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

The DataTable.Updat e 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=Tru e
DefaultValue=<D BNull>
---------------------------------------------

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=dateti me (8)
Null=True
DefaultValue=Nu ll
---------------------------------------------


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]

Re: Nullable object must have a value.

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" <stevein69@hotm ail.comwrote in message
news:1168968177 .488432.155830@ q2g2000cwa.goog legroups.com...

>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.EventArg s)
Handles ConfirmEdit.Cli ck
>
'*** Declare
Dim RequestedDate As Nullable(Of Date)
>
'*** Populate
RequestedDate=E RequestedDate.T ext
>
Result1 = DataTable.Updat e(Id, RequestedDate, ConfirmedDate)
>
End Sub
>
--------------------------------------
>
The DataTable.Updat e 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=Tru e
DefaultValue=<D BNull>
---------------------------------------------
>
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=dateti me (8)
Null=True
DefaultValue=Nu ll
---------------------------------------------
>
>
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]

Re: Nullable object must have a value.


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

[Mark Rae]

Re: Nullable object must have a value.

"scparker" <stevein69@hotm ail.comwrote in message
news:1169030371 .867897.113060@ a75g2000cwd.goo glegroups.com.. .

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.DateT ime);
objSqlParameter .IsNullable = true;
objSqlParameter .Value = dtmDate ?? (object)DBNull. Value;

[scparker]

Re: Nullable object must have a value.

Mark Rae wrote:

"scparker" <stevein69@hotm ail.comwrote in message
news:1169030371 .867897.113060@ a75g2000cwd.goo glegroups.com.. .
>

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.DateT ime);
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.

[Mark Rae]

Re: Nullable object must have a value.

"scparker" <stevein69@hotm ail.comwrote in message
news:1169046862 .761213.123590@ m58g2000cwm.goo glegroups.com.. .

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=D bNull.Value
Else
RequestedDate=E RequestedDate.T ext
End If

I haven't tested this, as I never use VB.NET...

[Brian Williams]

Re: Nullable object must have a value.

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.L ockoutDateTime == ConstantsDB.Nul lDateTime)
cmd.AddInParame ter(ConstantsDB .Contact.Lockou tDateTime, DbType.DateTime ,
DBNull.Value);
else
cmd.AddInParame ter(ConstantsDB .Contact.Lockou tDateTime, DbType.DateTime ,
contactDTO.Lock outDateTime);

Regards,
Brian K. Williams


"scparker" <stevein69@hotm ail.comwrote in message
news:1169030371 .867897.113060@ a75g2000cwd.goo glegroups.com.. .

>
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
>

[Mark Rae]

Re: Nullable object must have a value.

"Mark Rae" <mark@markNOSPA Mrae.comwrote in message
news:ejiaY3kOHH A.404@TK2MSFTNG P02.phx.gbl...

Try this:
>
If RequestedDate Is Null Then
RequestedDate=D bNull.Value
Else
RequestedDate=E RequestedDate.T ext
End If
>

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

If RequestedDate Is Null Then
Result1 = DataTable.Updat e(Id, DbNull.Value, ConfirmedDate)
Else
Result1 = DataTable.Updat e(Id, RequestedDate, ConfirmedDate)
End If

[Brian Williams]

Re: Nullable object must have a value.

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.MinVal ue Then

Regards
Brian K. Williams


"Mark Rae" <mark@markNOSPA Mrae.comwrote in message
news:u6OsI7kOHH A.4724@TK2MSFTN GP02.phx.gbl...

"Mark Rae" <mark@markNOSPA Mrae.comwrote in message
news:ejiaY3kOHH A.404@TK2MSFTNG P02.phx.gbl...
>

>Try this:
>>
>If RequestedDate Is Null Then
> RequestedDate=D bNull.Value
>Else
> RequestedDate=E RequestedDate.T ext
>End If
>>

>
Actually, on reflection, I think this may be better...
>
If RequestedDate Is Null Then
Result1 = DataTable.Updat e(Id, DbNull.Value, ConfirmedDate)
Else
Result1 = DataTable.Updat e(Id, RequestedDate, ConfirmedDate)
End If
>
>
>
>

[Mark Rae]

Re: Nullable object must have a value.

"Brian Williams" <williamsb@adel phia.netwrote in message
news:%23LBokClO HHA.4484@TK2MSF TNGP02.phx.gbl. ..

>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]

Re: Nullable object must have a value.

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" <mark@markNOSPA Mrae.comwrote in message
news:%23WY6jPlO HHA.5064@TK2MSF TNGP04.phx.gbl. ..

"Brian Williams" <williamsb@adel phia.netwrote in message
news:%23LBokClO HHA.4484@TK2MSF TNGP02.phx.gbl. ..
>

>>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]

Re: Nullable object must have a value.

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

Regards,
Brian K. Williams

"Mark Rae" <mark@markNOSPA Mrae.comwrote in message
news:%23WY6jPlO HHA.5064@TK2MSF TNGP04.phx.gbl. ..

"Brian Williams" <williamsb@adel phia.netwrote in message
news:%23LBokClO HHA.4484@TK2MSF TNGP02.phx.gbl. ..
>

>>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]

Re: Nullable object must have a value.

"Brian Williams" <williamsb@adel phia.netwrote in message
news:e%23x4gglO HHA.2312@TK2MSF TNGP04.phx.gbl. ..

>I did a little research on this.. it loolks like you need to use:
If RequestedDate.H asValue 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... :-)

[scparker]

Re: Nullable object must have a value.

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.EventArg s) 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.RequestedD ate, Data.ConfirmedD ate)

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.Requested Date
& Data.ConfirmedD ate)

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.Compone ntModel.DataObj ectMethodAttrib ute(System.Comp onentModel.Data ObjectMethodTyp e.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.SDDataTabl e = Adapter1.GetByI d(Id)
Dim RowsAffected As Boolean

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

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

'*** Populate
If Delivery2.HasVa lue = True Then Delivery.Delive ry2 =
Delivery2
If RequestedDate.H asValue = True Then Delivery.Reques tedDate =
RequestedDate
If ConfirmedDate.H asValue = True Then Delivery.Confir medDate =
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]

Re: Nullable object must have a value.

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" <stevein69@hotm ail.comwrote in message
news:1169056476 .900299.213530@ q2g2000cwa.goog legroups.com...

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.EventArg s) 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.RequestedD ate, Data.ConfirmedD ate)
>
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.Requested Date
& Data.ConfirmedD ate)
>
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.Compone ntModel.DataObj ectMethodAttrib ute(System.Comp onentModel.Data ObjectMethodTyp e.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.SDDataTabl e = Adapter1.GetByI d(Id)
Dim RowsAffected As Boolean
>
'*** Ensure Record Exists
If Deliveries.Coun t = 0 Then Return False
>
'*** Declare
Dim Delivery As DAL3.SDRow = Deliveries(0)
>
'*** Populate
If Delivery2.HasVa lue = True Then Delivery.Delive ry2 =
Delivery2
If RequestedDate.H asValue = True Then Delivery.Reques tedDate =
RequestedDate
If ConfirmedDate.H asValue = True Then Delivery.Confir medDate =
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.
>