Ranking Categories under the catalog root
I'm having trouble getting Commerce Server to accept the ranks I assign to Categories that are children of the catalog root, ie the first category level..I have a hashtable that is keyed by Category Name and contains a rank as the value, and I want to update all categories in that hashtable to be the assigned rank. The code works fine for all Categories that are not the root, but the same code is failing if I try to rank the main catalog categories (specifically it's telling me it succeeded and all category ranks reset to 0). Here is a sample of the code I'm using to accomplish the rank updates.
StaticCategory parentCategory;
CatalogItemsDataSet.CatalogItemsDataTable categoriesTable =null;parentCategory = (StaticCategory)Catalog.GetRootCategory(catalogName);
categoriesTable = parentCategory.ChildCategories.DataSet.CatalogItems;
foreach(string categoryName in hashTableOfCategoriesWithRankAssignments)
{
DataRow[] result =categoriesTable.Select(CatalogItemsDataSetSchema.CategoryName +" = '" +UtilityFunctions.SQLSafeQuote(categoryName) +"'");
result[0][CatalogItemsDataSetSchema.Rank] = (int)hashTableOfCategoriesWithRankAssignments[categoryName];
}
parentCategory.Save();
I have tried using just the standard Category as well with no luck. is this a known bug in Commerce Server? or am I doing this wrong?
Thanks,
Dave
[2953 byte] By [
DMAR330] at [2007-12-27]
Anyone on this? Anyone else even seeing this problem?
Ranking Categories underneath the root Catalogs not working?
Bueller? :)
Thanks,
Dave
Have you tried to do this using the Catalog Manager UI?
Sudha,
Yes it is working if I sequence using the Catalog Manager UI, but our system is controlled solely in the application itself, and the Documentation I looked at specified to get the root category the way I have done it in the code above. Is there anything in particular wrong with the code I have? Should I be using a different collection object to set these ranks?
Thanks,
Dave
Are your sure you are getting the right root category?
parentCategory = (StaticCategory)Catalog.GetRootCategory(catalogName);
Assuming that "catalogName" is a string, there is no such overloaded method on GetRootCategory that takes a "string" argument. If not what is "catalogName"?
Sudha,
I should mention that my "Catalog" above is a shortcut variable for CommerceContext.Current.CatalogContext.
I am using the CatalogContext.GetRootCategory() method, which does have an overload for a single string input which is the catalog name to retrieve the root category for. And catalogName is indeed a string variable. The function gives me back a Category object and I am modifying the ChildCategories Dataset of that object to set the ranks of the categories that lie under the main catalog.
One thing I am noticing now in my reading on MSDN is that it states that the root category cannot be modified, and thus, probably the Save() method will do nothing for that category. But since ranks are updated once the parent category is saved, as defined in the sequencing and ranking catalog items pattern, how will I accomplish this if the root category can't be saved?
Since it's working from the Catalog Manager, what's the web service API doing differently?
Thanks,
Dave
HI Dave,
The exact same code works on the sample Adventure Works Catalog. Can you try taking a SQL tracing the call to stored procedure dbo.ctlg_SetChildRank from the Catalog Manager UI and from your application. Please let me know if there is a diifference in the traces.
Thanks
Sudha
Sudha,
Ok, I am starting to remember the specifics of this problem now because I did have it in the RC version. it's not the updating of the ranks that isn't working. I order the products using a grid on the page, and the system DOES rank the products correctly, because refreshing the Catalog Manager UI shows the categories in the correct order, however, part of the operation is that once the categories have been ordered, the grid rebinds itself. Upon doing this, the CatalogItemsDataTable that I am using as the Grid's DataSource has all 0 values for Rank for each category. Again, this is all working correctly for ANY category that is below the root, so the code shouldn't be any different, I'm simply doing a Catalog.GetRootCategory(CategoryConfiguration) and returning the ChildCategories.CatalogItems dataset for the grid.
One interesting piece of info. It works fine if I use GetRootCategory with NO CategoryConfiguration passed in...but I know the values for the config are correct as they work with all other categories but the root, and when applied to the root, the filtering based ont he Category Configuration works properly, but simply does not bring the Rank Column back.
Thanks,
Dave
Ok, I figured it out, and it turns out I've posted on this before...Not sure if this is expected behavior, but.. if you do a GetRootCategory(CategoryConfiguration), the CatalogItemsDataSet's Rank column from the ChildProducts and ChildCategories collections is always pulled as 0, whereas any other Category, retrieved by Catalog.GetCategory(categoryName, CategoryConfiguration) pulls the proper rank column from the database.
So here's what I found. There is a procedure in the ProductCatalog Database called ctlg_FillCategoryDescendants that when the Oid = -1 (root category?) and Virtual Catalog is <> 0 it joins to the appropriate table but hardcodes a 0 in the rank column returned by the subquery. If the Virtual Catalog bit is NOT zero, it doesn't even join to the table with the Rank column in it, instead it just hardcodes to 0 the rank column yet again.
Here's the original code segment:
IF @oid = -1
BEGIN
if (@fVirtualCatalog <> 0)
BEGIN
-- Insert the Categorys first-level descendents in the #Desc_Oids temp-table
SET @Query_tmp = N'INSERT '+@PersistTTName + N'(#TmpCatalogoid#, #TmpCatalogName#, #Tmp_i_classtype#,rank,#TmpStep#)
SELECT A.oid, A.BaseCatalogName, A.i_classtype, 0, 0
FROM ' + @CatalogView_tmp + N' A
LEFT OUTER JOIN ' + @CatalogHierarchyTableName_tmp + N' H
ON A.oid = H.child_oid AND A.BaseCatalogName = H.child_CatalogName
WHERE H.Child_oid IS NULL AND H.Child_CatalogName IS NULL AND ' + @ClassTypeQuery_tmp
END
ELSE
BEGIN
-- Insert the Categorys first-level descendents in the #Desc_Oids temp-table
SET @Query_tmp = N'INSERT '+@PersistTTName+N'(#TmpCatalogoid#, #TmpCatalogName#, #Tmp_i_classtype#,rank,#TmpStep#)
SELECT oid, CatalogName, i_classtype, 0, 0
FROM '+@CatalogView_tmp+N'
WHERE parentoid = @oid AND '+@ClassTypeQuery_tmp
END
END
And here is what I did to the query to make it work properly in my app (changes are bolded and highlighted):
IF @oid = -1
BEGIN
if (@fVirtualCatalog <> 0)
BEGIN
-- Insert the Categorys first-level descendents in the #Desc_Oids temp-table
SET @Query_tmp = N'INSERT '+@PersistTTName + N'(#TmpCatalogoid#, #TmpCatalogName#, #Tmp_i_classtype#,rank,#TmpStep#)
SELECT A.oid, A.BaseCatalogName, A.i_classtype, H.Rank, 0
FROM ' + @CatalogView_tmp + N' A
LEFT OUTER JOIN ' + @CatalogHierarchyTableName_tmp + N' H
ON A.oid = H.child_oid AND A.BaseCatalogName = H.child_CatalogName
WHERE H.Child_oid IS NULL AND H.Child_CatalogName IS NULL AND ' + @ClassTypeQuery_tmp
END
ELSE
BEGIN
-- Insert the Categorys first-level descendents in the #Desc_Oids temp-table
SET @Query_tmp = N'INSERT '+@PersistTTName+N'(#TmpCatalogoid#, #TmpCatalogName#, #Tmp_i_classtype#,rank,#TmpStep#)
SELECT A.oid, A.CatalogName, A.i_classtype, H.Rank, 0
FROM '+@CatalogView_tmp+N' A
LEFT OUTER JOIN ' + @CatalogHierarchyTableName_tmp + N' H
ON A.oid = H.child_oid AND A.BaseCatalogName = H.child_CatalogName
WHERE A.parentoid = @oid AND '+@ClassTypeQuery_tmp
END
END
Again, to reproduce the problem, when retrieving category lists under the root, set up the categoryConfiguration to do ANY filtering, and call the Catalog.GetRootCategory(CategoryConfiguration) function. Rank column will be full of zeroes.
Can someone tell me if this is intended? I posted this back in July but noone ever responded.
Thanks,
Dave
