vb.net “条件表达式中的数据类型不匹配”错误将日期插入 Access 数据库中的日期/时间字段

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

提问by adaam

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.

我正在使用日历扩展器来扩展 Visual Studio 2010 内 ASP.NET 中的文本框。我试图将事件的日期与其他信息一起插入数据库。尝试插入数据库时​​，我收到“条件表达式中的数据类型不匹配”错误。

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

我尝试使用 DateTime.ParseExact 将字符串日期转换为访问日期/时间，但仍然没有运气。

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

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.NET 代码（请注意，我还将 CalendarExtender 插入文本框中的日期格式化为“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>

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

我的 Access 数据库中的字段是“日期/时间”类型。

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:

我不知道为什么我会遇到这个问题，因为我已经设法在另一个函数中从数据库中检索日期并将它们转换为 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

    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?

我收到的错误可能是什么原因？

回答by Gord Thompson

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

也许这不是让你感到困惑的日期。我想也许您收到错误是因为您添加了一个DateTime值作为参数（而不是将日期转换为格式为yyyy-mm-ddor的字符串m/d/yyyy），但我在 C# 中尝试了以下操作，效果很好......

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

...所以如果您的 DateTime 解析返回一个有效的 DateTime 值，那么您的查询看起来应该可以工作。

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

如果确实是 SQL 语句的执行失败了，那么唯一可能的怀疑是dd_eventcategory.SelectedValue. 也许这需要.ToString()'d ...？