Nullable object must have a value.

  • Thread starter Thread starter scparker
  • Start date Start date
S

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
 
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
 
Brian said:
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
Click to expand...

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
 
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?
Click to expand...

DateTime? dtmDate;
SqlParameter objSqlParameter;

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

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


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.
 
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.
Click to expand...

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...
 
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
 
Try this:

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

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
 
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
 
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.
Click to expand...

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

Dim RequestedDate As Nullable(Of Date)
 
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
 
I did a little research on this.. it loolks like you need to use:
If RequestedDate.HasValue Then

Regards,
Brian K. Williams
 
I did a little research on this.. it loolks like you need to use:
If RequestedDate.HasValue Then
Click to expand...

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... :-)
 
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.
 
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
 
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.
 
Back
Top