Problem with Full-Text Search and whitespace

I am having an problem with using full-text search and phrases in ASP.NET 2.0 when populating a treeview.

 

 

I have a stored procedure that searches a full text index and brings back the results in a datareader.  It fails when I try to search for more than one word with a space in between.

I get the error SQLException was unhandled by usercode "Syntax error near 'up'' in the full-text search condition ''item up''." 

The stored proc is as follows:

CREATE PROCEDURE [dbo].[SearchResults] @criteria as varchar(50)

 

AS

BEGIN

SELECT *

FROM PMGNT_PROJECTS PRJCT INNER JOIN

CONTAINSTABLE(PMGNT_PROJECTS,*,@criteria) K

ON PRJCT.PRJCT_REFNO = K.[KEY]

END

 

The stored procedure works fine though query analyser it's only when I try to use it in ASP.NET and use more than one word that it fails e.g. "item up". Search for single words also works fine in ASP.NET

 Code is as follows:

  

1    if (SearchField.Text != "")
2    
3    {
4    
5    string searchString = "";
6    
7    searchString = "'" + SearchField.Text + "'";
8    
9    
10   
11   SqlConnection SqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
12   
13   SqlCon.Open();
14   
15   SqlCommand SqlCmd = new SqlCommand("SearchResults", SqlCon);
16   
17   
18   SqlCmd.CommandType = CommandType.StoredProcedure;
19   
20   SqlCmd.Parameters.Add("@criteria", SqlDbType.VarChar);
21   
22   SqlCmd.Parameters["@criteria"].Value = searchString;
23   
24   
25   
26   SqlDataReader Sdr = SqlCmd.ExecuteReader();
27   
28   SqlCmd.Dispose();
29   
30   string[,] ParentNode = new string[200, 2];
31   
32   int count = 0;
33   
34   while (Sdr.Read())
35   
36   {
37   
38   ParentNode[count, 0] = Sdr.GetValue(Sdr.GetOrdinal("PRJCT_REFNO")).ToString();
39   
40   ParentNode[count++, 1] = Sdr.GetValue(Sdr.GetOrdinal("PRJCT_CODE")).ToString();
41   
42   }
43   
44   Sdr.Close();
45   
46   for (int loop = 0; loop < count; loop++)
47   
48   {
49   
50   TreeNode root = new TreeNode();
51   
52   root.Text = ParentNode[loop, 1];
53   
54   root.Value = ParentNode[loop, 0];
55   
56   root.SelectAction = TreeNodeSelectAction.Select;
57   
58   SqlCommand Module_SqlCmd = new SqlCommand("SELECT * FROM PMGNT_MILESTONES MILST where MILST_PRJCT_REFNO =" + ParentNode[loop, 0], SqlCon);
59   
60   SqlDataReader Module_Sdr = Module_SqlCmd.ExecuteReader();
61   
62   string[,] ChildNode = new string[200, 2];
63   
64   int countChild = 0;
65   
66   
67   
68   while (Module_Sdr.Read())
69   
70   {
71   
72   ChildNode[countChild, 0] = Module_Sdr.GetValue(Module_Sdr.GetOrdinal("MILST_REFNO")).ToString();
73   
74   ChildNode[countChild++, 1] = Module_Sdr.GetValue(Module_Sdr.GetOrdinal("MILST_TITLE")).ToString();
75   
76   }
77   
78   Module_Sdr.Close();
79   
80   for (int cloop = 0; cloop < countChild; cloop++)
81   
82   {
83   
84   TreeNode child = new TreeNode();
85   
86   child.Text = ChildNode[cloop, 1];
87   
88   
89   
90   root.ChildNodes.Add(child);
91   
92   }
93   
94   
95   
96   // Add root node to TreeView
97   
98   SearchResultTree.Nodes.Add(root);
99   
100  }
101  
102  SearchResultTree.CollapseAll();
103  
104  SqlCon.Close();
105  
106  
107  }
108  
109  

 

 Can anyone Help ?

Read More

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s