Full Text Search with a Parameter

I have a Full Text query that works fine when structured as follows:

SELECT First_Name, Middle_Name, Last_Name, Hire_Date, City, Department, Phone_Ext, title, Fax, Secretary_Name, Attorney_Name, Secy_Ext, Home_Phone, Desk_Location, Law_School, Undergraduate_School, Languages, E_mail, CMS_ID, WEB_ID, Notary

FROM dbo.PhotoDir

WHERE CONTAINS (*, 'Jones')

ORDER BY Last_Name, First_Name

However, I would like to replace "Jones" with a parameter, @.LName for example. However, I can't figure out the syntax. I tried the following:

WHERE CONTAINS (*, @.LName)

but received the following error:

The @.LName SQL construct or statement is not supported.

Is there a way to put a parameter in this type of query?

Thanks in advance.

Hi There,

Have a look at this article, mnight give you a clue on what you doing there.

http://technet.microsoft.com/en-us/library/ms187787.aspx

Hope it helps!

|||

Hi lwhalen618,

See this example in sql2005 online book:

USE AdventureWorks;GODECLARE @.SearchWord nvarchar(30)SET @.SearchWord = N'Performance'SELECT Description FROM Production.ProductDescription WHERE CONTAINS(Description, @.SearchWord);GO

See, it's totally fine to use parameter within full-text-indexing. Your code seems okay to me. Please check if you have declared @.LName in your code.

Hope my suggestione helps

|||

I was able to get my code to work using a stored procedure. The query builder within VS2005 would not let me use the additional code before the Select statement.

Thanks for your help.

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.

Fujitsu call to sproc via ADO using a parameter

I am trying to call a stored proc using a parameter via ADO exceute,
but cannot get the syntac correct.
Any help is much appreciated.
thanks,If your stored procedures doesn't have any output parameter then you can use the execute method of connection object as given below

Conn.Execute "Usp_GetEmp 1, '01/01/2002'"

assuming the sp has two input parameters, integer datatype as first parameter and date as second parameter.

If your stored procedure have output parameters then you have to use the command object of the ADO Object Library.

I hope this helps you

:)|||If you need to have a resultset returned from the stored procedure you would have to use the command object as well. If you need to use the command object, please post the stored procedure parameters.|||Getting a returned result set via exec is OK.

The problem is with trying to figure out how to set up the parameters array in order to pass the parameters!

e.g. how would I run something like the [Sales by Year] from Northwind, which requires 2 parameters? (let's say we ignore the ouput)|||Try the following:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

cn.Open "Your Connection String"
With cmd
Set .ActiveConnection = cn
.CommandText = "[Sales by Year]"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Beginning_Date", _
adDate, adParamInput)
.Parameters("Beginning_Date").Value = "1/1/1980"
.Parameters.Append .CreateParameter("Ending_Date", _
adDate, adParamInput)
.Parameters("Ending_Date").Value = "1/1/2000"
End With
rs.Open cmd

' close/destroy the connection, command, recordset objects

rs.Close
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing|||Thanks.

Unfortunately I'm looking for Fujitsu COBOL syntax , but I do appreciate your help.|||Do you have a help file on how to use ado with fujitsu cobol ? If so, please post and I will check it out.|||Ahhh. If I had that I wouldn't have had to have posted!! Fujitsu's support refers one to MSDN where one finds solutions such as yours.

They don't have any documentation re ADOor any examples of how to use it, although their marketing material states specifically that they support it.

Which is why I posted here in the first place.

Thanks again.|||You mean to say there is no online help for the ado syntax. What about an object reference - not necessarily a help document. Do you have connection, command and recordset objects ? If all else fails, you could create a com object in vb and reference that in your cobol colde.|||To say that the documentation is sparse is an understatement. Also what's there is incomplete and/or misleading in some cases, so it's not of much use.

We could create a component in VB, well Delphi probably, as that's our preferred development platform, but we specifically bought Fujitsu due to its claimed support for ADO. (To be honest, the compiler probably does support ADO, but the documentation isn't saying how, especially in the realm of parameter passing ...