[求助]标准表达式中的数据类型不匹配 [英] [SOLVED] Data type mismatch in criteria expression

问题描述

这应该是一个简单的，但我遗漏了一些东西，现在已经工作了两天了。



我想插入日期/时间以短日期格式（日期/时间mm / dd / yyyy）进入访问数据库，每次都得到条件表达式中的数据类型不匹配。如果我评论出开始日，我会得到没有价值，这是我所期待的。



任何帮助都会非常感激。



提前致谢



如果在这个例子中有任何不同，我在这里使用母版页。 ：-O：-O

这是除了我改变的连接字符串之外的完整代码：

This should be an easy one but I am missing something and have been working on this for two days now.

I am trying to insert a date/time in a short date format (date/time mm/dd/yyyy) into an access database and I get the "Data type mismatch in criteria expression" every time. If I comment out the "startday" I get the "No value Given" which is what I would expect.

Any help here would be greatly appreciated.

Thanks in advance

I am using a master page here if it makes any difference in this instance. :-O :-O
Here is the complete code except the connection string which I have changed:

Sub CrossPage_FirstPage()
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyConnectionString"
        Dim MyCon As New OleDbConnection(strConn)
        Dim MySqlDelete As String = "delete from problemdefinition"
        Dim MySqlInsert As String = "INSERT INTO problemdefinition (memberage, sex, spouseage, numberchildren, countyid, zipcoderange, startday, dayscov) VALUES (@memberage, @sex, @spouseage, @numberchildren, @countyid, @zipcoderange, @startday, @DaysCov)"
        'Dim MySqlInsert As String = "INSERT INTO problemdefinition (client, memberage, spouseage, numberchildren, County,zipcode, startcoverage) VALUES ('" & txtName.Text & "'," & txtAge.Text & "," & txtSpouseAge.Text & "," & txtNumberChildren.Text & ")"
        Dim textboxName As TextBox = Me.PreviousPage.Master.FindControl("menu2").FindControl("txtName")
        Dim MyDate As String = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text
        Dim cmd As New OleDbCommand(MySqlDelete, MyCon)
        With cmd.Parameters
            '.Add(New OleDbParameter("@client", textboxName.Text))
            '.Add(New OleDbParameter("@memberage", DirectCast(Me.PreviousPage.Master.FindControl("ContentPlaceHolder1").FindControl("txtname"), TextBox).Text))
            .Add(New OleDbParameter("@memberage", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtAge"), TextBox).Text))
            .Add(New OleDbParameter("@sex", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("rbgGender1"), RadioButtonList).Text))
            .Add(New OleDbParameter("@spouseage", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtSpouseAge"), TextBox).Text))
            .Add(New OleDbParameter("@numberchildren", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtNumberChildren"), TextBox).Text))
            .Add(New OleDbParameter("@countyid", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("lblhidden1"), Label).Text))
            .Add(New OleDbParameter("@zipcoderange", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("drpZip1"), TextBox).Text))
            '.Add(New OleDbParameter("@deductible", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("DrpDed1"), DropDownList).SelectedItem.Value))
            '.Add(New OleDbParameter("@startday", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text))
            .Add(New OleDbParameter("@DaysCov", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtNumDays1"), TextBox).Text))
            .Add(New OleDbParameter("@startday", "CDate('" + MyDate + "')"))
        End With
        
        Dim result As Integer = -1
        'Use a variable to hold the SQL statement.


        If MyCon.State = ConnectionState.Closed Then
            MyCon.Open()
            cmd.CommandText = MySqlDelete
            result = cmd.ExecuteNonQuery()
            MyCon.Close()
        End If
        'If result <> -1 Then
        If MyCon.State = ConnectionState.Closed Then
            MyCon.Open()
            cmd.CommandText = MySqlInsert
            cmd.ExecuteNonQuery()
            MyCon.Close()
        End If
        'End If

        Dim StartCov As String = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text

        
    End Sub

推荐答案

我发现了问题：我已将文本框声明为字符串而不是日期。

此线程可以关闭 - 它已被回答。

I found the problem: I had declared the textbox as a string rather than a date.
This thread can be closed - it is answered.

首先，使用Parameters.AddWithValue而不是Parameters.Add - 后者被折旧。



其次，不要将文本框中的日期读入字符串，而是使用DateTime.TryParseExact将其转换为DateTime - 然后可以直接将其用于startday而不是使用CDate转换。这也意味着您可以更好地控制日期格式，并在尝试插入数据库之前报告问题。

Firstly, use Parameters.AddWithValue instead of Parameters.Add - the later is depreciated.

Secondly, instead of reading a date from a text box into a string, use DateTime.TryParseExact instead to convert it to a DateTime - you can then use that directly with the "startday" instead of using a CDate conversion. This also means you can control the date format much better, and report problems with it before you try to insert into the database.

问题是OleDBCommand不能使用命名参数。

您必须插入问号。当您通过以下方式添加参数时：



myOleDBCmd.Parameters.Add（Object obj，SqlDBType sqltype）



您必须按照问号出现的确切顺序添加它们。由于命名参数不可能，它们在每个参数列表中的索引匹配？在SQL中选择具有下一个索引的参数。



此案例与您的相关：

如何在Access 2007中存储jpeg [ ^ ]



最好的问候，



Manfrd

Problem is OleDBCommand does not work with named parameters.
You have to insert a question mark instead. When you add the parameters via:

myOleDBCmd.Parameters.Add(Object obj, SqlDBType sqltype)

You have to add them in the exact order the question marks appear in. Since named parameters are not possible they are matched by their index in the list of parameters for every ? in the SQL the parameter with the next index is chosen.

This case is related to yours:
How to store jpeg in Access 2007[^]

Best Regards,

Manfrd

这篇关于[求助]标准表达式中的数据类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！