Convert ASP.NET textbox control .text contents to Date/Time format

Question

I am trying to insert into a database - various details about an event. The asp.net textbox is using the Calendar Extender (so a little calendar pops up and fills the textbox with a correctly formatted date). The EventDate field in my Access database is of the type Date/Time. I need to convert the text/string to date/time format

I have tried this so far:

VB:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("BookMeetConnString").ConnectionString)
    Dim SqlString As String = "Insert into Events(EventTitle,EventDescription,EventDate,EventCategory) Values
   (@f1,@f2,@f3,@f4)"
    Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)

    Dim strDate As String = tb_eventdate.Text
    Dim dtfi As New System.Globalization.DateTimeFormatInfo
    dtfi.ShortDatePattern = "dd/MM/yyyy"
    dtfi.DateSeparator = "/"
    Dim objDate As DateTime = Convert.ToDateTime(strDate, dtfi)

    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("@f1", tb_eventtitle.Text)
    cmd.Parameters.AddWithValue("@f2", tb_eventdescription.Text)
    cmd.Parameters.AddWithValue("@f3", tb_eventdate.Text)
    cmd.Parameters.AddWithValue("@f4", dd_eventcategory.Text)
    oleDbConn.Open()
    cmd.ExecuteNonQuery()
    System.Threading.Thread.Sleep("2000")
    Response.Redirect("~/calendar.aspx")
End Sub

Here is my clientside code just for reference:

 <h1>Add An Event!<ajaxToolkit:ToolkitScriptManager
 ID="ToolkitScriptManager1" 
                   runat="server">
               </ajaxToolkit:ToolkitScriptManager>
            </h1>
            <p>Title of Event:
                <asp:TextBox ID="tb_eventtitle" runat="server"></asp:TextBox>
            </p>
            <p>Event Description:
                <asp:TextBox ID="tb_eventdescription" runat="server"></asp:TextBox>
            </p>
            <p>Event Date:
                <asp:TextBox ID="tb_eventdate" runat="server"></asp:TextBox>
                <ajaxToolkit:CalendarExtender ID="tb_eventdate_CalendarExtender" runat="server" 
                    TargetControlID="tb_eventdate">
                </ajaxToolkit:CalendarExtender>
            </p>
            <p>Event Category:
                <asp:DropDownList ID="dd_eventcategory" runat="server" 
                    DataSourceID="SqlDataSource1" DataTextField="CategoryTitle" 
                    DataValueField="CategoryTitle">
                </asp:DropDownList>
            </p>
            <p>
                <asp:Button ID="Button1" runat="server" Text="Submit" />
            </p>

When I try to fill out the form, I receive this error:

My Two questions are:

1. What is wrong with the code above, and how do I successfully use the DateTimeFormatInfo class to convert String to Date/Time?
2. On a side note, the Calendar Extender inputs the date into the textbox in American Time format (MM/DD/YYYY), how do I change this to British (DD/MM/YYYY) format (I couldn't see an obvious property in the properties dialog to do this?)

Thanks in advance for your answers!

Adam

EDIT: Updated code below:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("BookMeetConnString").ConnectionString)
    Dim SqlString As String = "Insert into Events(EventTitle,EventDescription,EventDate,EventCategory) Values
 (@f1,@f2,@f3,@f4)"
    Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("@f1", tb_eventtitle.Text)
    cmd.Parameters.AddWithValue("@f2", tb_eventdescription.Text)
    cmd.Parameters.AddWithValue("@f3", DateTime.ParseExact(tb_eventdate.Text, "dd/MM/yyyy",
   CultureInfo.InvariantCulture))
    cmd.Parameters.AddWithValue("@f4", dd_eventcategory.Text)
    oleDbConn.Open()
    cmd.ExecuteNonQuery()
    System.Threading.Thread.Sleep("2000")
    Response.Redirect("~/calendar.aspx")
End Sub

Solution

I'd recommend you use DateTime.ParseExact static method, especially this oveload:

DateTime.ParseExact(textBox.Text, "dd/MM/yyyy",CultureInfo.InvariantCulture)

This will parse the text you have by the concrete format you specify ("dd/MM/yyyy" currently, case is important since mm is minutes as opposed to MM being months). Use of CultureInfo.InvariantCulture guarantees that date separators will be retrieved from the format string (the second parameter). I have noticed that if current culture is used, it overrides some aspects of the format string you pass to ParseExact.

---

A note on CultureInfo

Invariant culture is good also for the reason that your local dev environment may have different regional information setup than the deployment environment. Usually, .NET uses the current culture in all .ToString calls and implicit formatting or parsing. When forcing a format and culture invariance explicitly, you are less prone to problems you cannot reproduce locally but exist on the production application.

---

A note on date/time formats

With exact parsing, the datetime format is expected to strictly match the format of the input. You should then take into consideration the following examples:

• dd matches two-digit days only. So "dd/MM/yyyy" it will match "01/01/2013", but will fail for "1/1/2013" because it expects the exact number of digits for the day part. If you do not want leading zeros use: d/M/yyyy instead. Single letter means one digit for days less than 10 and two digits for the others.
• MM matches two-digit month, so all that applies to dd vs. d is the same for months.
• yyyy expects the year to be in 4 digits. If you use two-digit year, use yy instead.

---

A note on some ADO.NET providers

As it turns out to be the case with MS Access, the correctly parsed date-time object is not sufficient to make the query work. Currently, the following code

 cmd.Parameters.AddWithValue(...)

is used to add parameters to the query. However, this approach omits passing information to the ADO.NET db provider that tells what database type to use for the parameter. I have read on some forums that MS Access/OleDb is not capable to resolve the correct type in all cases. Therefore I recommend the following approach:

Dim prm as OleDbParameter = _
   New OleDbParameter("@dateTimeParameterName", OleDbType.DateTime)
prm.Value = value  'value is an instance of `System.DateTime` parsed 
                   'from the input
cmd.Parameters.Add(prm)

The above code allows to specify the parameter database type explicitly, so the OleDb driver is now capable of correctly passing the DateTime object to the MS Access database.