在Access数据库中搜索多个字段以进行匹配 [英] Searching Multiple Fields In Access Database For Match
问题描述
基本上我想要实现的是正确的select语句来搜索我的访问数据库表中的每个字段或列。
公共 共享 功能 Access_Find_Match( ByVal MatchString As String ) As Boolean
Dim Access_Command 作为 新 OleDbCommand( SELECT * FROM Table WHERE Field0 ='& MatchString& ' 或 Field1 ='&am磷; MatchString& ' 或 Field2 ='& MatchString& ,Access_Database)
使用 Access_Datareader As OleDbDataReader = Access_Command.ExecuteReader
返回 CStr (Access_Datareader.Read)
结束 使用
结束 功能
使用或尝试搜索每个字段时出现错误
转换来自 字符串 SELECT * FROM Table WHERE Field0 to type ' Long' 是无效。
如果我将或更改为&我收到错误
语法错误(缺少 operator )查询表达式中的class =code-keyword> ' Field0 =' MatchString ' Field1 =' MatchString ' field3 =' MatchString ' 。
在这种情况下使用的正确SELECT字符串或语法中存在什么错误?是可能由函数的返回类型引起的错误?
非常感谢任何帮助!
SQL查询应该像
SELECT *
FROM Tabl e WHERE Field0 = ' < somestringvalue> ;'
OR Field1 = ' < somestringvalue>'
或 Field2 = ' < somestringvalue>'< / somestringvalue>< / somestringvalue>< / somestringvalue>
您的代码应该类似于
Dim Access_Command < span class =code-keyword> As 新 OleDbCommand( String .Format(< span class =code-string> SELECT * FROM Table WHERE Field0 ='{0)'或Field1 ='{0} OR FIELD2 ='{0} ',MatchString),Access_Database);
H如果上述方法容易出现SQL注入攻击,则需要参数化查询。
尝试使用以下内容,
Dim Access_Command As 新 OleDbCommand( SELECT * FROM Table WHERE Field0 ='& MatchString& '或Field1 ='& MatchString& '或Field2 ='& MatchString& ,Access_Database)
你最好使用sql参数来避免sql注入攻击。那么,
Dim Access_Command As < span class =code-keyword>新 OleDbCommand( SELECT * FROM Table WHERE Field0 =?或Field1 =?或Field2 =?,Access_Database)
Access_Command.Parameters.AddWithValue( p1,MatchString)
Access_Command.Parameters.AddWithValue( p2 ,MatchString)
Access_Command.Parameters.AddWithValue( p3,MatchString)
Basically what I am trying to achieve is the proper select statement to search each field or column in my access database table.
Public Shared Function Access_Find_Match(ByVal MatchString As String) As Boolean
Dim Access_Command As New OleDbCommand("SELECT * FROM Table WHERE Field0 = '" & MatchString & "'" Or "Field1 = '" & MatchString & "'" Or "Field2 = '" & MatchString & "'", Access_Database)
Using Access_Datareader As OleDbDataReader = Access_Command.ExecuteReader
Return CStr(Access_Datareader.Read)
End Using
End Function
I get a error when using the "Or" to try and search each field
Conversion from string "SELECT * FROM Table WHERE Field0" to type 'Long' is not valid.
If I change the "Or" to "&" I get the error
Syntax error (missing operator) in query expression 'Field0= 'MatchString'Field1= 'MatchString'field3= 'MatchString'.
what would be the correct SELECT string to use in this case or what error exists in the syntax?, is the error possibly caused by the return type of the function?
Any help with this is greatly appreciated!
The SQL Query should be like
SELECT * FROM Table WHERE Field0 = '<somestringvalue>' OR Field1 = '<somestringvalue>' OR Field2 = '<somestringvalue>'</somestringvalue></somestringvalue></somestringvalue>
Your code should look like
Dim Access_Command As New OleDbCommand(String.Format("SELECT * FROM Table WHERE Field0 = '{0)' OR Field1 = '{0} OR FIELD2 = '{0}'", MatchString), Access_Database);
However the above approach is error prone to SQL Injection Attacks, you need to parameterize your query.
try with below,
Dim Access_Command As New OleDbCommand("SELECT * FROM Table WHERE Field0 = '" & MatchString & "' Or Field1 = '" & MatchString & "' Or Field2 = '" & MatchString & "'", Access_Database)
You better use sql parameters to avoid sql injection attacks. then,
Dim Access_Command As New OleDbCommand("SELECT * FROM Table WHERE Field0 = ? Or Field1 = ? Or Field2 = ?", Access_Database) Access_Command.Parameters.AddWithValue("p1",MatchString) Access_Command.Parameters.AddWithValue("p2",MatchString) Access_Command.Parameters.AddWithValue("p3",MatchString)
这篇关于在Access数据库中搜索多个字段以进行匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!