FREETEXT FILTER ON FREETEXTTABLE USING COLUMN LIST FOR REFINED SEARCH
Sep 12, 2007
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
View 1 Replies
ADVERTISEMENT
Aug 23, 2007
Hi,
I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name]
Can I use FREETEXTTABLE instead? How do I go about doing it?
View 2 Replies
View Related
Aug 11, 2007
Hi,
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Any thoughts appreciated.
Thanks
View 3 Replies
View Related
Sep 13, 2007
I have an issue trying to pass a search text parameter to FREETEXTTABLE via Dataset.
The following code works fine if you hardcode the search word/text as shown:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, 'cool') AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
Now, I want to do this:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, @Search) AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
The error I'm getting is @Search is not declared. How am I suppose to pass in a value?
I have searched almost everywhere and nobody seemed to ask this precise question. I'm sure this is a huge problem.
Can anyone help me please?
View 3 Replies
View Related
Apr 18, 2006
Can anybody provide an example of how to specify a column list in a CONTAINS statement? Documentation says it must be comma separated inside parenthesis - I have tried (every combination of) this but always get the error "Incorrect syntax near '('.", for example:
SELECT .... WHERE CONTAINS((Name,Description), '"options"')
This works fine when just querying one column (without the parenthesis).
View 4 Replies
View Related
Apr 28, 2008
Hi
I am using below code to fetch the data hit our webserver from different website or engine.
ex: If I pass the parameter Ref as google and the dates, will fetch the data (hits) from google.
I have created a table (PoList) to have names like google,amazon,aol upto 14 names.
My Question : How do I filter all (hits) the data which the name (Ref) is NOT in the PoList table.
Thanks in advance
CREATE PROCEDURE sp_Portals
@Ref as nvarchar(255),
@SDate as Decimal(18,0),
@EDate as Decimal(18,0)
AS
SELECT COUNT(b.Pcode) AS totclicks,
case when b.Pcode = 'NPF' then b.Pcode + '-- No Products Found'
else a.prodmodel
end as prodmodel,
SUM(c.orderTot) AS [Sales Total],
COUNT(c.cusID) AS NumSales,
b.Pcode as Code
FROM CusDetails c RIGHT OUTER JOIN
View1 b LEFT OUTER JOIN
Products a ON COALESCE (b.ProdID, 0) = a.ID ON c.sessID = b.SessID
WHERE (b.Referer LIKE '%' + @Ref+ '%') AND (b.theDate >= @SDate) AND (b.theDate <= @EDate)
GROUP BY b.Pcode, a.prodmodel
ORDER BY totclicks DESC
GO
View 2 Replies
View Related
Mar 24, 2015
SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,
[code]....
sb.CarrierId data is Sec.
Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values.
I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. It fails now and displays sec values in resultset.
View 1 Replies
View Related
May 17, 2007
Hi.
I think that it's possible only when CommandType is set to TableDirect.
But I need subj for this:
cc.CommandText = "SELECT FAM+' '+IMA+' '+OTC AS FIO FROM LICO AS LIC";
rs = cc.ExecuteResultSet(ResultSetOptions.Scrollable);
DataGridTableStyle ts = new DataGridTableStyle();
ts.MappingName = "";
DataGridColumnStyle cs = new DataGridTextBoxColumn();
cs.MappingName = "FIO";
cs.Width = 400;
ts.GridColumnStyles.Add(cs);
dataGrid1.TableStyles.Add(ts);
rsv = rs.ResultSetView;
dataGrid1.DataSource = rsv;
View 1 Replies
View Related
Mar 20, 2015
I am trying to import the data from SharePoint into my PowerPivot window as a Datafeed. I am able to successfully import the whole data from SharePoint list. But, now i would like to apply a filter (Where Clause) before importing the data.
View 2 Replies
View Related
Jan 23, 2008
Hello, I have read on the multiple places that filter for full text search of PDF files using FTS2005 is included in the Reader 8 etc. However, I have not found any document or instruction etc on adobe documents, microsoft documents or web that details on how to actually configure the filter. Please help. thanks
Kumud
View 4 Replies
View Related
Apr 15, 2011
Search option in the filter of SSRS, like in the filter let say for the category parameter we have 100 values , instead of scrolling down for the required option. can we have an option for search in the filter so that when we type certain value it will come up for example in the Google website, when we type the value in the search bar in the drop down we will get the option..same as that...........can we do in the ssrs.
If yes how can we achieve? Do we need to write any .net code?
View 5 Replies
View Related
May 14, 2015
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.Â
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
View 3 Replies
View Related
Mar 12, 2015
How do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.
Here is the flat table
SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO
Output will be something like this
12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG
Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.
View 2 Replies
View Related
Jan 9, 2008
I have a MSSQL Report Services Report Model set up to allow users to create their own ad hoc reports. The data source for my model is a Named Query that queries a MS SQL view that actually pulls data from a series of tables & other views. When you create a report from this model and attempt to filter the data some of the fileds will provide you with a pick list to select which values you would like to filter on and other fields do not provide you with a pick list but require you to enter the data directly that you wish to filter on. Most of the data fields that I am trying to filter on are varchar fields and like I said some will create a pick list for you to selet from and others will not. This all seemed to start after I changed my data source to a Named Query rather than having my data source as the MS SQL View directly. I did this becuase it seemed that anytime I had to make a change to the views that the data source pulled from it would mess up any existing reports that had been created and this does not happen if I use a named query. I have gone crazy trying to figure this one out so any ideas would be greatly appreciated!
View 1 Replies
View Related
Sep 9, 2015
I want to filter (=Searchargument) my dataset by something like this:
IF Customer = 'Peter' Then
  Searchargument = Username
