BUG !: Virtual Catalog - searchOptions Sorting issue when CatalogClassTypes does not include CatalogClassTypes.CategoryClass

hi there,

 

i have run strange problem or bug.. please please let me know if should log pss bug or if understanding / coding problem..

 

i trying run search on virtual catalog which searches product items and variants, it should sort results by displayname.. simple enough might say.. let me explain issue.

 

if search that has searchoptions.classtypes = catalogclasstypes.allclasstypes or searchoptions.classtypes = catalogclasstypes.categoryclass, search works search returns categories in returned dataset.

 

if search without either of above 2 catalogclasstypes returns error sortproperty not exist.

 

this search function works on virtual catalog:

private catalogitemsdataset ftsearch()

{

catalogitemsdataset retval = new catalogitemsdataset();

catalogsearch search = commercecomponents.helpers.cataloghelper.catalogcontext.getcatalogsearch();

search.language = system.globalization.cultureinfo.currentuiculture.name;

search.catalognamescollection = commercecontext.current.getcatalogsforuser();

search.searchoptions.classtypes = catalogclasstypes.productclass

| catalogclasstypes.categoryclass

| catalogclasstypes.productfamilyclass

| catalogclasstypes.productfamilyforvariantsclass

| catalogclasstypes.productvariantclass

| catalogclasstypes.productvariantsforfamilyclass;

search.searchoptions.propertiestoreturn = "primaryparentcategory,displayname,releasedate";

search.searchoptions.sortproperty = "[displayname]";

search.freetextsearchphrase = this.getsearchphrase();

retval = search.search(out inttotalrecords);

return retval;

}

 

this search function not work on a virtual catalog:

private catalogitemsdataset ftsearch()

{

catalogitemsdataset retval = new catalogitemsdataset();

catalogsearch search = commercecomponents.helpers.cataloghelper.catalogcontext.getcatalogsearch();

search.language = system.globalization.cultureinfo.currentuiculture.name;

search.catalognamescollection = commercecontext.current.getcatalogsforuser();

search.searchoptions.classtypes = catalogclasstypes.productclass

| catalogclasstypes.productfamilyclass

| catalogclasstypes.productfamilyforvariantsclass

| catalogclasstypes.productvariantclass

| catalogclasstypes.productvariantsforfamilyclass;

search.searchoptions.propertiestoreturn = "primaryparentcategory,displayname,releasedate";

search.searchoptions.sortproperty = "[displayname]";

search.freetextsearchphrase = this.getsearchphrase();

retval = search.search(out inttotalrecords);

return retval;

}

 

the error returned search following:

invalid column name 'displayname'.

description: an unhandled exception occurred during execution of current web request. please review stack trace more information error , originated in code.

exception details: system.data.sqlclient.sqlexception: invalid column name 'displayname'.

source error:

line 63: search.freetextsearchphrase = this.getsearchphrase(); line 64: line 65: retval = search.search(out inttotalrecords); line 66: line 67: return retval;

 

any / pointers / corrections appreciated thanks...

 

we on commerce server 2007 sp2.

 

kind regards

 

ryan

 

here more info found..

 

i ran sql trace see may failing.. found following:

for case 1 works, following sql call executed , returns list of items according free text search.

 

declare @p16 int

set @p16=3

exec dbo.ctlg_getresults_for_singlecatalog

@catalogname=n'au dd cm base catalog'

,@language=n'en-au'

,@propertiestoreturn=n' [primaryparentcategory]

,[displayname]

,[authorname]

,[releasedate]'

,@sqlclause=null

,@ftsphrase=n'poetry'

,@advancedftsphrase=null

,@orderby=n'[displayname]'

,@startingrec=1

,@numrecords=2147483646

,@sortascending=1

,@classtype=63

,@ejointype=-1

,@targettablename=n''

,@sourcejoinkey=n''

,@targetjoinkey=n''

,@recordcount=@p16 output

,@enableinventory=1

,@inventoryservername=null

,@inventorydatabasename=null

,@filteroutofstockproducts=0

,@filterbackorderableproducts=0

,@filterpreorderableproducts=0

,@usethresholdasfloor=1

,@treatmissingasoutofstock=1

,@stockhandling=0

,@inventorypropertiestoreturn=n'*'

,@categoryclause=null

,@recursive=0

select @p16

 

now case 2 not work, it executed following procedure..

 

declare @p16 int

set @p16=3

exec dbo.ctlg_getresults_for_singlecatalog

@catalogname=n'au dd cm base catalog'

,@language=n'en-au'

,@propertiestoreturn=n' [primaryparentcategory]

,[displayname]

,[authorname]

,[releasedate]'

,@sqlclause=null

,@ftsphrase=n'poetry'

,@advancedftsphrase=null

,@orderby=n'[displayname]'

,@startingrec=1

,@numrecords=2147483646

,@sortascending=1

,@classtype=62

,@ejointype=-1

,@targettablename=n''

,@sourcejoinkey=n''

,@targetjoinkey=n''

,@recordcount=@p16 output

,@enableinventory=1

,@inventoryservername=null

,@inventorydatabasename=null

,@filteroutofstockproducts=0

,@filterbackorderableproducts=0

,@filterpreorderableproducts=0

,@usethresholdasfloor=1

,@treatmissingasoutofstock=1

,@stockhandling=0

,@inventorypropertiestoreturn=n'*'

,@categoryclause=null

,@recursive=0

select @p16

 

this 1 returns following sql message

 

msg 207, level 16, state 1, line 1

invalid column name 'displayname'.

(1 row(s) affected)

 

so technically difference between execution of stored procedures @classtype variable..

 

i took @ stored procedure not understand inner workings enough figure out going wrong..

Commerce Server  >  Commerce Server 2007