Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6
This is the forum to discuss the Wrox book ASP.NET 2.0 Instant Results by Imar Spaanjaars, Paul Wilton, Shawn Livermore; ISBN: 9780471749516
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 11th, 2012, 08:59 AM
het het is offline
Registered User
Points: 62, Level: 1
Points: 62, Level: 1 Points: 62, Level: 1 Points: 62, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2012
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default Chapter 8 Customer Support Search Function error

Hi Imar

Thanks for your great contribution.

The 'Private Shared Function BuildWhereClause(ByVal searchTerm As String) As String' function in Chapter 8 of the Customer Support Code is working fine with the ...whereClause += "(F.QuestionShort LIKE '%" & myOrSplits(j) & "%' OR F.QuestionLong LIKE '%" & myOrSplits(j) & "%' OR F.Answer LIKE '%" & myOrSplits(j) & "%')"...

I added 20 more nvarchar fields to my sql2005 db table after which I updated my sprocFaqSelectListBySearchTerm to include these 20 fields and updated the above ...whereClause for that extra 20 fields as well. When I use the txtSearchTerm textbox to search for my keyword now, I receive an error - Unclosed quotation mark after the character string '%k
ORDER BY
Id DESC'.
Incorrect syntax near '%k
ORDER BY
Id DESC'.

Can you please help me?

Thank you
  #2 (permalink)  
Old February 11th, 2012, 11:30 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,955
Thanks: 79
Thanked 1,556 Times in 1,533 Posts
Default

Hi there,

Sounds like you're not building up the WHERE clasue correct. Can you post the code that generates it as well as the final clause you're sending to the database?

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #3 (permalink)  
Old February 12th, 2012, 05:10 AM
het het is offline
Registered User
Points: 62, Level: 1
Points: 62, Level: 1 Points: 62, Level: 1 Points: 62, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2012
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default Chapter 8 Customer Support Search Function error

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
  #4 (permalink)  
Old February 12th, 2012, 07:27 AM
Imar's Avatar
Wrox Author
Points: 71,164, Level: 100
Points: 71,164, Level: 100 Points: 71,164, Level: 100 Points: 71,164, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,955
Thanks: 79
Thanked 1,556 Times in 1,533 Posts
Default

Maybe your total Where clause is longer than 500 characters and doesn't fit in the where parameter of the stored procedure? Have you tried increasing its size?

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
het (February 12th, 2012)
  #5 (permalink)  
Old February 12th, 2012, 12:01 PM
het het is offline
Registered User
Points: 62, Level: 1
Points: 62, Level: 1 Points: 62, Level: 1 Points: 62, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2012
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default Chapter 8 Customer Support Search Function error

Hi Imar

Thank you so much for your help! I totally overlooked the size. I have changed the size to 1000 characters and it is working fine now.

Thank you again for your help and for your great contribution in general.
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Customer Support - Can't delete anything mfoof BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 21 June 24th, 2009 05:00 PM
Error-Object does not support doesn't support this bootsy Classic ASP Basics 1 May 25th, 2008 08:14 PM
Customer Support Site Master Pg. Meta Tags taggiese BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 4 February 26th, 2007 11:35 PM
Customer Support overdos2 BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 5 November 30th, 2006 04:54 PM
Customer Support - Adding Second Level Category mfoof BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 3 July 4th, 2006 01:22 AM



All times are GMT -4. The time now is 12:29 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.