ELSE
 Searchargument = Company
END
I want to filter on searchargument which value can be different and depends on a earlier selection.
View 3 Replies
View Related
Apr 18, 2007
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
View 7 Replies
View Related
May 12, 2015
Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Is it possible, if yes then how?
View 2 Replies
View Related
Dec 23, 2014
Trying to build a list of order numbers based on stock availability.
The data looks something like this:
OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
View 1 Replies
View Related
Jul 10, 2007
Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.etc.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?
View 1 Replies
View Related
Jul 11, 2007
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)
However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)
I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).
Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):
Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:
Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.
etc.
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>
I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?
View 1 Replies
View Related
Jul 14, 2014
I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.
WITH bug (BugID, BugComment) AS (
SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'
UNION ALL
SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'
UNION ALL
SELECT 3, 'Urgent DB_001000 DB_001000 Glick'
[Code] ....
View 6 Replies
View Related
Jan 1, 2014
I am new for SQL SP, I have three table,
Table 1 - Merchant
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
Table 2 - Region
RegionID RegionName
1 Region1
2 Region2
3 Region3
Table 3 - Offer
OfferID RegionID MerchantID
1 1 3
2 1 2
3 1 1
4 2 2
5 2 4
I have two input parameter for this SP, @MerchantName = NULL and @RegionName = NULL
Expected Result Table
If input parameter @MerchantName = NULL and @RegionName = NULL
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
4 Merchant4 1004
If we Search by RegionName="Region1" Ex: @MerchantName = NULL and @RegionName = 'Region1'
MerchantID MerchantName Zip
1 Merchant1 1001
2 Merchant2 1002
3 Merchant3 1003
If we Search by RegionName="Region2" Ex: @MerchantName = NULL and @RegionName = 'Region2'
MerchantID MerchantName Zip
2 Merchant2 1002
4 Merchant4 1004
If we search by MerchantName='Merchant1' Ex: @MerchantName = 'Merchant1' and @RegionName = ''
MerchantID MerchantName Zip
1 Merchant1 1001
How to get the expected result for the above scenario.
View 3 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
Jan 7, 2005
I have a date column and a value column. I want to display the value column based on the last date. MAX (Date)
I tried using MAX for the date column but that displays the last date.
Any Ideas?
View 5 Replies
View Related
Oct 3, 2006
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: }
Line 1445: try {
Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();
Line 1447: return returnValue;
Line 1448: }
View 3 Replies
View Related
Nov 5, 2007
I don't know whether this is the right place to ask this question. I didn't get the answer from sql forums may be someone here can help. I need to use freetext on two columnssomething likefreetext(column1+column2,@Search) this will give error "Incorrect syntax near '+'."Any One know the correct syntax. Thanks
View 1 Replies
View Related
Jan 24, 2005
I have a vb page, which is a simple front end so I can edit text from anywhere.
The person who is going to use it knows no html, and so I am trying to get freetextbox to work.
I am getting the following error:
Server Error in '/' Application.
--------------------------------------------------------------------------------
ExecuteNonQuery: CommandText property has not been initialized
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
Source Error:
Line 81:
Line 82: mySelectCmd.Connection.Open()
Line 83: mySelectCmd.ExecuteNonQuery()
Line 84: MyConnString.Close()
Line 85:
The pages code is below:
<%@ Page Language="VB" Debug="True" validateRequest="False"%>
<%@ Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
ReadMyData()
end if
end sub
Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable
dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))
mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()
dr = dt.NewRow()
dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")
dt.Rows.Add(dr)
End While
Finally
myReader.Close()
MyConnString.Close()
End Try
Pubs.DataSource = DT
Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
myExecuteQuery= "Update content_text Set title=@title, text=@text, section_no=@section_no where id=@id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
dim e_id as integer
dim e_title, e_text, e_section_no as string
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text
mySelectCmd.Connection.Open()
mySelectCmd.ExecuteNonQuery()
MyConnString.Close()
pubs.EditItemIndex = -1
ReadMyData()
End Sub
</script>
<html>
<head>
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left">Word</td>
<td width="15%" align="left">Title</td>
<td width="70%" align="left">Text</td>
<td width="10%" align="left">Section Number</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
<td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
<td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html>
View 5 Replies
View Related
Jul 9, 2007
Hello all. I'm new to this forum and of course I found it because I have a problem I can't find the answer to.
I have full-text indexing on a couple of tables in my database. Periodically, maybe once every couple of weeks, I get an error on a webpage that runs a stored procedure searching these tables. The error is as follows:
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed."
I'm running Classic ASP, and the stored procedures run thousands of times a day. As soon as I get the error email from the website containing the url that created the error, I immediately click on it, but by that time, the error has stopped and everything is running properly.
The timing of the error does not coorespond to any other processes. My transactionals run on the hour and the index is re-built at 12:45am, yet I just received the error again at 12:45pm (no, I checked the clocks and they are right).
I do not have replication running and I don't know where to look next.
Server:
SQL 2005 sp1
Windows 2003 Server
Any ideas would be greatly appreciated.
Russ
View 2 Replies
View Related
Nov 4, 2005
I tried the following select:
select * from patientprofile where contains (BillingNotes, '572576-02')
And got the errormessage:
Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'patientprofile' because it is not full-text indexed.
It shows BillingNotes as a text column
Is there a differnet way I can search this note field for records that contain that text value (572576-02)
View 3 Replies
View Related
Nov 5, 2007
Is there a way I could achieve something like this.
freetext(FirstName+LastName,@Name) FirstName and LastName are two columns in my database table.
What I am trying here is allowing the user to type their first name , last name or both. and write a search which can find there record for all the three cases.
Please Advice.
View 1 Replies
View Related
Mar 22, 2004
I'm trying to build a search function using FREETEXTTABLE to provide rankings of the results.
I've got it working on one of the tables using a query that looks something like this:
SELECT DISTINCT title, recordid, rank
FROM mn_records INNER JOIN FREETEXTTABLE(mn_records,*, 'sony') AS ft
ON mn_records.recordid = ft.[key]
ORDER BY rank DESC
The problem is that I want to search two tables mn_records and mn_items. A record has multiple items linked to it and some of the fields in the items table are different for the same record. I have the functionality working using FREETEXT:
SELECT DISTINCT title, recordid
FROM mn_records, mn_items
WHERE mn_records.recordid = mn_items.parentrecord
AND (FREETEXT (mn_records.*, 'sony')
OR FREETEXT (mn_items.*, 'sony'))
How can I properly join these two tables and the FREETEXTTABLE rank so that the search results will only contain distinct record_ids yet still search the information contained in mn_items?
View 1 Replies
View Related
Mar 21, 2006
This is my proc...
ALTER PROCEDURE [dbo].[IT_GetKBFreeText]
@Search Varchar(8000)
AS
BEGIN
SELECT KEY_TBL.RANK, KB_TBL.KNOWLEDGE_BASE_UID
FROM dbo.INFOSYS_KNOWLEDGE_BASE AS KB_TBL
INNER JOIN
FREETEXTTABLE(dbo.INFOSYS_KNOWLEDGE_BASE, (KNOWLEDGE_BASE_SHORT_NAME, KNOWLEDGE_BASE_TEXT),
@Search) AS KEY_TBL
ON KB_TBL.KNOWLEDGE_BASE_UID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
Iam returning rank = 0 for each record. FTS Is running and the catalog has all table records in in it ( I can tell by the record count)
Help!!!???
View 1 Replies
View Related
May 13, 2006
Hi..
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19
Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject
(
@SearchString nvarchar(500),
@CultureName nvarchar(50),
@HowManyResults int OUTPUT
)
AS
CREATE TABLE #SearchTable
(
FieldNO int,
ProjectNO int,
ProjectName nvarchar(200),
ProjectDescription nvarchar(1000),
ProjectImage nvarchar(1000),
CultureID int
)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
ProjectField_Locale AS FL,
FREETEXTTABLE(ProjectField_Locale,*,@SearchString) AS FT2
WHERE FL.FieldNO=P.FieldNO
AND FL.FieldCultureID=FT2.[KEY]
AND PL.ProjectNO=P.ProjectNO
AND PL.CultureID=dbo.GetCultureID(@CultureName)
AND FL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID)
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM Project_Locale AS PL ,Project AS P,
Feature_Locale AS PFEA,
ProjectFeature AS FP,
Feature AS F,
FREETEXTTABLE(Feature_Locale,*,@SearchString) AS FT3
WHERE FT3.[KEY]=PFEA.FeatureCultureID
AND FP.ProjectNO=P.ProjectNO
AND FP.FeatureNO=PFEA.FeatureNO
AND PL.ProjectNO=P.ProjectNO
AND F.FeatureNO=PFEA.FeatureNO
AND PFEA.CultureID=dbo.GetCultureID(@CultureName)
AND PL.CultureID=dbo.GetCultureID(@CultureName)
SELECT @HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable
SELECT DISTINCT * FROM #SearchTable
RETURN
GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle
DECLARE @HowManyResults int
EXEC SearchProject 'ملÙ?ات','ar-SA',@HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,'ملÙ?ات') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID
JOIN Project AS P ON P.ProjectNO=PL.ProjectNO
WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
View 5 Replies
View Related