Hi there,

 I'm using the Classifieds Starter Kit to develop a test website. I found that the normal search can only search the interested term within Ads title. Is there anyway that I can search the Ads description and even other attributes (ie. username)? I'm programming in VB. Any help is highly appreciated.

Thanks in advance.

 Jay

Hello Jay,

It's pretty easy for the most part. The file that does the physical work is in your stored procedure "GetAllAdsByQuery"

I guess if you wanted it to search for description it would look something like this,

@SearchTerm represents the textbox id that is performing the search. So modify as needed.

If you add that to the search term it should allow you to still search for the title in the same box, otherwise replace title with description.

Good Luck, and have fun with it.

Daniel Meis

Hi Daniel,

 Thanks for the quick reply. I tried your idea. The following is the code that i modified (see the bold). But it didn't work for me. Any one knows where else i should change? Thanks.

 Jay

CREATE PROCEDURE GetAllAdsByQuery
@LimitResultCount int = 100,
@CategoryId int = 0,
@MemberId int = 0,
@MaxPrice money = -1,
@SearchTerm nvarchar(50) = N'',
@Location nvarchar(50) = N'',
@AdType int = 0,
@AdStatus int = 0,
@AdLevel int = 0,
@MinDateCreated smalldatetime = NULL,
@MustHaveImage bit = false
AS
 SET ROWCOUNT @LimitResultCount

 SELECT *
 FROM ClassifiedsView_Ads
 WHERE
 (@CategoryId = 0 OR
 CategoryId IN (
 SELECT Id FROM classifieds_Categories
 WHERE Path LIKE
   (SELECT Path
    FROM classifieds_Categories
    WHERE Id = @CategoryId ) + '%'
 )) AND
 (@MaxPrice = -1 OR Price <= @MaxPrice) AND
 (@MustHaveImage = 0 OR PreviewImageId IS NOT NULL) AND
 (@AdStatus = 0 OR AdStatus = @AdStatus) AND
 (@AdLevel = 0 OR AdLevel = @AdLevel) AND
 (@AdType = 0 OR AdType = @AdType) AND
 (@MemberId = 0 OR MemberId = @MemberId) AND
 (@MinDateCreated IS NULL OR DateCreated > @MinDateCreated) AND
 (Title LIKE '%' + @SearchTerm + '%' OR Description LIKE '%' + @SearchTerm + '%') AND
 (Location LIKE '%' + @Location + '%')
    ORDER BY PreviewImageId DESC, DateCreated DESC  
 SET ROWCOUNT 0
GO

Instead of using the OR try copying the same searchterm twice such as.

Description LIKE '%' + @SearchTerm + '%' AND

Title LIKE '%' + @SearchTerm + '%' AND

so you just added the line, I didn't try it but it should work.

I did try that way too. But it didn't work out either. Any other thoughts or anywhere else i'm supposed to make change on? Thanks.

It was such a long time ago I guess I did more than I thought.

But when I added completly new search features I modified that file, and I think you're going to need to add Description to the top part.

The other files I modified was at the bottom of your search page you will find your adsearchdatabase.

And in the BLL folder in the GetAds section I modified the Ads.vb file.

Let me know if you have any trouble.

Good Luck

I looked into three files trying to solve my problem, which are classifieds-add.sql, BLL\Ads.vb, and DAL\Ads.xsd. Unfortunately, I didn't figure out how to modify some of them properly. I supposed that Title and Description are just different table fields. I didn't find anything special for Title compared with Description. Why does a simple line (Title Like '%'+ @SearchTerm + '%') would work for searching a keyword from title but a similar line for Description wouldn't. I'm very new to the field. If you could provide me with more detailed description and/or your complete code, that would be really really helpful to me. Thanks a lot.

All of your .sql files are for uploading your database only, it put's your database together as it was originally, unmodified.

When you are ready to upload your database you can use a tool to generate your .sql with your current database state.

So that file you modified did nothing to help you. And the DAL/Ads.xsd file won't help you search either.

That file just points to the Stored procedure.

So for a Demo of my code there are three files, and you will notice that I have added several to my search,

For your use I would pretty much copy my loading of ZipCode, although in the stored procedure you may want to increase from nvarchar (50) to at least nvarchar (100) I'm not positive but I think it put's limits on the code.

