vb.net 我应该在“条件表达式中的数据类型不匹配”中做什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11087698/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
what should I do in "Data type mismatch in criteria expression"
提问by user188228
Why am I getting this error? "OledbException was unhandled" "Data type mismatch in criteria expression."
为什么我收到这个错误？"OledbException 未处理" "条件表达式中的数据类型不匹配。"
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..
当我查询字符串数据类型时没有问题，但是当我查询整数数据类型时，我总是遇到这个问题..
I am using microsoft access 2007
我正在使用 microsoft access 2007
here is my source code:
这是我的源代码：
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
the data type of Member_ID is autonumber, the error was always pointing to da.Fill(ds, "john")
Member_ID 的数据类型是自动编号，错误总是指向 da.Fill(ds, "john")
"ds" is a Dataset
“ds”是一个数据集
采纳答案by Olivier Jacot-Descombes
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
更新
The Valfunction will not help in this case. Simply write
Val在这种情况下，该功能将无济于事。简单地写
sqlStr = "SELECT ... WHERE Member_ID = " & userId
If the ID was a string it could contain single quotes that you must escape with double quotes in a SQL string
如果 ID 是一个字符串，它可以包含单引号，您必须在 SQL 字符串中用双引号转义
ID = "John's record"
sqlStr = "SELECT ... WHERE Member_ID = '" & ID.Replace("'", "''") & "'"
' ==> "SELECT ... WHERE Member_ID = 'John''s record'"
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.
这也有助于防止SQL 注入（维基百科）。然而，更专业的方法是使用参数。请参阅MSDN 上的Steve's answer 和DataAdapter Parameters (ADO.NET)（尤其是“OleDb Parameter Placeholders”和“OleDb Example”部分）。
回答by Steve
You should use parameters and this type of problems do not occur.
您应该使用参数并且不会发生此类问题。
sqlStr = "Select Member_ID From tblMemberInfo Where Member_ID = ?"
ds.Clear()
da = New OleDbDataAdapter(sqlStr, con.ConnectionString)
da.SelectCommand.Parameters.AddWithValue("@id", Convert.ToInt32(userID))
da.Fill(ds, "john")
Return ds
