Virtual Catalog Search Is Unsuccessful When Both a Free Text and a SQL Query Are Set (814071)

MORE INFORMATION

1. Run the HotFixSetup.exe program. This replaces the CatalogCreate.sql file. Any new catalog in any language will have the hotfix already installed.
2. 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

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();
			}
		}