First off you need to modify your stored procedure "GetAllAdsByQuery"

ALTER PROCEDURE GetAllAdsByQuery

@CategoryId int = 0,

@MaxPrice money = -1,

@PriceFrom money = -1,

@BathsQuery money = -1,

@SearchTerm nvarchar(50) = N'',

@AdType int = 0,

@AdStatus int = 0,

@MinDateCreated smalldatetime = NULL,

SELECT *

WHERE

(@CategoryId = 0 OR

SELECT Id FROM classifieds_Categories

WHERE Path LIKE

FROM classifieds_Categories

WHERE Id = @CategoryId ) + '%'

)) AND

(@MaxPrice = -1 OR Price <= @MaxPrice) AND

(@PriceTo = -1 OR Price <= @PriceTo) AND

(@PriceFrom = -1 OR Price >= @PriceFrom) AND

(@BedsQuery = -1 OR Beds >= @BedsQuery) AND

(@BathsQuery = -1 OR Baths >= @BathsQuery) AND

(@SqFeetQuery = -1 OR SqFeet >= @SqFeetQuery) AND

(@MustHaveImage = 0 OR PreviewImageId IS NOT NULL) AND

(@AdStatus = 0 OR AdStatus = @AdStatus) AND

(@AdLevel = 0 OR AdLevel = @AdLevel) AND

(@AdType = 0 OR AdType = @AdType) AND

(@MemberId = 0 OR MemberId = @MemberId) AND

(@MinDateCreated IS NULL OR DateCreated > @MinDateCreated) AND

Location LIKE '%' + @SearchTerm + '%' AND

ZipCode LIKE '%' + @ZipCode + '%' AND

Location LIKE '%' + @Location + '%'

ORDER BY DateCreated DESC

SET ROWCOUNT 0

Next at the bottom of your search.aspx page you need to modify the object database, datasource.

<asp:ObjectDataSource ID="AdSearchDataSource" runat="server" TypeName="AspNet.StarterKits.Classifieds.BusinessLogicLayer.AdsDB"

SelectMethod="GetActiveAdsByQuery" OldValuesParameterFormatString="original_{0}">

<asp:Parameter Type="Int32" DefaultValue="50" Name="recordLimit"></asp:Parameter>

<asp:ControlParameter Name="categoryId" DefaultValue="0" Type="Int32" ControlID="CategoryDropDown"

PropertyName="CurrentCategoryId"></asp:ControlParameter>

<asp:ControlParameter Name="memberId" ControlID="AdvancedSearch" PropertyName="MemberId"

Type="Int32" DefaultValue="0" />

<asp:ControlParameter Name="maxPrice" ControlID="AdvancedSearch" PropertyName="MaximumPrice"

Type="Decimal" DefaultValue="-1" />

<asp:ControlParameter Name="searchTerm" DefaultValue="" Type="String" ControlID="SearchTermTextBox"

PropertyName="Text"></asp:ControlParameter>

<asp:ControlParameter Name="location" ControlID="AdvancedSearch" PropertyName="Location"

Type="String" DefaultValue="" />

<asp:ControlParameter Name="adType" ControlID="AdTypeList" Type="Int32" DefaultValue="0" />

<asp:ControlParameter Name="ZipCode" DefaultValue="" Type="String" ControlID="ZipCodeQueryTB"

PropertyName="Text"></asp:ControlParameter>

<asp:Parameter Type="Int32" DefaultValue="0" Name="adLevel"></asp:Parameter>

<asp:ControlParameter Name="dayRange" ControlID="AdvancedSearch" PropertyName="DayRange"

Type="Int32" DefaultValue="-1" />

<asp:ControlParameter ControlID="PhotoCheckBox" DefaultValue="False" Name="MustHaveImage" Type="Boolean" />

<asp:ControlParameter ControlID="PriceToDropDown" DefaultValue="-1" Name="PriceTo" Type="Decimal" />

<asp:ControlParameter ControlID="PriceFromDropDown" DefaultValue="-1" Name="PriceFrom" Type="Decimal" />

<asp:ControlParameter ControlID="BedsQueryDropDown" DefaultValue="-1" Name="BedsQuery" Type="Decimal" />

