“Data type mismatch in criteria expression” error inserting Dates into Date/Time Field in Access database RRS feed

  • Question

  • User-359420042 posted

    I am using the Ajax Control Toolkit Calendar extender to extend a textbox in ASP.NET inside of Visual Studio 2010. I am trying to insert the date of an event into the database along with other bits of information. I am receiving the "Data type mismatch in criteria expression" error when trying to insert into the database.

    I tried using DateTime.ParseExact to convert the string date to Access Date/Time but still no luck.

    Here is my code behind:

        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",
            cmd.Parameters.AddWithValue("@f4", dd_eventcategory.SelectedValue)
        End Sub

    Here is my ASP.NET code (notice that I am also formatting the date inserted into the textbox by the CalendarExtender as "dd/MM/yyyy"):

         <asp:TextBox ID="tb_eventdate" runat="server" ToolTip="Enter a
                        <ajaxToolkit:CalendarExtender ID="tb_eventdate_CalendarExtender"  Format="dd/MM/yyyy" runat="server"

    The field in my Access database is of type "Date/Time".

    I don't know why I am having this problem as I have managed to retrieve dates from the database in another function and converted them ToString:

    Function GetEventListing(selectedDay As DateTime) As DataTable
        '--read event listing for the given day from an Access query
        Dim con As OleDbConnection = GetConnection()
        Dim cmd As OleDbCommand = New OleDbCommand()
        cmd.Connection = con
        cmd.CommandText = String.Format("Select * from EventInfo Where EventDate >= #{0}# And EventDate < #{1}#", _
                                        selectedDay.ToString("dd/MM/yyyy"), _
        Dim ds As DataSet = New DataSet()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        Return ds.Tables(0)
    End Function

    What could be the cause of the error I am receiving?

    Wednesday, April 10, 2013 11:38 AM


All replies