在VB.Net中使用文本框和搜索按钮查找数据库中的记录

Question

我正在尝试创建一个图书馆管理系统，我是一个编码初学者。我遇到的问题是，我想在Visual Basic中按标题搜索我的图书数据库，使用一个文本框和搜索按钮，并希望它以单独的形式显示结果。在visual basic中如何在数据库中进行搜索?

我已经导入我的数据库到visual basic。我使用下面的查询使其在Microsoft Access中工作，但不能在Visual Basic中工作。我在Microsoft访问中使用的查询是:

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] & "*"));

请在这方面帮助我。

Answer 1

我找到了别人的代码，并修改了它以配合我的应用程序http://www.sattsoft.com/sourcecodes/details/1/4/vb-net-add-edit-delete-and-search-data-from-access-database.html

使用的代码如下:

  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

这对我来说很好:)

Answer 2

您需要通过使用ODBC、ADO或DAO连接连接到数据库。您将需要使用一个特定的连接字符串，这取决于您决定采用哪个选项。

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:UserslturnerDocumentsmyDatabase.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

上面的代码将把查询结果复制到单元格A1中。您应该能够轻松地调整上面的内容，用查询结果填充用户表单。如果你需要任何帮助，请告诉我。

您可以在这里找到ADO、DAO和ODBC连接的比较。