<asp:ControlParameter ControlID="BathsQueryDropDown" DefaultValue="-1" Name="BathsQuery" Type="Decimal" />

<asp:ControlParameter ControlID="SqFeetQueryDropDown" DefaultValue="-1" Name="SqFeetQuery" Type="Decimal" />

</SelectParameters>

</asp:ObjectDataSource>

And finally there are two sections in the BLL/Ads.vb file in the get Ads section

Dim escapedSearchTerm As String = AdsDB.EscapeWildcardCharacters(searchTerm)

Public Shared Function GetAdsByQuery(ByVal recordLimit As Integer, ByVal categoryId As Integer, ByVal memberId As Integer, ByVal maxPrice As Decimal, ByVal searchTerm As String, ByVal location As String, ByVal adType As Integer, ByVal ZipCode As String, ByVal adStatus As Integer, ByVal adLevel As Integer, ByVal dayRange As Integer, ByVal mustHaveImage As Boolean, ByVal PriceTo As Decimal, ByVal PriceFrom As Decimal, ByVal BedsQuery As Decimal, ByVal BathsQuery As Decimal, ByVal SqFeetQuery As Decimal) As AdsDataComponent.AdsDataTable

If location Is Nothing Then

location = ""

End If

If dayRange > 0 Then

End If

' To return no results if the searchTerm is empty remove the following

' check on the searchTerm.

If searchTerm Is Nothing Then

searchTerm = ""

End If

If ZipCode Is Nothing Then

ZipCode = ""

End If

Dim aa As AdsDataComponent.AdsDataTable = Nothing

End Using

End Function

That's it, but take note in my second step the ControlID="ZipCodeQueryTB" is pointing at my textbox Id name.

Good Luck

Daniel Meis

Your explaination did make a lot sense to me. I was following your instruction to achieve my goal but didn't quite get there yet. I'm grateful for your guidance and help.

Here is what I did and the error message i got:

In classifieds-add.sql

ALTER PROCEDURE GetAllAdsByQuery  <ALTER was originall CREATE>
@LimitResultCount int = 50,
@CategoryId int = 0,
@MemberId int = 0,
@MaxPrice money = -1,
@SearchTerm nvarchar(50) = N'',
@Location nvarchar(50) = N'',
@AdType int = 0,
@Description ntext(500) = N'',   <I added this line.>
@AdStatus int = 0,
@AdLevel int = 0,
@MinDateCreated smalldatetime = NULL,
@MustHaveImage bit = false
AS
 SET ROWCOUNT @LimitResultCount

 SELECT *
 FROM ClassifiedsView_Ads
 WHERE
 (@CategoryId = 0 OR
 CategoryId IN (
 SELECT Id FROM classifieds_Categories
 WHERE Path LIKE
   (SELECT Path
    FROM classifieds_Categories
    WHERE Id = @CategoryId ) + '%'
 )) AND
 (@MaxPrice = -1 OR Price <= @MaxPrice) AND
 (@MustHaveImage = 0 OR PreviewImageId IS NOT NULL) AND
 (@AdStatus = 0 OR AdStatus = @AdStatus) AND
 (@AdLevel = 0 OR AdLevel = @AdLevel) AND
 (@AdType = 0 OR AdType = @AdType) AND
 (@MemberId = 0 OR MemberId = @MemberId) AND
 (@MinDateCreated IS NULL OR DateCreated > @MinDateCreated) AND
 Title LIKE '%' + @SearchTerm + '%' AND
 Description LIKE '%'+ @Description +'%' AND      <I added this line>
 Location LIKE '%' + @Location + '%'
    ORDER BY DateCreated DESC
   
 SET ROWCOUNT 0
GO

In search.aspx

<asp:ControlParameter Name="location" ControlID="AdvancedSearch" PropertyName="Location"
    Type="String" DefaultValue="" />
   <asp:ControlParameter Name="adType" ControlID="AdvancedSearch" PropertyName="AdType"
    Type="Int32" DefaultValue="0" />
   <asp:ControlParameter Name="description" DefaultValue="" Type="String" ControlID="DescriptionTextBox"    <PropertyName="Text"></asp:ControlParameter>                                                  <I added this line>
   <asp:Parameter Type="Int32" DefaultValue="0" Name="adLevel"></asp:Parameter>
   <asp:ControlParameter Name="dayRange" ControlID="AdvancedSearch" PropertyName="DayRange"
    Type="Int32" DefaultValue="-1" />

