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

 

[Brian Williams]

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]

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]

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;

 

[scparker]

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.

 

[Mark Rae]

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

 

[Brian Williams]

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

 

[Mark Rae]

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]

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]

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]

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

 

[Brian Williams]

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

Regards,
Brian K. Williams

 

[Mark Rae]

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

 

[scparker]

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]

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]

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.