[RESOLVED] [2003] 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

[viperbyte]

Howdy Grim Reaper.

I would set a break point and see what SQL statement is being generated. Then you can One see if it's what you expected and wanted generally. And if it is then take care of the syntax. And if it's not what you wanted or expected then you can take a closer look at the logic. You should look at the value being generated with oSelCmd.CommandText = AppSearchField1 & AppSearchField2 & AppSearchField3.

[dglienna]

These might have to be PUBLIC, depending on where they are. They would lose scope, and equal NOTHING. That would hose your query

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

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

[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?

[Cimperiali]

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

this does the trick in sql server 2008

[DataMiser]

You realize that you would get class 11-19 if they exist as well as class 1 right?

[Cimperiali]

and also you would not get Class 101-109
or Class 10A...

[GrimmReaper]

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

this does the trick in sql server 2008

So simple - I cannot believe I missed the "And Not" part! Thank you!

You realize that you would get class 11-19 if they exist as well as class 1 right?

and also you would not get Class 101-109
or Class 10A...

Yes to both

I am only making use of only 10 classes, there is also not a chance for having more classes Thank you for your concerns!

[HanneSThEGreaT]

Glad you got it solved!

Please mark your thread as resolved as explained here :

http://www.codeguru.com/forum/showthread.php?t=403073

[GrimmReaper]

Glad you got it solved!

Please mark your thread as resolved as explained here :

http://www.codeguru.com/forum/showthread.php?t=403073

I forgot. DOne.