asked on

Visual Basic 2010: Read / Write Dates to Access Database

I am using a VB2010 form as a front end to an Access database. I am having two problems when reading/writing date fields.

1) When reading a date from the dataset, it doesn't display properly on the form if the date has a single digit for the month and/or day. Example: 9/23/2011 displays as 92/32/011

I'm using the following to read into a masked text box with the mask set to short date:

mtxtRequestDate.Text = ds.Tables("WorkOrders").Rows(inc).Item("RequestDate").ToString

2)When saving a date field, if the field is blank I get the following error:

String was not recognized as a valid DateTime.Couldn't store < / /> in RequestDate Column. Expected type is DateTime.

I'm using the following to write to the dataset:

ds.Tables("WorkOrders").Rows(inc).Item("RequestDate") = mtxtRequestDate.Text

Thanks in advance.

Éric Moreau

what is the value of your textbox?

have you tried: ds.Tables("WorkOrders").Rows(inc).Item("RequestDate") = convert.todatetime( mtxtRequestDate.Text)

cspdmg

ASKER

Thanks for the response however it did not work. I received the error:

String was not recognized as a valid DateTime.

Éric Moreau

what is the value of your textbox?

cspdmg

ASKER

The value of the textbox is empty (null?). It shows only the literals (/). Originally there was a date (10/12/2011), I deleted it and tried to resave.

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

HooKooDooKu

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

cspdmg

ASKER

emoreau:

It treats 'nothing' as null:

Error: Cannot set Column 'RequestDate' to be null

It looks like I will need to check for null before attempting to save and, if it is, create a blank date?

HooKooDooKu:

That worked for reading from the Access table. The only change I needed to make was to the format definition. I changed 'mm' (minutes) to MM (month). Thanks!

Éric Moreau

is your table allows null for this field?

try this:
dim dtmDate as datetime
if DateTime.TryParse(mtxtRequestDate.Text, dtmDate) then
ds.Tables("WorkOrders").Rows(inc).Item("RequestDate") = convert.todatetime( mtxtRequestDate.Text)
else
ds.Tables("WorkOrders").Rows(inc).Item("RequestDate") = datetime.today
endif

cspdmg

ASKER

That works except that it saves today's date of course.

Éric Moreau

is your table allows null for this field?

what do you need to do when the textbox does not contain a valid date?

cspdmg

ASKER

No, it doesn't except null. How to handle it through VB is where the problem is.

Éric Moreau

again, what do you need to do when the textbox does not contain a valid date?

cspdmg

ASKER

If the textbox is empty (i.e blank), I need to accept it as valid. If it is not a valid date, then reject it with a message.

Éric Moreau

But your date cannot be null. Which value will you set if it is empty?

cspdmg

ASKER

I have it working. Similar to what you suggested but I need to take the mask literals into account.

If DateTime.TryParse(mtxtRequestDate.Text, dtmDate) Then
ds.Tables("WorkOrders").Rows(inc).Item("RequestDate") =
Convert.ToDateTime(mtxtRequestDate.Text)
ElseIf Trim(mtxtRequestDate.Text) = "/ /" Then
ds.Tables("WorkOrders").Rows(inc).Item("RequestDate") = Nothing
Else
MsgBox("Invalid Request Date. Please reenter")
mtxtRequestDate.Select()
mtxtRequestDate.Clear()
Exit Sub
End If

Thank you for your assistance.