[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! :thumb:

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