问题描述
为什么会出现此错误?未处理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.
这篇关于我应该在“条件表达式中的数据类型不匹配"中做什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!