Thread: Question about searching for SKU

I am a little confused with the search features of the site. If I have full text search enabled the search pages will not return items if the sku is entered unless I mention it in the description. If I turn off site search then it will find it by the sku but they have to be entered in a differant format than how we have it in our catalog for example our part number is 28-CDR the site SKU is 28CDR, if I search for 28-CDR nothing comes up if I search for 28CDR it does. If I turn on Full Text Search it doesn't matter which I type it will not come up unless I add it to a description.

Thanks,

Any ideas?

Based on the description of your setup such is the behavior that we would expect. There are a couple of way that you could address this, the easiest way would be to modify the site SKU to match your product SKU. Though the Full Text Search does improve search capabilities, the search matches are measure in degrees of similarity and in a 5-6 character search term, a single character difference is fairly significant.

Dusty
ASPDotNetStorefront Staff

Not too sure what you mean by "Site SKU" I only see two places to enter a SKU 1 the product definition page (SKU Prefix) and then on the variant page (SKU Suffix). The searches seem to be finding the SKU Prefix on the product page but not searching the SKU-Suffix on the variant page.

Thanks for your help.

Yes, you're right. Only the SKUSuffix but that doesn't mean it's impossible to integrate result based on the SKU Prefix also. This would only take a little tweaking on the aspdnsf_GetProducts, page.search.xml.config or page.searchadv.xml.config; however, on this example, we'll be using the former. First, you'll need to modify the stored procedure: aspdnsf_GetProducts
Change the occurrences (2 occurrences to be exact) of this line:

Code:

or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0

To:

Code:

or patindex(@searchstr,isnull(p.sku,'')) > 0
or patindex(@searchstr,isnull(pv.skusuffix,'')) > 0

Execute the stored procedure, then next, we'll modify the XML package: page.search.xml.config

Under the template /
Add the highlighted parts:

Code:

                                                    <td align="center">
                                                        <b>
                                                            <xsl:value-of select="aspdnsf:StringResource('search.aspx.7', $LocaleSetting)" disable-output-escaping="yes" />
                                                        </b>
                                                    </td>
                                                  <td align="center">
                                                    <b>
                                                      <xsl:value-of select="'SKU Suffix'" disable-output-escaping="yes" />
                                                    </b>
                                                  </td>

Note: Adding a string resource, instead of a hardcoded: SKU Suffix would be good for flexibility.

Code:

                    <td valign="middle" align="left" >
                        <a href="{aspdnsf:ProductLink(ProductID, SEName, 0, '')}">
                            <xsl:value-of select="$pName"  disable-output-escaping="yes" />
                            <xsl:if test="vName!=''">
                                -<xsl:value-of select="$vName" />
                            </xsl:if>
                        </a>
                    </td>
                  <td align="center">
                    <xsl:value-of select="SKU" />
                  </td>

                  <td align="center">
                        <xsl:value-of select="SKUSuffix" />
                        <xsl:if test="SKUSuffix!=''">
                            <xsl:value-of select="SKUSuffix" />
                        </xsl:if>
                    </td>

Then comment out the highlighted parts:

Code:

                  <td align="center">
                        <xsl:value-of select="SKUSuffix" />
                        <xsl:if test="SKUSuffix!=''">
                            <xsl:value-of select="SKUSuffix" />
                        </xsl:if>
                    </td>

This looks exactly like what I am looking for my problem now is when I go to implement it the stored procedure you mention is locked in SQL Server Manager and shows that it is encrypted. How do I get access to the Stored Procedure so that I can modify it?

Thanks!

You could use an SQL Decryption tool or use this steps: create a new query, then open the original Create AspDotNetStorefront Database.sql from your db folder. Copy the section that creates the aspdnsf_GetProducts and then comment out this line:

Code:

WITH ENCRYPTION

Execute the stored procedure and that's it. Unlocked and free...

Great answers. I have edited the stored procedure and I have modified the template pages. However I noticed that I still cannot search an item by the combined sku and skuSuffix. I started looking in the stored procedure and noticed that the line I commented out.

Code:

or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0

This line seems to combine the search string into one, so when I uncomment it I start getting the results I want. My question is why comment this line out will it interfere with any of the other modifications?

Thanks a lot! You guys have definitely got me on the right track with this.

Well, I thought you wanted to search using either the SKUPrefix or SKUSuffix. Anyway, yes, uncomment that line if you want to search by the SKUPrefix+SkuSuffix...