In BLL\Ads.vb

 Public Shared Function GetActiveAdsByQuery(ByVal recordLimit As Integer, ByVal categoryId As Integer, ByVal memberId As Integer, ByVal maxPrice As Decimal, ByVal searchTerm As String, ByVal location As String, ByVal adType As Integer, ByVal description As String, ByVal adLevel As Integer, ByVal dayRange As Integer, ByVal mustHaveImage As Boolean) As AdsDataComponent.AdsDataTable

            Dim escapedSearchTerm As String = AdsDB.EscapeWildcardCharacters(searchTerm)
            Dim escapedLocation As String = AdsDB.EscapeWildcardCharacters(location)
            Return GetAdsByQuery(recordLimit, _
             categoryId, memberId, maxPrice, _
             HttpUtility.HtmlEncode(escapedSearchTerm), _
             HttpUtility.HtmlEncode(escapedLocation), _
             adType, Description, CInt(AdStatus.Activated), _
             adLevel, dayRange, mustHaveImage)

        End Function

        Public Shared Function GetAdsByQuery(ByVal recordLimit As Integer, ByVal categoryId As Integer, ByVal memberId As Integer, ByVal maxPrice As Decimal, ByVal searchTerm As String, ByVal location As String, ByVal adType As Integer, ByVal description As String, ByVal adStatus As Integer, ByVal adLevel As Integer, ByVal dayRange As Integer, ByVal mustHaveImage As Boolean) As AdsDataComponent.AdsDataTable
            Dim minCreatedDate As System.Nullable(Of DateTime)
            If location Is Nothing Then
                location = ""
            End If
            If dayRange > 0 Then
                minCreatedDate = DateTime.Now.Subtract(TimeSpan.FromDays(dayRange))
            End If
            ' To return no results if the searchTerm is empty remove the following
            ' check on the searchTerm.
            If searchTerm Is Nothing Then
                searchTerm = ""
            End If
            If Description Is Nothing Then
                Description = ""
            End If

            Using db As New AdsDataAdapter()
                Dim aa As AdsDataComponent.AdsDataTable = Nothing
                aa = db.GetAllAdsByQuery(recordLimit, categoryId, memberId, maxPrice, searchTerm, location, adType, description, adStatus, adLevel, minCreatedDate, mustHaveImage)
                Return aa
            End Using
        End Function

Error given me:

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30311: Value of type 'Integer' cannot be converted to 'System.Nullable(Of Date)'.

Source Error:

Line 180:            Using db As New AdsDataAdapter()
Line 181:                Dim aa As AdsDataComponent.AdsDataTable = Nothing
Line 182:                aa = db.GetAllAdsByQuery(recordLimit, categoryId, memberId, maxPrice, searchTerm, location, adType, description, adStatus, adLevel, minCreatedDate, mustHaveImage)
Line 183:                Return aa
Line 184:            End Using

Alright, I think I got it.

Go into App_Data/DAL and right click on Ads.xsd select view code, one of the sections is GetAllAdsByQuerry, you need to add to that aswell.

Your error just hit me like a brick, sorry I forgot that one.

Don't forget to mark your question as answered when you get it.

I tried to modify that part too. It didn't work out for me still. This problem drives me crazy. I've tried every way i could think of. I even tried to modify the AdvancedSearch control to suit my case. Nothing really helped me out. It's just so strange. Why we don't need to do anything for "Title" other than the simple line (Title LIKE  searthTerm..) in classifieds-add.sql, but the search function always search the title field. In contrast, Desctiption won't be searched anyhow. Aren't they just two different table fields in the database. I didn't see any codes that make "Title" special to other fields. I kinda lost my logic there.

There is only one line you need to modify to get it to seach for description.

In the stored procedure, "GetAllAdsByQuery" just the bottom part change Title to Description.

That's it. There is no other references, the only thing is it takes the place of searching for title.

So it's one or the other there.

If you would like both, untested but the active term is serchterm and you would need to copy that in all references, and then in your search page have searchterm and searchterm1 pointing to the same control. "SearchTermTextBox"