We help IT Professionals succeed at work.

Visual Basic 2010: Read / Write Dates to Access Database

cspdmg
cspdmg asked
on
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.

Comment
Watch Question

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
what is the value of your textbox?

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

Author

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

String was not recognized as a valid DateTime.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
what is the value of your textbox?

Author

Commented:
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.
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
so you don't have a valid date!

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") = nothing
endif
CERTIFIED EXPERT
Commented:
It looks to me like you need to modify the command...

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

...so that the date gets formatted before you attempt to insert it into the masked field.

Not sure what you would need to do for VB2010, but in the old days of VB6, it would look something like this:

mtxtRequestDate.Text = Format( ds.Tables("WorkOrders").Rows(inc).Item("RequestDate"), "mm/dd/yyyy")

Author

Commented:
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 MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

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

Author

Commented:
That works except that it saves today's date of course.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
is your table allows null for this field?

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

Author

Commented:
No, it doesn't except null.  How to handle it through VB is where the problem is.
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

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

Author

Commented:
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 MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

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

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.