Search Database

[tomanderson91]

Hello, I am making a small application that connects to a database and gets the information displays it in a few labels and text boxes..

I want to add a search feature so it only shows results based on the search, but I have no idea where to start. Please help

I have added the code I have so far.

Imports System.Data
Public Class frmMain
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Tom\Documents\Visual Studio 2008\Projects\CameraFormatFinder\CameraFormatFinder.mdb;Persist Security Info=True;Jet OLEDB:Database Password=jjtrtg78"
        sql = "SELECT * FROM tblCameraFormats"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "CameraFormats")

        con.Open()
        MessageBox.Show("Connected")

        con.Close()
        MessageBox.Show("Disconnected")

        lblCameraName.Text = ds.Tables("CameraFormats").Rows(0).Item(1)
        lblCameraType.Text = ds.Tables("CameraFormats").Rows(0).Item(2)
        txtDefaultFormat.Text = ds.Tables("CameraFormats").Rows(0).Item(3)
        txtCameraHelp.Text = ds.Tables("CameraFormats").Rows(0).Item(4)

        MaxRows = ds.Tables("CameraFormats").Rows.Count
        inc = -1

    End Sub

    Private Sub NavigateRecords()
        lblCameraName.Text = ds.Tables("CameraFormats").Rows(inc).Item(1)
        lblCameraType.Text = ds.Tables("CameraFormats").Rows(inc).Item(2)
        txtDefaultFormat.Text = ds.Tables("CameraFormats").Rows(inc).Item(3)
        txtCameraHelp.Text = ds.Tables("CameraFormats").Rows(inc).Item(4)

    End Sub
    Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
        Me.Close()

    End Sub

    Private Sub cmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNext.Click
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MessageBox.Show("End of Formats", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        End If
    End Sub

    Private Sub cmdLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLast.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        Else
            MessageBox.Show("End of Formats", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If
    End Sub

    Private Sub cmdLastResult_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLastResult.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()

        End If
    End Sub

    Private Sub cmdFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()

        End If
    End Sub

    Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click


    End Sub
End Class

 

[tomanderson91]

Sorry to bother you guys again, but I just found another problem, whenever I search once say 'Canon' it brings up anything related to canon, however If i then search Acer only the Canon results stay up there.

 

[bdinnocenzo]

When you say the "results stay up there", what is "there"? Do you have a form that displays the results? When you re-run the query (search) I assume the results are in a dataset. Does the dataset change? Are you assigning it correctly?

 

[tomanderson91]

Okay sorry I was vague in my explanation, when the form loads there is a search box, if you search Canon then the results will be canon which you can browse through using Next and Last Buttons, however if after the first search I want to search again it doesn't work it stays with the first results brought up.

That is a screenshot of my form if that helps.

 

[bdinnocenzo]

The screen shot is nice but it doesn't tell me about the code behind the controls. You say it works the first time, so I'll assume your logic for accessing the database and running the query and displaying the information basically works. So as I understand your problem, when you change the search criteria, say from "can" to "acer", click search, the displayed information stays the same...meaning clicking previous and next continue to show you the results of the first or initial search. If that's the case then however you "load" and manipulate the dataset from the query must not be reflecting the new query.

So now you need to fill us in on the logic...show us some code always helps.

 

[tomanderson91]

Okay sorry, Here is the code for the search button

 Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSearch.Click

        If txtSearch.Text = "" Then
            MessageBox.Show("Error: You must enter a search query", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else


            Dim searchQuery As String
            searchQuery = StrConv(txtSearch.Text, VbStrConv.None)

            'If searchQuery = "canon" Then
            '    MessageBox.Show("Your search returned no results, however you have searched with the word 'Canon' the most common format that Canon uses is IMG_####.jpg", "Search Result", MessageBoxButtons.OK, MessageBoxIcon.Information)
            'End If

            'lblResults.Text = "Search: Complete"

            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Tom\Documents\Visual Studio 2008\Projects\CameraFormatFinder\CameraFormatFinder.mdb;Persist Security Info=True;Jet OLEDB:Database Password=jjtrtg78"

            sql = "SELECT * " & _
                   "FROM tblCameraFormats " & _
                   "WHERE CameraName LIKE '%" & _
                   txtSearch.Text & _
                   "%'"

            da = New OleDb.OleDbDataAdapter(sql, con)

            Try
                da.Fill(ds, "SearchResults")
            Catch ex As Exception
                MessageBox.Show("An Error has occured", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            End Try


            con.Open()


            con.Close()
            Try
                lblCameraName.Text = ds.Tables("SearchResults").Rows(0).Item(1)
                lblCameraType.Text = ds.Tables("SearchResults").Rows(0).Item(2)
                txtDefaultFormat.Text = ds.Tables("SearchResults").Rows(0).Item(3)
                txtCameraHelp.Text = ds.Tables("SearchResults").Rows(0).Item(4)
            Catch es As Exception
                MessageBox.Show("There was no results returned from your search, please redefine your query", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            MaxRows = ds.Tables("SearchResults").Rows.Count
            inc = -1

            cmdLastSearch.Visible = True
            cmdNextSearch.Visible = True
            cmdFirst.Visible = False
            cmdLast.Visible = False
            cmdLastResult.Visible = False
            cmdNext.Visible = False
            cmdBrowseAll.Visible = False

            GroupBox1.Text = "Search Results:"


        End If

 

[bdinnocenzo]

I haven't looked at all the code, but one thing that would probably fix your problem and fix a "memory leak" is you create a new instance of da every time you enter the search button click routine and never clean up after you use it.

Since you use the da outside of just the search routine -- previous and next I assume -- you should instantiate it at the form level and "re-load it" each time you run the new query.

 

[tomanderson91]

Sorry to sound stupid, but I am kind of new to all of this if you couldn't tell, but how would I go about reloading it

 

[bdinnocenzo]

Sorry if I confused you. When I say reload the da all you need to do is read the database again with the new sql string...

da = New OleDb.OleDbDataAdapter(sql, con)

 

[tomanderson91]

I already have that line: da = New OleDb.OleDbDataAdapter(sql, con) in the click event for my search button, so I'm not sure where I am suppose to put it.

 

[bdinnocenzo]

The problem of not getting relative search results seems to happen with your previous and next click events. Can you post the code for those?

 

[tomanderson91]

Next Button:

 If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords_Search()
        Else
            MessageBox.Show("End of Formats", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        End If

Last Button:

  If inc > 0 Then
            inc = inc - 1
            NavigateRecords_Search()
        Else
            MessageBox.Show("End of Formats", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End If

and the NavigateRecords_Search() is:

Private Sub NavigateRecords_Search()
        lblCameraName.Text = ds.Tables("SearchResults").Rows(inc).Item(1)
        lblCameraType.Text = ds.Tables("SearchResults").Rows(inc).Item(2)
        txtDefaultFormat.Text = ds.Tables("SearchResults").Rows(inc).Item(3)
        txtCameraHelp.Text = ds.Tables("SearchResults").Rows(inc).Item(4)

    End Sub