database 在 VB.Net 中使用文本框和搜索按钮在数据库中查找记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31561090/
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
Finding Records in a Database using a Textbox and Search Button in VB.Net
提问by Matt07211
I am Trying to Create a Library management system, I am a beginner at coding. The Problem I am having is I want to search my books database by title in Visual Basic, using a Textbox and Search button and wanting it to display the results in an seperate form. How would I go about a search in my database in visual basic.
我正在尝试创建一个图书馆管理系统，我是编码初学者。我遇到的问题是我想在 Visual Basic 中按标题搜索我的书籍数据库，使用文本框和搜索按钮并希望它以单独的形式显示结果。我将如何在 Visual Basic 中在我的数据库中进行搜索。
I have imported my database into visual basic. I have used the query below to make it work in Microsoft Access, but couldn't in Visual Basic. The Query I used in Microsoft access was this:
我已将我的数据库导入到 Visual Basic 中。我已经使用下面的查询使其在 Microsoft Access 中工作，但不能在 Visual Basic 中使用。我在 Microsoft access 中使用的查询是这样的：
SELECT Books.[Book ID], Books.Title, Books.Author, Books.Category, Books.Location, Books.[Fiction/Non-Fiction], Books.Loaned
FROM Books
WHERE (((Books.Title) Like [Search Certin Title] & "*"));
Please help me in this regard.
请在这方面帮助我。
采纳答案by Matt07211
I have found someone elses code and have modified it to work with my application http://www.sattsoft.com/sourcecodes/details/1/4/vb-net-add-edit-delete-and-search-data-from-access-database.html
我找到了别人的代码并修改了它以与我的应用程序一起使用 http://www.sattsoft.com/sourcecodes/details/1/4/vb-net-add-edit-delete-and-search-data-from -access-database.html
The Code used is as followed:
使用的代码如下：
Private Sub search_btn_Click(sender As Object, e As EventArgs) Handles search_btn.Click
Searched_Books_frm.Show()
Search_Record()
End Sub
Private Sub Search_Record()
'The Code Below is not Mine, But I modified it to work with my code. This Code below belongs to Christopher Tubig, Code from: http://goo.gl/113Jd7 (Url have been shortend for convenience) User Profile:
Dim conn As New OleDbConnection
Dim cmd As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim dt As New DataTable
Dim sSQL As String = String.Empty
Try
'get connection string declared in the Module1.vb and assing it to conn variable
conn = New OleDbConnection(Get_Constring)
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
sSQL = "SELECT Books.[Book ID], Books.Title, Books.Author, Books.Category, Books.Location, Books.[Fiction/Non-Fiction], Books.Loaned FROM Books"
sSQL = sSQL & " Where Books.Title like '%" & Me.search_txt.Text & "%'"
cmd.CommandText = sSQL
da.SelectCommand = cmd
da.Fill(dt)
Searched_Books_frm.search_datagrid.DataSource = dt
If dt.Rows.Count = 0 Then
MsgBox("No record found!")
End If
Catch ex As Exception
MsgBox(ErrorToString)
Finally
conn.Close()
End Try
End Sub
This works perfectly fine for me :)
这对我来说非常好:)
回答by luke_t
You will need to connect to the database by using either ODBC, ADOor DAOconnectivity. You will need to use a specific connection string, dependant on which option you decide to take.
您将需要使用ODBC、ADO或DAO连接来连接到数据库。您将需要使用特定的连接字符串，具体取决于您决定采用的选项。
ADO (ActiveX Data Objects) uses OLEDB, so you should use one of the Jet or ACE connection strings. Below is an example of connecting to an Access Database using ADO within VBA.
ADO（ActiveX 数据对象）使用 OLEDB，因此您应该使用 Jet 或 ACE 连接字符串之一。下面是在 VBA 中使用 ADO 连接到 Access 数据库的示例。
Option Explicit
Sub queryADO()
Private Const pw = "password"
Dim rsData As ADODB.Recordset, rsConn As ADODB.Connection
Dim strSQL As String, strConn As String
Dim wb As Workbook, ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("mySheet")
strConn = "C:\Users\lturner\Documents\myDatabase.accdb"
Set rsConn = New ADODB.Connection
With rsConn
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
strConn & ";Jet OLEDB:Database Password=" & pwd
.Open
End With
strSQL = "SELECT * FROM myTable"
Set rsData = rsConn.Execute(strSQL)
ws.Range("A1").CopyFromRecordset rsData
Set rsData = Nothing
Set rsConn = Nothing
End Sub
The above will copy the query results into cell A1. You should easily be able to adapt the above to populate a userform with the query results. If you need any help, let me know.
以上将查询结果复制到单元格 A1 中。您应该能够轻松地调整上述内容以使用查询结果填充用户表单。如果您需要任何帮助，请告诉我。
You can find a comparison between ADO, DAO and ODBC connections here.
您可以在此处找到 ADO、DAO 和 ODBC 连接之间的比较。
