Question search for items in the database

[kieran82]

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

 

[jmcilhinney]

In future, please post only the code that is relevant to your specific problem. If we have to wade through a lot of irrelevant code then it wastes our time and mankes it less likely that you'll get the help you need.

From what I can see, the issue is here:

sql = "SELECT * FROM tblProperty WHERE tblProperty.County LIKE = 'Cork' and 'Kerry'"

Your SQL syntax is invalid. First up, LIKE and = are mutually exclusive options. Either you want LIKE or you want =, not both. Secondly, each criterion can only compare one column to one value. You can't compare one column to multiple values at the same time. For that you need multiple criteria. Your code should be:

sql = "SELECT * FROM tblProperty WHERE tblProperty.County = 'Cork' OR tblProperty.County = 'Kerry'"

I assumed that you wanted = rather than LIKE because you have no wildcards in your values.

 

[jmcilhinney]

I should also point out that qualifying the columns with the table name when there's only one table in the query is pointless and only makes your code harder to read, so:

sql = "SELECT * FROM tblProperty WHERE County = 'Cork' OR County = 'Kerry'"

 

[r3plica]

yeah I agree, but if you were to use multiple tables then you would do it like so:

SELECT a.*
FROM tblProperty AS a WITH (NOLOCK)
INNER JOIN tblOther AS b WITH (NOLOCK)
ON a.ID = b.PropertyID
WHERE UPPER(a.County) LIKE 'CORK%'
AND UPPER(a.County) LIKE 'KERRY%'

it's also worth noting that you can combine your original expression like this:

SELECT * FROM tblProperty WHERE County IN ('Cork', 'Kerry')