[RESOLVED] [2003] Filter Search
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 :
Code:
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 :
Code:
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 :
Code:
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 :
Code:
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" :
Code:
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 :
Code:
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 :
Code:
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 :(
