SQL Server Full-Text Searching - Syntax error from spaces

Hello, I hope you can help!

Database: SQL Server 2005
Code: ASP.net 2.0

I have implemented full text search that works fine with single word searches but I receive a syntax error when the search string contains a space. For example:

An error example:

Search on Absorbers, Nuclear Radiation

Error:
Syntax error near ',' in the full-text search condition 'Absorbers, Nuclear Radiation'.
My Code:

<asp:SqlDataSource ID="SqlDataSource6" runat="server" ConnectionString="<%$ ConnectionStrings:XYZ %>"
            SelectCommand="SELECT [Categories], [SupplierName] FROM [Suppliers] WHERE CONTAINS([Categories], @Category)" ProviderName="System.Data.SqlClient">
             <SelectParameters>
                <asp:ControlParameter ControlID="ListBox3" Name="Category" PropertyName="SelectedValue"
                    Type="String" />
            </SelectParameters>        
           </asp:SqlDataSource>

Typical data format in the Categories database field. Note: Each entry in the field is separated by a semicolon.  

Abrasives;Absorbers, Nuclear Radiation;Accelerators & Accessories;Adhesives;

It looks like the white space in the search string is giving the error. How can I change the select command to stop the error?

Thanks!
CyberzonesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
Try this


<asp:SqlDataSource ID="SqlDataSource6" runat="server" ConnectionString="<%$ ConnectionStrings:XYZ %>"
            SelectCommand="SELECT [Categories], [SupplierName] FROM [Suppliers] WHERE CONTAINS([Categories], '@Category')" ProviderName="System.Data.SqlClient">
             <SelectParameters>
                <asp:ControlParameter ControlID="ListBox3" Name="Category" PropertyName="SelectedValue"
                    Type="String" />
            </SelectParameters>        
           </asp:SqlDataSource>
0
CyberzonesAuthor Commented:
thanks for the help asvforce. I tried your fix and the error stops but now it does not display the reults of the search in the listbox.
0
Anthony PerkinsCommented:
Phrases need to be enclosed in double quotes.  So the value of @Category should be:
"Absorbers, Nuclear Radiation" (Double quotes included)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

CyberzonesAuthor Commented:
thanks for the post acperkins.

Ok, I understand what you are saying but I do not know how to pass along the double quotes in the string or in the Selcet Command. Can you show me?

Thanks!
0
CyberzonesAuthor Commented:
bump
0
Anthony PerkinsCommented:
Where do you assign the value to the parameter @Category?
0
CyberzonesAuthor Commented:
Hi Sage,

The value of @Category is from a user selection in Listbox3 (code attached). Listbox3 pull its values from the database and based on the user selcetion, it will display the serach results in Listbox4.

I hop this helps!
<asp:Panel ID="Panel6" runat="server" Height="480px" Style="z-index: 106; left: 34px;
        position: absolute; top: 813px" Width="95%" BackColor="LightSteelBlue" BorderColor="Black" BorderWidth="1px">
           <br />
           <asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Medium" Style="z-index: 100;
               left: 8px; position: relative; top: 0px" Text="Nuclear Services and Product Search:"></asp:Label>:<br />
           <br />
           <asp:ListBox ID="ListBox3" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource5"
            DataTextField="Category" DataValueField="Category" Style="z-index: 101;
            left: 20px; position: relative; top: 0px" Width="90%" Height="200px"></asp:ListBox>
           &nbsp;<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:SuppliersConnectionString %>"
            SelectCommand="SELECT [Category] FROM [Categories] ORDER BY [Category]">
           </asp:SqlDataSource>
           <asp:SqlDataSource ID="SqlDataSource6" runat="server" ConnectionString="<%$ ConnectionStrings:SuppliersConnectionString %>"
            SelectCommand="SELECT [Categories], [SupplierName] FROM [Suppliers] WHERE CONTAINS([Categories], '@Category')" ProviderName="System.Data.SqlClient">
             <SelectParameters>
                <asp:ControlParameter ControlID="ListBox3" Name="Category" PropertyName="SelectedValue"
                    Type="String" />
            </SelectParameters>        
           </asp:SqlDataSource>
           &nbsp;&nbsp;<br />
           &nbsp;
           <asp:ListBox ID="ListBox4" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource6"
            DataTextField="SupplierName" DataValueField="SupplierName" Style="z-index: 103;
            left: 26px; position: absolute; top: 302px" Width="90%" Height="200px"></asp:ListBox>
           <br />
           &nbsp; &nbsp;&nbsp;
       </asp:Panel>

Open in new window

0
Anthony PerkinsCommented:
Unfortunately you are going to have to write code for this and add the double quotes to the value.

You can confirm it is correct by testing it as follows:
SelectCommand="SELECT [Categories], [SupplierName] FROM [Suppliers] WHERE CONTAINS([Categories], '""Absorbers, Nuclear Radiation""')" ProviderName="System.Data.SqlClient"
(I am doubling the double quotes in order to make it syntactically correct, I think)

Instead of:
SelectCommand="SELECT [Categories], [SupplierName] FROM [Suppliers] WHERE CONTAINS([Categories], '@Category')" ProviderName="System.Data.SqlClient
0
CyberzonesAuthor Commented:
Well it should not be too dificult to insert quotation marks in a string but unfortunately, I am coming up empty :(

Any examples or other options out there????
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.