Article ID: 814071
Article Last Modified on 11/15/2007
APPLIES TO
- Microsoft Commerce Server 2002 Service Pack 1
SYMPTOMS
The results of a query on a virtual catalog do not reflect the set price rule if both a SQLWhereClause property and a FreeTextSearchPhrase property are specified. If only the SQLWhereClause property is set or only the FreeTextSearchPhrase property is set, the returned prices have the price rule applied.
RESOLUTION
A supported hotfix is now available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next service pack that contains this hotfix.
To resolve this problem, submit a request to Microsoft Online Customer Services to obtain the hotfix. To submit an online request to obtain the hotfix, visit the following Microsoft Web site:
Note If additional issues occur or any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. To create a separate service request, visit the following Microsoft Web site:
The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
Date Time Size File name
----------------------------------------------------
05-Mar-2003 20:34 602,939 Catalogcreate.sql
06-Mar-2003 21:16 95,748 Catalogsp2migration.sql
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
MORE INFORMATION
To install this hotfix:
- Run the HotFixSetup.exe program. This replaces the CatalogCreate.sql file. Any new catalog in any language will have the hotfix already installed.
- Open Query Analyzer, and then run the CatalogSP2Migration.sql file. The script updates the stored procedures and drops and re-creates the catalog scratch database.
Steps to Reproduce the Behavior
The following code causes the problem to occur:
private void SimpleCatalogSearch()
{
// This allows you to search a Commerce Server Catalog using 3 filters:
// 1) Category
// 2) Keywords (Free text)
// 3) Price range and product status (SQL Clause)
// This also uses custom paging on a datagrid to only bring back the exact number of records per page.
// Created Waterfront Partners 08/13/02
// INPUTS: HashTable that contains the "search form"
// Category, keyword(s), price range and products per page to display
try
{
// Get the search options from the session.
Hashtable htSearchOpts = (Hashtable)Session[SESSION_CAT_SEARCHCRITERIA];
// Create a Catalog Search class.
CatalogSearch csSearch = new CatalogSearch( CommerceContext.Current.CatalogSystem );
// Set the catalog name.
csSearch.CatalogNames = htSearchOpts["Catalog"].ToString();
// Set the category if any check against the default const.
if ( htSearchOpts["Category"].ToString() != UI_DROPDOWNLIST_DEFAULT_VALUE )
{
csSearch.CategoryName = htSearchOpts["Category"].ToString();
}
// Build the SQL string for further filtering.
// Return only active products.
StringBuilder sbSql = new StringBuilder();
sbSql.Append( "status = 'Active'" );
// Now determine if you have a PRICE RANGE to search on.
if ( ( htSearchOpts["P1"].ToString() != "" ) && ( htSearchOpts["P2"].ToString() != "" ) )
{
sbSql.Append( " and [cy_list_price] between " );
sbSql.Append( htSearchOpts["P1"].ToString() );
sbSql.Append( " and " );
sbSql.Append(htSearchOpts["P2"].ToString();
}
else if ( (htSearchOpts["P1"].ToString() != "" ) && ( htSearchOpts["P2"].ToString() == "" ) )
{
sbSql.Append( " and [cy_list_price] >= " );
sbSql.Append( htSearchOpts["P1"].ToString();
}
else if ( ( htSearchOpts["P1"].ToString() == "" ) && ( htSearchOpts["P2"].ToString() != "" ) )
{
sbSql.Append( " and [cy_list_price] <= " );
sbSql.Append( htSearchOpts["P2"].ToString();
}
// Set the SQL WHERE clause.
csSearch.SqlWhereClause = sbSql.ToString();
// Set the free text phrase (key words), if any.
if ( htSearchOpts["KeyWords"].ToString() != "*" )
{
csSearch.FreeTextSearchPhrase = htSearchOpts["KeyWords"].ToString().Replace( "-", " and " );
}
// Now set up a search options object.
CatalogSearchOptions csOptions = new CatalogSearchOptions();
// Properties to return
csOptions.PropertiesToReturn = "[ProductName], [LongDesc], [ImageSmallFileName], [ImageLargeFileName], [Manufacturer], [Product_ID], [WFP_ID], [SKU], [cy_list_price], [ProductID]";
// Product class to return - no variants here
csOptions.ClassTypes = CatalogClassTypes.ProductClass | CatalogClassTypes.ProductFamilyClass;
// For now sort on Manufacturer, Price ascending
csOptions.SortProperty = "[Manufacturer], [cy_list_price]";
csOptions.SortAscending = true;
// Set the paging stuff.
// The search has been set up to use a datagrid custom paging method.
// Only return the amount of records that will fit on a page.
csOptions.StartingRecord = m_iStartIndex;
csOptions.RecordsToRetrieve = Convert.ToInt32( htSearchOpts["PPP"] );
// Set the options into the search object.
csSearch.SearchOptions = csOptions;
// Set the page size.
// Execute the search and BIND depending on WHERE you are coming in.
// If not a post back then a first time search so set the page count
// in the viewstate and in the datagrid virtual item count.
DataSet daResulst;
int iSearchResults = 0;
daResulst = csSearch.Search(out iSearchResults);
// Set the virtual item count for custom paging.
dgResults.VirtualItemCount = iSearchResults;
if ( iSearchResults > 0 )
{
lblSearchResultMSG.Text = "Your search has found " + iSearchResults.ToString("#,#0") + " product(s).";
SubSequentVisit();
}
else
{
NoResultsFound();
}
dgResults.DataSource = daResulst.Tables[0];
// Set up the paging options for all.
dgResults.PageSize = Convert.ToInt32( htSearchOpts["PPP"] );
dgResults.DataBind();
}
// The following errors are catalog search errors. In all cases we will report to the user
// that no results have been found.
catch ( ArgumentOutOfRangeException )
{
NoResultsFound();
}
catch ( ArgumentNullException )
{
NoResultsFound();
}
catch ( CatalogException ce)
{
Logging.LogError( "Catalog Exception Error - " + ce.Message );
Logging.LogError( "Catalog Inner Exception Error - " + ce.InnerException );
NoResultsFound();
}
catch ( Exception e)
{
Logging.LogError( "Catalog General Error - " + e.Message );
NoResultsFound();
}
}
Keywords: kbhotfixserver kbqfe kbbug kbfix KB814071
