CODEWITHSUNDEEP
databasevisual-studioms-accessvisual-studio-2012
Matt07211
Matt07211

Reputation: 162

Finding Records in a Database using a Textbox and Search Button in VB.Net

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.

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:

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.

Upvotes: 2

Views: 59078

Answers (2)

Matt07211
Matt07211

Reputation: 162

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

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 :)

Upvotes: 2

luke_t
luke_t

Reputation: 2985

You will need to connect to the database by using either ODBC, ADO or DAO connectivity. You will need to use a specific connection string, dependant on which option you decide to take.

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.

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.

You can find a comparison between ADO, DAO and ODBC connections here.

Upvotes: 1

Related Questions