vb.net 如何使用代码在VB中搜索access数据库的多个字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33304042/
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
How to search multiple fields of an access database in VB using code
提问by Tyler_Coder
I have created a program which displays data from different tables in an access database(without the use of wizards to connect to the database. i.e. all connection have been hard coded), I have created a simple search which searches through one field in one table. However I would like to make a function which allows me to search through all of the fields in a table. Below is the simple search function I have created.
我创建了一个程序，它在访问数据库中显示来自不同表的数据（不使用向导连接到数据库。即所有连接都已硬编码），我创建了一个简单的搜索，它搜索一个表中的一个字段. 但是我想创建一个函数，它允许我搜索表中的所有字段。下面是我创建的简单搜索功能。
Public Sub Search()
con.Open()
Dim dt As New DataTable("Table1")
Dim rs As New OleDb.OleDbDataAdapter("Select * from Table1 where FirstName = '" & txtTabel1.Text & "'", con)
rs.Fill(dt)
dgvTabel2.DataSource = dt
dgvTabel2.Refresh()
rs.Dispose()
con.Close()
end sub
The new function should look something like this
新功能应该是这样的
Public Sub SearchHard(TableName As String)
con.Open()
Dim dt As New DataTable("TableName")
Dim rs As New OleDb.OleDbDataAdapter(("Select * from " & TableName & " where FirstName = '" & txtTabel1.Text & "'") Or ("Select * from " & TableName & " where LastName = '" & txtTabel1.Text & "')"), con)
'SELECT * FROM MyTable WHERE FirstName LIKE '% txtTable1.text %' OR LastName LIKE '%txtTable1.tetx%'
rs.Fill(dt)
dgvTabel1.DataSource = dt
dgvTabel1.Refresh()
rs.Dispose()
con.Close()
End Sub
It would also be a big plus if the function could accept a parameter to select a different table to reduce overusing the code. Any and all help would be much appreciated
如果该函数可以接受一个参数来选择不同的表以减少过度使用代码，那也将是一个很大的优势。任何和所有的帮助将不胜感激
Working code to with searches.
用于搜索的工作代码。
con.Open()
Dim dt As New DataTable("Table1")
Dim rs As New OleDb.OleDbDataAdapter("SELECT * FROM Table1 WHERE (FirstName = '" & txtTabel1.Text & "') or (LastName = '" & txtTabel1.Text & "')", con)
rs.Fill(dt)
dgvTabel1.DataSource = dt
dgvTabel1.Refresh()
rs.Dispose()
con.Close()
采纳答案by Andre
You have some wrong quotes in there:
你在那里有一些错误的报价：
"Select * from Table where Field1 = '" & txtTabel1.Text & "'" or Field2 = '" & txtTabel1.Text"'"
should be
应该
"Select * from Table where Field1 = '" & txtTabel1.Text & "' or Field2 = '" & txtTabel1.Text & "'"
Now of course you could use your parameter TableNamelike this
现在当然你可以TableName像这样使用你的参数
"Select * from " & TableName & " where Field1 = '" & txtTabel1.Text & "'"
But different tables will have different field names, so that wouldn't really work. In the end you'd have to add lots of parameters and sort of re-invent SQL.
但是不同的表会有不同的字段名称，所以这不会真正起作用。最后，您必须添加大量参数并重新发明 SQL。
A better solution would be to pass the full SQL SELECT string to your function.
更好的解决方案是将完整的 SQL SELECT 字符串传递给您的函数。
回答by T.S.
On top of what was already mentioned here by @andre451, you could use unionif you want to search multiple tables
除了@andre451 在这里已经提到的内容之外，union如果要搜索多个表，可以使用
SELECT field_1 , field_2
FROM table_1
WHERE field_3 = 'value' or field_4 = 'value'
UNION [ALL]
SELECT field_100 , field_200
FROM table_2
WHERE field_300 = 'value' or field_400 = 'value'
As long as your field_1and field_100match data types and count of fields matches
只要您field_1和field_100匹配的数据类型和字段数匹配
