Error in Search.aspx.vb after adding date columns in database

I've added two date columns (as datetime, nullable) to the ads database and updated Ads.vb, Ads.xds, PostAds but I'm getting the following error on Search.aspx.vb

Exception Details: System.InvalidOperationException: ObjectDataSource 'AdSearchDataSource' could not find a non-generic method 'GetActiveAdsByQuery' that has parameters: recordLimit, categoryId, memberId, maxPrice, searchTerm, location, adType, adLevel, dayRange, mustHaveImage, city.

Source Error:

Line 786:            BrowsePanel.Visible = False

Line 787:            ResultsPanel.Visible = True
Line 788:            ResultsPanel.DataBind()
Line 789:        Else
Line 790:            ' if no search critieria were specified, show the regular browse category view (as on Default.aspx)

    If (AdvancedSearch.AdType <> 0) Then
                    sb.Append("ad type = """ & CType(AdvancedSearch.AdType, AdType) & """ <br/>")
                End If
                If (AdvancedSearch.MustHavePhotos) Then
                    sb.Append("photo = """ & AdvancedSearch.MustHavePhotos & """ <br/>")
                End If
                If (AdvancedSearch.City.Length > 0) Then
                    sb.Append("city = """ & Server.HtmlEncode(AdvancedSearch.City) & """ <br/>")


                End If

>>>>>>>>>Seems like there should be some reference to eventStart and eventEnd here>>>>>>>>>>

Does anyone know if this is the problem and if so, what goes in here?  Or, maybe I'm looking in the wrong

place altogether.  Would really appreciate any help.  My site is up but search isn't working at all.  Thanks.

Re: Error in Search.aspx.vb after adding date columns in database

It looks like 'GetActiveAdsByQuery' SP doesn't have the correct number of parameters. Check your SP again to ensure it matches the object datasource 'AdSearchDataSource' or visa versa.

Re: Error in Search.aspx.vb after adding date columns in database

My stored procedure is

USE [pgclassifieds1]
GO

/****** Object: StoredProcedure [dbo].[GetAllAdsByQuery] Script Date: 02/02/2010 16:43:30 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetAllAdsByQuery
@LimitResultCount int = 50,
@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,
@City nvarchar(50) = N'',
@EventStart datetime = NULL,
@EventEnd datetime = NULL

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
Location LIKE '%' + @Location + '%' AND
                     City LIKE '%' + @City + '%' AND
                      (@EventStart = -1 OR EventStart = @EventStart) AND
                     (@EventEnd = -1 OR EventEnd = @EventEnd)

ORDER BY DateCreated DESC

SET ROWCOUNT 0

There is no stored procedure for GetActiveAdsByQuery that I can find. Ads.vb does have a section called GetActiveAdsByQuery that matches the xsd section for GetAllAdsByQuery. There's no GetActiveAdsByQuery in this file either.

I added a field before and had a similar problem, but could this be different because I'm adding DateTime fields? Thanks

Thanks, Bev

Re: Error in Search.aspx.vb after adding date columns in database

Then check this datasource 'AdSearchDataSource' and see how it is configured or post the code for that datasource.

Re: Error in Search.aspx.vb after adding date columns in database

I found the GetActiveAdsByQuery in Ads.vb was missing adStatus. I don't know why. It's not something I've messed with. Anyhow, I put it back in and looks like I'm past the line of code that was causing the original problem. Here's the new 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: BC30456: 'Activated' is not a member of 'Integer'.


Source Error:


 



Line 157:             HttpUtility.HtmlEncode(escapedSearchTerm), _
Line 158:             HttpUtility.HtmlEncode(escapedLocation), _
Line 159:             adType, CInt(AdStatus.Activated), _
Line 160:             adLevel, dayRange, mustHaveImage, city, eventStart, eventEnd)
Line 161:
 



Source File: XXX:\XXXXX\XXXXXX\App_Code\BLL\Ads.vb    Line: 159

#Region "GetAds..."

        Public Shared Function GetAdById(ByVal adId As Integer) As AdsDataComponent.AdsRow

            Using db As New AdsDataAdapter()
                Return GetFirstRow(db.GetAdById(adId))
            End Using

        End Function


        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 adStatus As CInt, ByVal adLevel As Integer, ByVal dayRange As Integer, ByVal mustHaveImage As Boolean, ByVal city As String, ByVal eventStart As Date, ByVal eventEnd As Date) 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, CInt(AdStatus.Activated), _
             adLevel, dayRange, mustHaveImage, city, eventStart, eventEnd)

        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 adStatus As Integer, ByVal adLevel As Integer, ByVal dayRange As Integer, ByVal mustHaveImage As Boolean, ByVal city As String, ByVal eventStart As Date, ByVal eventEnd As Date) As AdsDataComponent.AdsDataTable
            Dim minCreatedDate As System.Nullable(Of DateTime)
            If location Is Nothing Then
                location = ""
            End If
            If city Is Nothing Then
                city = ""
            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
            Using db As New AdsDataAdapter()
                Dim aa As AdsDataComponent.AdsDataTable = Nothing
                aa = db.GetAllAdsByQuery(recordLimit, categoryId, memberId, maxPrice, searchTerm, location, adType, adStatus, adLevel, minCreatedDate, mustHaveImage, city, eventStart, eventEnd)
                Return aa
            End Using
        End Function


        Public Shared Function GetPendingAds() As AdsDataComponent.AdsDataTable
            Return GetAdsByStatus(AdStatus.ActivationPending, DefaultValues.IdNullValue)
        End Function


        Public Shared Function GetPendingAds(ByVal memberId As Integer) As AdsDataComponent.AdsDataTable
            Return GetAdsByStatus(AdStatus.ActivationPending, memberId)
        End Function


        Public Shared Function GetActiveAds(ByVal memberId As Integer) As AdsDataComponent.AdsDataTable
            Return GetAdsByStatus(AdStatus.Activated, memberId)
        End Function


        Public Shared Function GetInactiveAds(ByVal memberId As Integer) As AdsDataComponent.AdsDataTable
            Return GetAdsByStatus(AdStatus.Inactive, memberId)
        End Function 'GetInactiveAds


        Public Shared Function GetAdsByStatus(ByVal adStatus As AdStatus, ByVal memberId As Integer) As AdsDataComponent.AdsDataTable

            Using db As New AdsDataAdapter()
                Return db.GetAdsByStatus(CInt(adStatus), memberId)
            End Using

        End Function


        Public Shared Function GetAdsByStatus(ByVal adStatus As AdStatus) As AdsDataComponent.AdsDataTable
            Return GetAdsByStatus(adStatus, DefaultValues.IdNullValue)
        End Function


        Public Shared Function GetFeaturedAdsSelection(ByVal maxNumAds As Integer) As AdsDataComponent.AdsDataTable

            If maxNumAds < 1 Then
                Return Nothing
            End If
            Using db As New AdsDataAdapter()
                Return db.GetAdsByRandomOrder(maxNumAds, CInt(AdStatus.Activated), CInt(AdLevel.Featured))
            End Using

        End Function


        Public Shared Function GetSavedAds(ByVal memberId As Integer) As AdsDataComponent.AdsDataTable

            Using db As New AdsDataAdapter()
                Return db.GetSavedAds(memberId)
            End Using

        End Function
#End Region

Re: Error in Search.aspx.vb after adding date columns in database

I've double and triple checked my database tables, stored procedures, Ads.xsd file and Ads.aspx.vb and can't find anywhere the column order doesn't match. Search doesn't work. I get the following error. But MyAds does return the correct ads, columns in

Line 234:			BrowsePanel.Visible = False

Line 235: ResultsPanel.Visible = True Line 236: ResultsPanel.DataBind() Line 237: Else Line 238: ' if no search critieria were specified, show the regular browse category view (as on Default.aspx)

The AdSearchDataSource is in Search.aspx.vb

' copy the values which the AdSearchDataSource needs on this page (SearchTerm comes from SearchTermTextBox, Category comes from the CategoryDropDown control)

SetCurrentCategory(AdvancedSearch.CategoryId)

SearchTermTextBox.Text = AdvancedSearch.SearchTerm

There's no reference to AdSearchDataSource outside of Search.aspx.vb that I can find. Can anyone help point me in the right direction, please?

Re: Error in Search.aspx.vb after adding date columns in database

The problem lies with this function:



 This function has 14 required parameters and the error you receive:
... GetActiveAdsByQuery that has parameters: recordLimit, categoryId, memberId, maxPrice, searchTerm, location, adType, adLevel, dayRange, mustHaveImage, city.

Only has 11 parameters. So you need to modify where this is called to include those parameters.

What I would do is a Ctrl+F (search) the whole project for GetActiveAdsByQuery. This should show you where all the times you call that function. You could also try debugging and placing a breakpoint on the databind line where you receive the error. This will allow you to step into the databinding event and see if GetActiveAdsByQuery is called and where.

Re: Error in Search.aspx.vb after adding date columns in database

I'm not certain, but I seem to have got by that error. Now I'm getting the following and the site won't load. Not sure if I've fixed anything, or simply added another layer of error. The database does not have a GetActiveAdsByQuery stored procedure and the Ads.xsd doesn't either. Still the search function worked ok before I add the two fields so I don't think that is part of the problem.

There are two references to GetActiveAdsByQuery in the site. 1 is the Search.aspx page

<uc1:AdvancedSearch ID="AdvancedSearch" runat="server" Visible="False"></uc1:AdvancedSearch>
<asp:ObjectDataSource ID="AdSearchDataSource" runat="server" TypeName="AspNet.StarterKits.Classifieds.BusinessLogicLayer.AdsDB"
  SelectMethod="GetActiveAdsByQuery">
  <SelectParameters>
   <asp:Parameter Type="Int32" DefaultValue="5000" 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="AdvancedSearch" PropertyName="AdType"
    Type="Int32" DefaultValue="0" />
   <asp:Parameter Type="Int32" DefaultValue="0" Name="adLevel"></asp:Parameter>
   <asp:ControlParameter Name="dayRange" ControlID="AdvancedSearch" PropertyName="DayRange"
    Type="Int32" DefaultValue="-1" />
   <asp:ControlParameter Name="mustHaveImage" ControlID="AdvancedSearch" PropertyName="MustHavePhotos"
    Type="Boolean" DefaultValue="False" />
            <asp:ControlParameter Name="city" ControlID="AdvancedSearch" PropertyName="City"
                Type="String" DefaultValue="" />
           <asp:ControlParameter Name="eventStart" ControlID="AdvancedSearch" PropertyName="EventStart"

                Type="DateTime" DefaultValue="NULL" />
           <asp:ControlParameter Name="eventEnd" ControlID="AdvancedSearch" PropertyName="EventEnd"
                Type="DateTime" DefaultValue="NULL" />    </SelectParameters>
</asp:ObjectDataSource>

*******When I added EventStart and EventEnd the original error went away and now I'm getting the following error. The only other reference is in Ads.vb

Compiler Error Message: BC30456: 'EventStart' is not a member of 'AdvancedSearch_ascx'.

Source Error:

Line 189:    Public Sub SetEventStart(ByVal eventStart As Date)
Line 190:
Line 191:        Me.EventStart = eventStart
Line 192:        Dim EventStartDate As Date = Me.EventStart
Line 193:        If Not (EventStartDate > 0) Then
Source File: d:\hosting\rporterpg\Controls\AdvancedSearch.ascx.vb    Line: 191

I've tried to copy the code for another column to add EventStart, but it's not correct apparently. I keep thinking if I was making a string rather than a date I could just copy the way city is coded. Not sure if that has anything to do with the problem.

Thanks for your reply.

Re: Error in Search.aspx.vb after adding date columns in database

In case anyone else is working this sort of problem. My solution (cop out maybe) was to start over and add the fields as integers. So instead of StartDate and EndDate, I have StartYear, StartMonth, StartDate and EndMonth, EndDate. As integers I was able to copy an existing field, AdType and make the thing work.

One thing I learned...dates are different.

Re: Error in Search.aspx.vb after adding date columns in database

It sounds to me as if it was a data type problem now.

If these dates are needed later for anything other t han you are using them for, you will regret having the date separated like that.

Re: Error in Search.aspx.vb after adding date columns in database

Hi. Yes, I think it is a data type problem. I just don't have the experience to replicate the various bits of code to deal with dates. There are dates in the classified starter kits, but they are all calculated dates. None is a direct user input date which is what I was trying to do.

I know I may regret my "cop out" later. However, the purpose of the dates is only for sorting/searching and I'm hopeful I'll be able to use the separate fields as integers for that purpose. In any event, I just ran out of time. My website was effectively off the air for over a week and I had to get it back somehow.

Thanks again for your help. Even when I don't entirely understand the answer, it is so helpful to know there is somewhere I can go to ask.

Re: Error in Search.aspx.vb after adding date columns in database

Go into the database and check the Views - which is where the search function is going against. You'll have to add the two date columns to the View definition.

Regards,

Scott

Re: Error in Search.aspx.vb after adding date columns in database

Bev, it's not a datatype problem, just a user problem ;) After you add the extra columns to GetActiveAdsByQuery(), you'll have to add the sameto GetAdsByQuery(),and GetAllAdsByQuery() in your code (the Ads.cs) file. You'll then have to load the Ads Adapter in the Designer and locate the GetAllAdsByQuery method and add the extra parameters to the call using the Parameter Collections Editor. Next to last, you'll have to add them to the stored procedure GetAllAdsQuery. Finally - and this is most likely why your search is crashing, the sp is SELECTing from a View classifiedsView_Ads , which means you'll have to alter the View's definition to add the two date columns in the SELECT statement so that they shouw up in the result set for the procedure to use. Tha's why you're not finding anything (bcs the View is the missing link here). Since they aren't there to begin with, the application is having a fit because it can't reconcile two variable you added with anything it's getting back from the database. I know it's sounds complicate and overwhelming, but take it step by step, trace your program call from Search->Ads->Ads.xsd->StoredProc->View->StoredProc->ObjectDataSource(ie, Ads.xsd)->Ads->Search.

Take a look at the MinDateCreated columns to see how the application is going about using dates. You can use the same logic for you Start/End dates conditions.

Also, try to not to modify ads.xsd by handle unless you know exactly what you're doing. Work with that file with the appropriate designer, it'll result in fewer headaches down the road.

Regards,

Scott