CODEWITHSUNDEEP
asp.netvb.netms-accessdatetime-format
adaam
adaam

Reputation: 3706

"Data type mismatch in criteria expression" error inserting Dates into Date/Time Field in Access database

I am using the 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?

Upvotes: 0

Views: 7145

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

Maybe it's not the date that's messing you up. I thought perhaps you were getting the error because you were adding a DateTime value as a parameter (instead of a date converted to a string formatted as yyyy-mm-dd or m/d/yyyy), but I tried the following in C# and it worked fine...

static void Main(string[] args)
{
    OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\Desktop\Database1.accdb;");
    conn.Open();
    OleDbCommand cmd = new OleDbCommand("INSERT INTO Events (EventName, EventDate) VALUES (?, ?)", conn);
    cmd.Parameters.AddWithValue("?", "TestEvent");
    cmd.Parameters.AddWithValue("?", (new DateTime(2013,3,21)));
    cmd.ExecuteNonQuery();
    conn.Close();

    Console.WriteLine("Done.");
}

...so if your DateTime parsing is returning a valid DateTime value then it looks like your query should work.

If it really is the execution of the SQL statement that is failing, the only other likely suspect is the dd_eventcategory.SelectedValue. Perhaps that needs to be .ToString()'d...?

Upvotes: 3

Related Questions