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

 

[jmcilhinney]

You simply need to add an appropriate WHERE clause and then insert the user input into your SQL code using parameters. Follow the Blog link in my signature and check out my post on ADO.NET Parameters for the how and why.

 

[tomanderson91]

Thank you for the reply, However I still do not understand how I am going to make this work

 

[bdinnocenzo]

In your variable "sql" you currently set it to:

sql = "SELECT * FROM tblCameraFormats"

Assuming you have a searchable key like "jpg" for example in a variable called "SearchFormat" that you obtained from the users, you would modify the sql statement to be:

sql = "SELECT * FROM tblCameraFormats WHERE CameraFormats='" & SearchFormat & "'"

Then the resulting ds would only contain records WHERE CameraFormats was equal to "jpg".

 

[tomanderson91]

Okay, I have the following code when my search button is clicked

sql = "SELECT * FROM tblCameraFormats WHERE CameraFormats='" & _
                  searchQuery & "'"

            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "CameraFormatsSearch")

            con.Open()


            con.Close()

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

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

But I get an error:

No value given for one or more required parameters.

 

[bdinnocenzo]

Try it without the single quotes...

sql = "SELECT * FROM tblCameraFormats WHERE CameraFormats=" & searchQuery

 

[jmcilhinney]

Did you read the blog I suggested? I specifically said in that to NOT use string concatenation to build SQL statements.

 

[bdinnocenzo]

Did you read the blog I suggested? I specifically said in that to NOT use string concatenation to build SQL statements.

I didn't, but I just did. Nice read. Use parameters...I got it!

 

[tomanderson91]

I have a new error now:

Syntax error (missing operator) in query expression 'CameraFormat=Acer CR-8530'.

 

[jmcilhinney]

I have a new error now:

Syntax error (missing operator) in query expression 'CameraFormat=Acer CR-8530'.

Have YOU read my blog post? If you're getting that error then you're not using parameters. Errors like that simply can't occur if you do it the right way. That's not to say that everything will work, but you eliminate several sets of possible issues.

 

[tomanderson91]

Thank you for the help I managed to get the search working in a fashion, however I just need a few tips to make it better, If I search the exact thing say Acer CR-8530 it works, however if I search just Acer on its own it doesn't bring anything up, how would I go about that.

 

[jmcilhinney]

You've got '=' in your query so you'll only find records that match exactly. If you want partial matches then you have to use 'LIKE' with appropriate wildcards. A wildcard at the beginning of the value, e.g. "LIKE '%X'", will find records that end with the specified value. A wildcard at the end will find records that start with the value. A wildcard at the beginning and the end would match records that contain the value anywhere.

 

[tomanderson91]

Thank you for replying, however I am slightly confused where and how do I put the wildcards into my statement I keep getting Unknown Character errors.

I have this :

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

 

[jmcilhinney]

There are only so many places the wildcards could go. If that's wrong, what other options do you have?

 

[tomanderson91]

Thanks, I've fixed it now, thanks for all your help, I appreciate the way you helped by not giving me the direct answer but pointed me in the right direction I think its the best way to learn