locked
“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",
            CultureInfo.InvariantCulture))
            cmd.Parameters.AddWithValue("@f4", dd_eventcategory.SelectedValue)
            oleDbConn.Open()
            cmd.ExecuteNonQuery()
            System.Threading.Thread.Sleep("2000")
            Response.Redirect("~/calendar.aspx")
        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
         date"></asp:TextBox>
                        <ajaxToolkit:CalendarExtender ID="tb_eventdate_CalendarExtender"  Format="dd/MM/yyyy" runat="server"
    
                            TargetControlID="tb_eventdate">
                        </ajaxToolkit:CalendarExtender>
    
    

    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"), _
                                        selectedDay.AddDays(1).ToString("dd/MM/yyyy"))
        Dim ds As DataSet = New DataSet()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        da.Fill(ds)
        con.Close()
    
        Return ds.Tables(0)
    End Function

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




    Wednesday, April 10, 2013 11:38 AM

Answers

All replies