我应该在“条件表达式中的数据类型不匹配"中做什么? [英] what should I do in "Data type mismatch in criteria expression"

问题描述

为什么会出现此错误? 未处理OledbException" 条件表达式中的数据类型不匹配."

Why am I getting this error? "OledbException was unhandled" "Data type mismatch in criteria expression."

查询字符串数据类型没有问题，但是查询整数数据类型时，总是遇到此问题.

There is no problem when I am querying a string data type, but when I am querying a integer data type, I am always getting this problem..

我正在使用Microsoft Access 2007

I am using microsoft access 2007

这是我的源代码:

Public Function searchMemberId(ByVal userId As String) As DataSet
    sqlStr = "Select Member_ID From tblMemberInfo Where Member_ID = '" & _
        Val(userId) & "'"
    ds.Clear()
    da = New OleDbDataAdapter(sqlStr, con.ConnectionString)
    da.Fill(ds, "john")

    Return ds
End Function

Member_ID的数据类型为自动编号，错误始终指向da.Fill(ds，"john")

the data type of Member_ID is autonumber, the error was always pointing to da.Fill(ds, "john")

"ds"是一个数据集

推荐答案

如果您查询数字类型，请不要使用引号

If you query a numeric type, don't use quotes

SELECT Member_ID 
FROM tblMemberInfo
WHERE Member_ID = 17

如果您查询字符串类型，请使用引号

If you query a string type, do use quotes

SELECT Member_ID 
FROM tblMemberInfo
WHERE Member_ID = 'john'

---

更新

---

UPDATE

在这种情况下，Val功能将无济于事.只需写

The Val function will not help in this case. Simply write

sqlStr = "SELECT ... WHERE Member_ID = " & userId

如果ID是一个字符串，则它可能包含单引号，您必须在SQL字符串中使用双引号将其转义

If the ID was a string it could contain single quotes that you must escape with double quotes in a SQL string

ID = "John's record"
sqlStr = "SELECT ... WHERE Member_ID = '" & ID.Replace("'", "''") & "'"
' ==> "SELECT ... WHERE Member_ID = 'John''s record'"

这也有助于防止 SQL注入(维基百科).但是，更专业的方法是使用参数.请参阅史蒂夫的答案和 DataAdapter参数(ADO.NET) (尤其是"OleDb参数占位符"和"OleDb示例"部分.

This also helps preventing SQL injections (Wikipedia). A more professional approach, however, is to use parameters. See Steve's answer and DataAdapter Parameters (ADO.NET) on MSDN (especially the "OleDb Parameter Placeholders" and "OleDb Example" sections.

这篇关于我应该在“条件表达式中的数据类型不匹配"中做什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！