0

I need help to create a search button vb.net to search a access database that is binded in vb.net. I want to search for 2 items in the database and display then in the datagrid. the code i done is here.

Dim da = New OleDb.OleDbDataAdapter()
    Dim con As New OleDb.OleDbConnection()
    Dim sql As String
    Dim ds As New DataSet()
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim Records, current As Integer

    Private Sub count()
        Records = Me.BindingContext(DsProperty1, "tblProperty").Count
        current = Me.BindingContext(DsProperty1, "tblProperty").Position + 1
    End Sub

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
        panEdit.Visible = True
        panAdd.Visible = True

        DsProperty1.Clear()
        OleDbDataAdapter1.Fill(DsProperty1)
        count()
    End Sub
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

        Me.BindingContext(DsProperty1, "tblProperty").Position += 1
        count()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        Me.BindingContext(DsProperty1, "tblProperty").Position = 0
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        Me.BindingContext(DsProperty1, "tblProperty").Position = Me.BindingContext(DsProperty1, "tblProperty").Count - 1
    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        Me.BindingContext(DsProperty1, "tblProperty").Position -= 1
        count()
    End Sub

    'Load the New Record Panel
    Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
        panAdd.Visible = True
        panEdit.Visible = False
        panDisplay.Visible = False
        txtCFName1.Clear()
        txtCSName1.Clear()
        txtAddress1a.Clear()
        txtAddress2a.Clear()
        txtCounty1.Clear()
        txtContact1.Clear()
        txtPrice1.Clear()
    End Sub

    'Add Button, to confirm on creating a new record
    Private Sub btnAdd_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim dsNewRow As DataRow

        current = 0
        count()

        If current <> -1 Then
            dsNewRow = DsProperty1.Tables("tblProperty").NewRow()
            dsNewRow.Item("ClientFName") = txtCFName1.Text
            dsNewRow.Item("ClientSurname") = txtCSName1.Text
            dsNewRow.Item("Address1") = txtAddress1a.Text
            dsNewRow.Item("Address2") = txtAddress2a.Text
            dsNewRow.Item("County") = txtCounty1.Text
            dsNewRow.Item("Contact Number") = txtContact1.Text
            dsNewRow.Item("Property Type") = cboProperty1.Text
            dsNewRow.Item("Price") = txtPrice1.Text
            DsProperty1.Tables("tblProperty").Rows.Add(dsNewRow)
            OleDbDataAdapter1.Update(DsProperty1, "tblProperty")
            MsgBox("New Record added to the Database")

            panAdd.Visible = False
        End If
    End Sub

    'Delete Record Button, to delete a record
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        ' note : record 1 in a database is record 0 not 1 

        count()
        DsProperty1.Tables("tblProperty").Rows(current - 1).Delete()
        'MaxRows = MaxRows - 1 
        'current = 0 
        ' the following line of code update the database to match the dataset - remember the dataset is a mirror of the db 
        OleDbDataAdapter1.Update(DsProperty1, "tblProperty")
        MsgBox("Record deleted from Database")
    End Sub
    'Update Record Button, to Update a record
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            Me.BindingContext(DsProperty1, "tblProperty").EndCurrentEdit()
            Me.OleDbDataAdapter1.Update(DsProperty1)
            'ending current editing and updating the dataset
        Catch
        End Try
        MsgBox("Data updated")
    End Sub
    'Clear Button, to Clear all textboxes
    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        txtCFName1.Clear()
        txtCSName1.Clear()
        txtAddress1a.Clear()
        txtAddress2a.Clear()
        txtCounty1.Clear()
        txtContact1.Clear()
        txtPrice1.Clear()
    End Sub
    'Quit Menu Item
    Private Sub mnuQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuQuit.Click
        Me.Close()
    End Sub
    'New Record Menu Item
    Private Sub mnuNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuNew.Click
        btnNew_Click(sender, e)
    End Sub
    'Delete Record Menu Item
    Private Sub mnuDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuDelete.Click
        btnDelete_Click(sender, e)
    End Sub
    'Update Record Menu Item
    Private Sub mnuUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuUpdate.Click
        btnUpdate_Click(sender, e)
    End Sub
    'Clear Menu Item
    Private Sub mnuClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuClear.Click
        btnClear_Click(sender, e)
    End Sub
    'Confirm Menu Item
    Private Sub mnuConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        btnAdd_Click_1(sender, e)
    End Sub
    'First Menu Item
    Private Sub munFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles munFirst.Click
        btnFirst_Click(sender, e)
    End Sub
    'Last Menu Item
    Private Sub munLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles munLast.Click
        btnLast_Click(sender, e)
    End Sub
    'Next Menu Item
    Private Sub munNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles munNext.Click
        btnNext_Click(sender, e)
    End Sub
    'Previous Menu Item
    Private Sub mnuPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuPrevious.Click
        btnPrevious_Click(sender, e)
    End Sub
    'Quit Button, to Quit Program
    Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuit.Click
        Me.Close()
    End Sub
    'Load Menu Item
    Private Sub mnuLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuLoad.Click
        btnLoad_Click(sender, e)
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim ds As New DataSet()
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        'Laptop 
        'con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = g:\AddressBook.mdb" 
        'CSN 
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = ..\dbPropertys.mdb"
        con.Open()

        'WORKS == sql = "SELECT * FROM tblContacts where tblContacts.Surname ='Blue'" 
        sql = "SELECT * FROM tblProperty WHERE tblProperty.County LIKE = 'Cork' and 'Kerry'"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Filter")

        dtgDisplay.DataSource = ds
        dtgDisplay.DataMember = "Filter"
        con.Close()

    End Sub
End Class
2
Contributors
1
Reply
9
Views
6 Years
Discussion Span
Last Post by __avd
0

>I want to search for 2 items in the database and display then in the datagrid.

You should have to change select query.

SELECT * FROM tblProperty WHERE tblProperty.County='Cork'  or Country='Kerry'

OR

SELECT * FROM tblProperty WHERE tblProperty.County IN ('Cork','Kerry')
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.