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