[RESOLVED] Filter Search

[GrimmReaper]

Hello everyone.

I hate SQL.

I am busy making a program that can search through records in a database. The normal search works fine. When I say normal search it means something like this :

AppSearchField = "SELECT * FROM StudentInfo WHERE Class LIKE '%" & AppSearchValue & "%'"

As you can see the LIKE statement enabled me to search for the items I wanted. It works.

Now, there is a slight hiccup. Say for example I wanted to add a search condition like AND OR and NOT - it doesn't seem to work

For example, if I do a search like the above statement I get results for Class 1 and Class 10 - which sometimes I do not want, that is why I decided to add NOT etc.

I made my variables like this :

    Private AppSearchField1 As String
    Private AppSearchField2 As String
    Private AppSearchField3 As String

    Private AppFieldName As String
    Private AppSearchValue1 As String
    Private AppSearchValue2 As String

Then I did this for the AND OR and NOT radio buttons :

    Private Sub rdAND_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rdAND.CheckedChanged, rdOR.CheckedChanged, rdNOT.CheckedChanged
        If rdAND.Checked Then
            AppSearchField3 = " AND "
        End If

        If rdNOT.Checked Then
            AppSearchField3 = " NOT "
        End If

        If rdOR.Checked Then
            AppSearchField3 = " OR "
        End If

    End Sub

On the button that adds this to the existing query I did this :

   Private Sub btnAddCond_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddCond.Click
        Select Case AppFieldName
            Case "StudentNo"
                AppSearchField3 += AppFieldName & " LIKE '%" & AppSearchValue2 & "%'"
            Case "FileNo"
                AppSearchField3 += AppFieldName & " LIKE '%" & AppSearchValue2 & "%'"
            Case "TimeSlot"
                AppSearchField3 += AppFieldName & " LIKE '%" & AppSearchValue2 & "%'"
            Case "Class"
                AppSearchField3 += AppFieldName & " LIKE '%" & "Class " & AppSearchValue2 & "%'"
            Case "StartDate"
                AppSearchField3 += AppFieldName & " = #" & AppSearchValue2 & "#"
            Case "FullNames"
                AppSearchField3 += AppFieldName & " LIKE '%" & AppSearchValue2 & "%'"
            Case "Surname"
                AppSearchField3 += AppFieldName & " LIKE '%" & AppSearchValue2 & "%'"
            Case "Course"
                AppSearchField3 += AppFieldName & " LIKE '%" & AppSearchValue2 & "%'"
            Case "Monthly"
                AppSearchField3 += AppFieldName & " = " & CSng(AppSearchValue2)
            Case "NoPayments"
                AppSearchField3 += AppFieldName & " = " & AppSearchValue2
        End Select
    End Sub

My "Conditional search value" :

  Private Sub txtAdvCond_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtAdvCond.LostFocus
        AppSearchValue2 = txtAdvCond.Text
    End Sub

Then, this was the original query :

   Private Sub cboAdvFieldName_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboAdvFieldName.SelectedIndexChanged
        AppSearchField1 = "SELECT * FROM StudentInfo WHERE "

        Select Case cboAdvFieldName.SelectedIndex
            Case 0
                AppFieldName = "StudentNo"
                AppSearchField2 = "StudentNo LIKE '%" & AppSearchValue1 & "%'"
                '  AppSearchField = "SELECT * FROM StudentInfo WHERE StudentNo LIKE '%" & AppSearchValue & "%'"
            Case 1
                AppFieldName = "FileNo"
                AppSearchField2 = "FileNo LIKE '%" & AppSearchValue1 & "%'"
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE FileNo LIKE '%" & AppSearchValue & "%'"
            Case 2
                AppFieldName = "TimeSlot"
                AppSearchField2 = "TimeSlot LIKE '%" & AppSearchValue1 & "%'"
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE TimeSlot LIKE '%" & AppSearchValue & "%'"
            Case 3
                AppFieldName = "Class"
                AppSearchField2 = AppFieldName & " LIKE '%" & "Class " & AppSearchValue1 & "%'"
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE Class LIKE '%" & AppSearchValue & "%'"
            Case 4
                AppFieldName = "StartDate"
                AppSearchField2 = "StartDate = #" & AppSearchValue1 & "#"
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE StartDate = #" & AppSearchValue & "#"
            Case 5
                AppFieldName = "FullNames"
                AppSearchField2 = "FullNames LIKE '%" & AppSearchValue1 & "%'"
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE FullNames LIKE '%" & AppSearchValue & "%'"
            Case 6
                AppFieldName = "Surname"
                AppSearchField2 = "Surname LIKE '%" & AppSearchValue1 & "%'"
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE Surname LIKE '%" & AppSearchValue & "%'"
            Case 7
                AppFieldName = "Course"
                AppSearchField2 = "Course LIKE '%" & AppSearchValue1 & "%'"
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE Course LIKE '%" & AppSearchValue & "%'"
            Case 8
                AppFieldName = "Monthly"
                AppSearchField2 = "Monthly = " & CSng(AppSearchValue1)
                ' AppSearchField = "SELECT * FROM StudentInfo WHERE Monthly = " & CSng(AppSearchValue)
            Case 9
                AppFieldName = "NoPayments"
                AppSearchField2 = "NoPayments = " & AppSearchValue1
                'AppSearchField = "SELECT * FROM StudentInfo WHERE NoPayments = " & AppSearchValue
        End Select
    End Sub

And execute the new appended query :

    Private Sub btnAdvSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdvSearch.Click

        Try

            Dim oSelCmd As OleDbCommand = New OleDbCommand
            oSelCmd.CommandType = CommandType.Text
            oSelCmd.Connection = oledbAdvSCon
            oSelCmd.CommandText = AppSearchField1 & AppSearchField2 & AppSearchField3
            oledbAdvSCon.Open()
            Dim oDr As OleDbDataReader = oSelCmd.ExecuteReader(CommandBehavior.Default)

            ''''''''''''''Construct DataTable''''''''''''''
            Dim dt As New DataTable
            Dim i As Integer
            Dim count As Integer = oDr.FieldCount - 1

            'add columns
            For i = 0 To count
                dt.Columns.Add(oDr.GetName(i), oDr.GetFieldType(i))
            Next

            'add rows
            Do While oDr.Read()
                Dim r As DataRow = dt.NewRow
                For i = 0 To count
                    r(i) = oDr.Item(i)
                Next
                dt.Rows.Add(r)
            Loop

            Me.dgAdvSearch.DataSource = dt
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            oledbAdvSCon.Close()
        End Try
    End Sub

I keep getting errors that there are errors in my SQL Syntax

All I want to do is to enter 1, then select NOT and enter 10 - to only get class 1's items instead of class 1 and 10

I know I am on the wrong path, that is why I am here for help? Is there any better way to construct this type of conditional query?

I am desperate

[Hack]

Send your sql statement to the immediate window.

What does it look like?

[GrimmReaper]

Hello!

Thanks for your interest.

This is what it produces when I selected NOT :

SELECT * FROM StudentInfo WHERE Class LIKE '%Class 1%' NOT Class LIKE '%Class 10%'

What am I doing wrong?

[Paolo_ituk]

The syntax of your SQL statement is incorrect; it should read:

SELECT * FROM StudentInfo WHERE Class LIKE '%Class 1%' AND NOT Class LIKE '%Class 10%'

[GrimmReaper]

I knew it had to be something simple - I cannot believe I missed the "and Not" part - duh!

Thank you!