Full Text Search for Find Methods

Hello,
I have to implement a method FindProduct, which takes a parameter search query and returns product based on the query.

I think i can use full text search here but don't know how. I tried to read a various way but could not figure out.

123 [DataObjectMethodAttribute(DataObjectMethodType.Select,true)]4public DataLayer.shopDBDataSet.ProductsDataTable GetProducts()5 {6return Adapter.GetProducts();7 }89 [DataObjectMethodAttribute(DataObjectMethodType.Insert,true)]10public bool AddProduct(string UPCCode,string Description,string Size)11 {12 shopDBDataSet.ProductsDataTable products =new shopDBDataSet.ProductsDataTable();13 shopDBDataSet.ProductsRow productRow = products.NewProductsRow();1415 productRow.UPCCode = UPCCode;16 productRow.Description = Description;17 productRow.Size = Size;1819 products.AddProductsRow(productRow);20int rowsAffected = Adapter.Update(products);21return rowsAffected == 1;22 }2324 [DataObjectMethodAttribute(DataObjectMethodType.Delete,true)]25public bool DeleteProduct(string UPCCode)26 {27int rowsAffected = Adapter.Delete(UPCCode);28return rowsAffected == 1;29 }3031 [DataObjectMethodAttribute(DataObjectMethodType.Select,false)]32public DataLayer.shopDBDataSet.ProductsDataTable FindProducts(string searchQuery)33 {3435throw new System.NotImplementedException();3637 }38

You can see the method at line number 32. I could not figure out the way to implement it although i know full text search is a good choice.

I have following table adapter

GetProducts(); GetProductsByManufacturerName(); GetProductsByManufacturerID(), and GetProductsByUPCCode();

Product table contains UPCCode, Description, Size columns

But i need to get the product which returns the description containing search query.

I know some of you have done something similar to this.

Could you please guide me...

Thanks in advance

...

The Full Text index require four predicates CONTAINS,CONTAINSTABLE,FREETEXT and FREETEXTTABLE and the Microsoft search service and the catalog must be populated before you can get search results. You can implement it in your application but there is limited support for SQL Server Express. The links below will take you in the right direction if you decide to implement it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms142559.aspx
http://msdn2.microsoft.com/en-us/library/ms142547.aspx

http://msdn2.microsoft.com/en-us/library/ms189760.aspx

|||

Thanks a lot...

i got the full text indexes for my tables.

But do you suggest me to create a productstableadapter that uses FREETEXT SQL query.

or just call it from the function.

Thanks

|||

There are samples provided by Microsoft but I think you should use a stored proc like the samples in my first post and call the stored proc in your ADO.NET code. Hope this helps.

|||

Thank you again...

how do i pass search query entered by user.

SELECT ProductName
FROM Products
WHERE FREETEXT (ProductName, 'spread' )

instead of spread i would like to pass user input.

thanks once again 

|||

Then the CONTAINS predicate is more suited for your needs because it can be used in seven or more context when searching, run a search for all four predicates I posted in my first reply in SQL Server BOL(books online). Here are all the uses of CONTAINS. Hope this helps.

A. Using CONTAINS with <simple_term>

B. Using CONTAINS and phrase in <simple_term>

C. Using CONTAINS with <prefix_term>

D. Using CONTAINS and OR with <prefix_term>

E. Using CONTAINS with <proximity_term>

F. Using CONTAINS with <generation_term>

G. Using CONTAINS with <weighted_term>

H. Using CONTAINS with variables

|||

Hey Caddre (my favorite poster)

I want to piggy back on this. I implemented the code used in the links above but something still isn't right. Here's my code:

<code>

Sub search(ByVal sender As Object, ByVal e As System.EventArgs)
GridView1.Visible = True
Dim DBCOnnection As SqlConnection
Dim Criterion As String = Request.QueryString("Criterion")
DBCOnnection = New SqlConnection("Data Source=server/db;Initial Catalog=SOS_KnowledgeBase;Integrated Security=True")
DBCOnnection.Open()

Dim strSQL As String
Dim objDataset As New DataSet()
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()

strSQL = "exec dbo.sp_search @.Application, @.Category, @.textsearch"

objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, DBCOnnection)
objAdapter.SelectCommand.Parameters.AddWithValue("@.Application", DDLApp.SelectedItem.Text)
objAdapter.SelectCommand.Parameters.AddWithValue("@.Category", DDLCat.SelectedItem.Text)
objAdapter.SelectCommand.Parameters.AddWithValue("@.textsearch", textsearch.Text)
objAdapter.Fill(objDataset)
Dim oView As New DataView(objDataset.Tables(0))
GridView1.DataSource = oView
GridView1.DataBind()

DBCOnnection.Close()
If GridView1.PageCount <= 0 Then
Label1.Text = "No Results Matched Your Query."
Else

If GridView1.PageCount > 0 Then
Label1.Text = ""
End If

End If
Button1.Visible = False
Button2.Visible = True


End Sub

</code>

<html>

<asp:content id="Content1" contentplaceholderid="headertext" runat="server">

<asp:DropDownList ID="DDLApp" runat="server" DataTextField="Application" DataValueField="Application" AutoPostBack="True" DataSourceID="Application" AppendDataBoundItems="True" OnSelectedIndexChanged="refresh">
<asp:ListItem Value="0">-- Choose an Application --</asp:ListItem>
</asp:DropDownList><br />

<asp:SqlDataSource ID="Application" runat="server" ConnectionString="<%$ ConnectionStrings:SOSKB %>"
SelectCommand="SELECT DISTINCT Application.Application, Article.ApplicationID FROM Application INNER JOIN Article ON Application.ApplicationID = Article.ApplicationID">
</asp:SqlDataSource>
<asp:DropDownList ID="DDLCat" runat="server" DataTextField="Category"
DataValueField="Category" DataSourceID="Category" AppendDataBoundItems="True">
<asp:ListItem Value="0" Selected="True">-- Select All --</asp:ListItem>
</asp:DropDownList><asp:SqlDataSource ID="Category" runat="server" ConnectionString="<%$ ConnectionStrings:SOSKB %>"
SelectCommand="SELECT DISTINCT Category.Category FROM Category INNER JOIN Article ON Category.CategoryID = Article.CategoryID INNER JOIN Application ON Article.ApplicationID = Application.ApplicationID WHERE (Application.Application = ISNULL(@.Application, Application.Application)) ORDER BY Category.Category">
<SelectParameters>
<asp:ControlParameter ControlID="DDLApp" Name="Application" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

<asp:Label ID="textsearch" runat="server" CssClass="box-title" Text="Keyword(s)"></asp:Label><br />
<asp:TextBox ID="searchbox" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" OnClick="search" Text="Search Now" Width="83px" />

</asp:content>

</html>

<stored proc>

CREATE PROCEDURE [sp_search]

@.Application nvarchar (25),
@.Category nvarchar(25),
@.textsearch nvarchar(100)

AS

Begin

If @.Category ='-- Select All --'
SELECT Article.ArticleID, Application.Application, Category.Category,
Article.Title, Article.DateUpdated AS [Last Updated]
FROM Article INNER JOIN Application ON
Article.ApplicationID = Application.ApplicationID
INNER JOIN Category ON Article.CategoryID = Category.CategoryID
WHERE Application.Application = ISNULL(@.Application, Application.Application) AND
Category.Category = ISNULL(null, Category.Category) AND
Article.Title IN (
SELECT Title
From Article
WHERE (CONTAINS(*, @.textsearch)))

If @.@.RowCount=0
--raiserror ("No Results.", 16, 1)
RETURN @.@.Error
End
GO

</stored proc>

Here's the error I keep getting:

System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=13
Message="Syntax error occurred near '('. Expected '' in search condition 'Keyword(s)'."
Number=7631
Procedure="sp_search"
Server="my server"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.ProviderBase.DataReaderContainer.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at ASP.search_aspx.search(Object sender, EventArgs e) in D:\Documents and Settings\uid\My Documents\Visual Studio 2005\WebSites\SOSKnowledgeBase\search.aspx:line 33
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Can you tell me what I am doing wrong here? Thanks.

Cordell

|||

Never mind...so stupid. I was referencing a label instead of the actual textbox!!!!!

Now that this is fixed, does anyone know a link or two that shows me how to strip commas, periods, etc from textbox entries before you pass it to a procedure?

Cordell

|||

I think you are calling Contains too late in your query in the WHERE clause of a JOIN which is just a filter and if you are in Express there is limited support for Express. You should check out the other three predicates especially the tables version. Hope this helps.

FreetextTable

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

Containstable

http://msdn2.microsoft.com/en-us/library/ms189760.aspx

And thanks for the compliment.