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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor Commented:
Phrases need to be enclosed in double quotes.  So the value of @Category should be:
"Absorbers, Nuclear Radiation" (Double quotes included)
0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
All Courses

From novice to tech pro — start learning today.