Hi Imar
Below is my different search terms and the results I am getting. I have a table 'Persons' where I have a nvarchar(50) 'lastName' field with a lastName of 'Koekemoer' which I then search for. I used the same 'Faq' related (function) names as in the original Customer Support Code. It seems that the longer my whereClause (more fields) then I have to shorten (less characters) my search word to prevent an error - See my comments in the code below. I am sorry for this large qty of text below.
Code:
Public Shared Function GetFaqList(ByVal searchTerm As String) As DataSet
Dim myDataSet As DataSet = New DataSet()
Try
Using myConnection As New SqlConnection(AppConfiguration.ConnectionString)
Dim myCommand As SqlCommand = New SqlCommand("sprocFaqSelectListBySearchTerm", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim whereClause As String = BuildWhereClause(searchTerm)
myCommand.Parameters.AddWithValue("@whereClause", whereClause)
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter()
myDataAdapter.SelectCommand = myCommand
myDataAdapter.Fill(myDataSet)
myConnection.Close()
Return myDataSet
End Using
Catch ex As Exception
' Pass up the error; it will be caught by the code in the Global.asax and the generic error page set up in
web.config.
Throw
End Try
End Function
Private Shared Function BuildWhereClause(ByVal searchTerm As String) As String
Dim simpleSearch As Boolean = True
Dim whereClause As String = String.Empty
searchTerm = searchTerm.Trim()
searchTerm = searchTerm.Replace("'", "''")
searchTerm = searchTerm.Replace("""", "")
searchTerm = searchTerm.Replace("%", "")
searchTerm = searchTerm.Replace("--", "")
searchTerm = searchTerm.Replace(";", "")
searchTerm = searchTerm.Replace("(", "")
searchTerm = searchTerm.Replace(")", "")
searchTerm = searchTerm.Replace("_", "")
' Change string, so simple spaces are considered AND operators
Dim testReplace As String = ""
testReplace = searchTerm.ToUpper().Replace(" AND ", "")
If testReplace <> searchTerm.ToUpper() Then
' String does contain AND
simpleSearch = False
End If
testReplace = searchTerm.ToUpper().Replace(" OR ", "")
If testReplace <> searchTerm.ToUpper() Then
' String does contain OR
simpleSearch = False
End If
If simpleSearch = True Then
searchTerm = searchTerm.Replace(" ", " AND ")
End If
Dim myAndSplits() As String = Regex.Split(searchTerm, " and ", RegexOptions.IgnoreCase)
For i As Integer = 0 To myAndSplits.Length - 1
Dim myOrSplits() As String = Regex.Split(myAndSplits(i), " or ", RegexOptions.IgnoreCase)
whereClause += "("
For j As Integer = 0 To myOrSplits.Length - 1
'whereClause only 3 fields
'search word = koekemoer1234567890123456789012345 -no error
'-no error in general
'whereClause += "(F.lastName LIKE '%" & myOrSplits(j) & "%' OR F.firstName LIKE '%" & myOrSplits(j) & "%' OR F.IdNumber LIKE '%" & myOrSplits(j) & "%')"
'whereClause 15 fields
'search word = koekemoe -no error (8 chars)
'search word = koekemoer1 -error (10 chars)
'whereClause += "(F.lastName LIKE '%" & myOrSplits(j) & "%' OR F.firstName LIKE '%" & myOrSplits(j) & "%' OR F.IdNumber LIKE '%" & myOrSplits(j) & "%' OR F.companyNumber LIKE '%" & myOrSplits(j) & "%' OR F.tagNumber LIKE '%" & myOrSplits(j) & "%' OR F.address1 LIKE '%" & myOrSplits(j) & "%' OR F.address2 LIKE '%" & myOrSplits(j) & "%' OR F.cellIn
LIKE '%" & myOrSplits(j) & "%' OR F.cellOut LIKE '%" & myOrSplits(j) & "%' OR F.email LIKE '%" & myOrSplits(j) & "%' OR F.shift LIKE '%" & myOrSplits(j) & "%' OR F.remarks LIKE '%" & myOrSplits(j) & "%' OR F.bankName LIKE '%" & myOrSplits(j) & "%' OR F.bankAccount LIKE '%" & myOrSplits(j) & "%' OR F.position LIKE '%" & myOrSplits(j) & "%')"
'whereClause 16 fields
'search word = koekemo -no error (7 chars)
'search word = koekemoe -error (8 chars)
'whereClause += "(F.lastName LIKE '%" & myOrSplits(j) & "%' OR F.firstName LIKE '%" & myOrSplits(j) & "%' OR F.IdNumber LIKE '%" & myOrSplits(j) & "%' OR F.companyNumber LIKE '%" & myOrSplits(j) & "%' OR F.tagNumber LIKE '%" & myOrSplits(j) & "%' OR F.address1 LIKE '%" & myOrSplits(j) & "%' OR F.address2 LIKE '%" & myOrSplits(j) & "%' OR F.cellIn
LIKE '%" & myOrSplits(j) & "%' OR F.cellOut LIKE '%" & myOrSplits(j) & "%' OR F.email LIKE '%" & myOrSplits(j) & "%' OR F.shift LIKE '%" & myOrSplits(j) & "%' OR F.remarks LIKE '%" & myOrSplits(j) & "%' OR F.bankName LIKE '%" & myOrSplits(j) & "%' OR F.bankAccount LIKE '%" & myOrSplits(j) & "%' OR F.position LIKE '%" & myOrSplits(j) & "%' OR F.contract LIKE '%" & myOrSplits(j) & "%')"
'whereClause 17 fields
'search word = koeke -no error (5 chars)
'search word = koekem -error (6 chars)
'whereClause += "(F.lastName LIKE '%" & myOrSplits(j) & "%' OR F.firstName LIKE '%" & myOrSplits(j) & "%' OR F.IdNumber LIKE '%" & myOrSplits(j) & "%' OR F.companyNumber LIKE '%" & myOrSplits(j) & "%' OR F.tagNumber LIKE '%" & myOrSplits(j) & "%' OR F.address1 LIKE '%" & myOrSplits(j) & "%' OR F.address2 LIKE '%" & myOrSplits(j) & "%' OR F.cellIn
LIKE '%" & myOrSplits(j) & "%' OR F.cellOut LIKE '%" & myOrSplits(j) & "%' OR F.email LIKE '%" & myOrSplits(j) & "%' OR F.shift LIKE '%" & myOrSplits(j) & "%' OR F.remarks LIKE '%" & myOrSplits(j) & "%' OR F.bankName LIKE '%" & myOrSplits(j) & "%' OR F.bankAccount LIKE '%" & myOrSplits(j) & "%' OR F.position LIKE '%" & myOrSplits(j) & "%' OR F.contract LIKE '%" & myOrSplits(j) & "%' OR F.allocationRig LIKE '%" & myOrSplits(j) & "%')"
If (j + 1) < myOrSplits.Length Then
whereClause += " OR "
End If
Next
whereClause += ") "
If (i + 1) < myAndSplits.Length Then
whereClause += " AND "
End If
Next
Return whereClause
End Function
My Stored Procedure looks like this:
ALTER PROCEDURE sprocFaqSelectListBySearchTerm
@whereClause nvarchar(500)
AS
DECLARE @sqlStatement nvarchar(MAX)
SET @sqlStatement = 'SELECT Id, lastName, firstName, IdNumber, companyNumber, tagNumber, address1, address2, cellIn, cellOut, email, shift, issueDate, expireDate, remarks, downloadUrl, bankName, bankAccount, position, contract, allocationRig, field1, field2, field3, field4, field5 FROM Persons F
WHERE ' + @whereClause
+ '
ORDER BY
Id DESC'
EXEC(@sqlStatement)
RETURN
Thank you
Chapter 8 Customer Support Search Function error
button below this post to show your appreciation!
