FREETEXTTABLE returns no result for a complex freetext only on JDB

This problem sounds like a problem of fulltext module, but it's not.
I'm using SQL Server 2005 Jun CTP on Windows 2k3 server
through MS SQL Server JDBC Driver SP3
from J2EE 1.4.2_08 on Windows 2k.
I've indexed and am searching tons of Japanese news articles
to test fulltext features of 2005.
SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
(M_HOGE,*,N'関西電力は阪神大震災で倒 した従X員向け福利厚生施Xの跡地 特別養X老人3施Xを建Xした。')
ON(ID=[KEY])
(This Japanese sentense is a little complex, saying
'The Kansai-Electric has constructed three elder care facilities at a
vacant lot where their employee welfare facilities which had given way
because of the Great Hanshin Earthquake had existed.')
This query returns no result if it was passed through JDBC,
but if I pass it to SQLServer Management Studio directly,
it returns tons of result with very good ranking.
(btw, I feel the ranking of FREETEXTTABLE was so much improved in 2005.).
But, as a strange thing, if I simplify the freetext like
SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
(M_HOGE,*,N'関西電力は福利厚生施Xの 地に施Xを建Xした。')
ON(ID=[KEY])
(It says 'The Kansai-Electric has constructed facilities at a
vacant lot where their employee welfare facilities had existed.')
, it returns good result even through JDBC!
Our company provides a product on Java using fulltext feature of SQL Server
2000,
and is going to adapt it to 2005, expecting much improvements.
We're looking forward to update about JDBC as well as fulltext features!
Hideaki:
The SQL Server 2000 JDBC driver isn't supported against SQL Server 2005.
Please try the SQL Server 2005 driver --
http://www.microsoft.com/sql/downloads/2005/jdbc.mspx.
-shelby
Shelby Goerlitz
Microsoft SQL Server
"hideaki" <hideaki@.discussions.microsoft.com> wrote in message
news:21C664BC-0FD8-488B-BC58-0E119E58CBE0@.microsoft.com...
> This problem sounds like a problem of fulltext module, but it's not.
> I'm using SQL Server 2005 Jun CTP on Windows 2k3 server
> through MS SQL Server JDBC Driver SP3
> from J2EE 1.4.2_08 on Windows 2k.
> I've indexed and am searching tons of Japanese news articles
> to test fulltext features of 2005.
>
> SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
> (M_HOGE,*,N'??????3? ??')
> ON(ID=[KEY])
> (This Japanese sentense is a little complex, saying
> 'The Kansai-Electric has constructed three elder care facilities at a
> vacant lot where their employee welfare facilities which had given way
> because of the Great Hanshin Earthquake had existed.')
> This query returns no result if it was passed through JDBC,
> but if I pass it to SQLServer Management Studio directly,
> it returns tons of result with very good ranking.
> (btw, I feel the ranking of FREETEXTTABLE was so much improved in 2005.).
>
> But, as a strange thing, if I simplify the freetext like
> SELECT ID,RANK,FOO FROM M_HOGE AS M INNER JOIN FREETEXTTABLE
> (M_HOGE,*,N'?????')
> ON(ID=[KEY])
> (It says 'The Kansai-Electric has constructed facilities at a
> vacant lot where their employee welfare facilities had existed.')
> , it returns good result even through JDBC!
> Our company provides a product on Java using fulltext feature of SQL
> Server
> 2000,
> and is going to adapt it to 2005, expecting much improvements.
> We're looking forward to update about JDBC as well as fulltext features!
>

Freetexttable

I am very new to the freetext searching and have not been
able to decipher some of the documentation steps in making
FreeTextTable work.
I have a table with a primary key of "KeyID", and my
search index is created on varchar fields of Problem and
Cause. I also have a field called WorkOrderID which is not
part of the index.
Basically I want to return the rank, the WorkOrderID, and
Problem/Cause if it matches. Pretty simple I should think.
I haven't found any line by line explanation of how the
query works.
It would be helpful if you could post the entire schema of this table. Here
is my stab in the dark as to what it would look like.
select KeyID, Problem, Cause, Rank from TableName join
FreeTextTable(TableName,*,'SearchPhrase') as a
on a.[key]=KeyID
order by Rank Desc
This will search for hits in any of the full text indexed columns, and will
search across columns. So if you are searching for James Bond, and one row
has the word James in the problem column and Bond in the Cause column this
will be a "hit"
If this won't work for you, you may have to do the more expensive:
select distinct KeyID, Problem, Cause, Rank=a.Rank +b.rank from authors,
FreeTextTable(TableName,Problem,'SearchPhrase') as a,
FreeTextTable(TableName,Cause,'SearchPhrase') as b where
a.[key]=KEYID or b.[key]=KEYID
order by Rank Desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dave" <anonymous@.discussions.microsoft.com> wrote in message
news:08f601c49cb2$87024080$a501280a@.phx.gbl...
> I am very new to the freetext searching and have not been
> able to decipher some of the documentation steps in making
> FreeTextTable work.
> I have a table with a primary key of "KeyID", and my
> search index is created on varchar fields of Problem and
> Cause. I also have a field called WorkOrderID which is not
> part of the index.
> Basically I want to return the rank, the WorkOrderID, and
> Problem/Cause if it matches. Pretty simple I should think.
> I haven't found any line by line explanation of how the
> query works.
>
|||I will try that also... table is:
KeyID - PK, int, identity
Problem - Varchar(2048)
Cause - varchar(2048)
WorkOrderID - int
All fields allow nulls except the PK of course.

FREETEXT vs FREETEXTTABLE

1) The following query works fine and gives me the results I want. Is there
a better more efficient way of writing this?
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler" '
SELECT Stock.OrderNo, Stock.Description, Stock.Category,
Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
Titles.Artist, Hardware.m_Specs, Stock.ManCode
FROM Stock LEFT OUTER JOIN
Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
Hardware ON Stock.OrderNo = Hardware.OrderNo
WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
FREETEXT(Stock.Description,@.SearchCriteria) OR
FREETEXT(Stock.Category,@.SearchCriteria) OR
FREETEXT(Stock.s_Type,@.SearchCriteria) OR
FREETEXT(Stock.Manuf,@.SearchCriteria) OR
FREETEXT(Stock.Label,@.SearchCriteria) OR
FREETEXT(Stock.ManCode,@.SearchCriteria) OR
FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
FREETEXT(Titles.Title,@.SearchCriteria) OR
FREETEXT(Titles.Artist,@.SearchCriteria)
2) As far as I can tell, RANK is not available with FREETEXT, but is
available with FREETEXTTABLE. How can I convert the above to make use of
FREETEXTTABLE?
thanks!
This should do it: NOTE: This assumes you want to search ALL fields that
are set up in the FULL-TEXT Search for that table.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = 'midler'
SELECT
Stock.OrderNo,
Stock.Description,
Stock.Category,
Stock.s_Type,
Stock.Manuf,
Stock.Label,
Titles.Title,
Titles.Artist,
Hardware.m_Specs,
Stock.ManCode
FROM
Stock
LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
ORDER BY
FS_TABLE.Rank DESC
"shank" <shank@.tampabay.rr.com> wrote in message
news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> 1) The following query works fine and gives me the results I want. Is
there
> a better more efficient way of writing this?
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler" '
> SELECT Stock.OrderNo, Stock.Description, Stock.Category,
> Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
> Titles.Artist, Hardware.m_Specs, Stock.ManCode
> FROM Stock LEFT OUTER JOIN
> Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
> Hardware ON Stock.OrderNo = Hardware.OrderNo
> WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
> FREETEXT(Stock.Description,@.SearchCriteria) OR
> FREETEXT(Stock.Category,@.SearchCriteria) OR
> FREETEXT(Stock.s_Type,@.SearchCriteria) OR
> FREETEXT(Stock.Manuf,@.SearchCriteria) OR
> FREETEXT(Stock.Label,@.SearchCriteria) OR
> FREETEXT(Stock.ManCode,@.SearchCriteria) OR
> FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
> FREETEXT(Titles.Title,@.SearchCriteria) OR
> FREETEXT(Titles.Artist,@.SearchCriteria)
> 2) As far as I can tell, RANK is not available with FREETEXT, but is
> available with FREETEXTTABLE. How can I convert the above to make use of
> FREETEXTTABLE?
> thanks!
>
|||The query works, but I'm getting all rows returned. The highest ranked are
at the top like expected, but it's also returning all rows in the Stock
table that have no match whatsoever. In my FREETEXT query, all the results
had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
expecting a temp table to be created with the results. However, the code
below is actually joining the created table. I don't get it.
How do I get only matching results in the FREETEXTTABLE?
thanks!
"news.microsoft.com" <spammehere@.arcaderestoration.com> wrote in message
news:e4dx4uuXEHA.3420@.TK2MSFTNGP12.phx.gbl...
> This should do it: NOTE: This assumes you want to search ALL fields that
> are set up in the FULL-TEXT Search for that table.
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = 'midler'
> SELECT
> Stock.OrderNo,
> Stock.Description,
> Stock.Category,
> Stock.s_Type,
> Stock.Manuf,
> Stock.Label,
> Titles.Title,
> Titles.Artist,
> Hardware.m_Specs,
> Stock.ManCode
> FROM
> Stock
> LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
> LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
> LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
> ORDER BY
> FS_TABLE.Rank DESC
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> there
>

Freetext Search with Parameter

Hi All,
I am trying to perform a simple search but not sure what the best way is
to do it.
In my stored procedure I have 3 parameters.
1. Newspapers (if param is 0 search all)
2. Companies (if param is 0 search all)
3. Keywords
Is the only way to do these queries by using dynamic sql?
e.g.
@.SQL = @.SQL + 'Select * from Article a where freetext(a.*, @.Keyword)'
if @.Companies > 0
begin
@.SQL = @.SQL + ' and a.companies = ' + @.Companies
end
etc.
Can anyone help?
Thanks
Angela
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Angela,
If I correctly understand what you're trying to accomplish, then using a
IF... THEN... ELSE construct might work for you, for example:
-- FTS and IF/THEN/ELSE construct
Declare @.searchstring varchar(255)
if(@.searchstring = '1')
SELECT * FROM Article
else
SELECT * FROM Article
WHERE FREETEXT(name, @.searchstring)
-- and so, on...
If the above doesn't work for you, then try using a CASE statement. If
possible, could you post your stored proc code?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"angela" <angela@.anon.com> wrote in message
news:eYM#MGgDFHA.2572@.tk2msftngp13.phx.gbl...
> Hi All,
> I am trying to perform a simple search but not sure what the best way is
> to do it.
> In my stored procedure I have 3 parameters.
> 1. Newspapers (if param is 0 search all)
> 2. Companies (if param is 0 search all)
> 3. Keywords
> Is the only way to do these queries by using dynamic sql?
> e.g.
> @.SQL = @.SQL + 'Select * from Article a where freetext(a.*, @.Keyword)'
> if @.Companies > 0
> begin
> @.SQL = @.SQL + ' and a.companies = ' + @.Companies
> end
> etc.
> Can anyone help?
> Thanks
> Angela
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for the reply John,
Here's my SP so far, which works but is there another way (i.e. not
using dynamic sql). On my asp page, if the user select 'Search ALL
companies' then a 0 is passed on to the SP. And like wise with
'Newspapers'
----
DECLARE @.CompanyID integer, @.NewspaperID integer, @.Keyword varchar(600)
DECLARE @.SQL varchar(8000)
SET @.SQL = ''
SET @.SQL = @.SQL + 'Select * from Article a where freetext(a.*,
@.Keyword)'
IF @.CompanyID > 0
BEGIN
SET @.SQL = @.SQL + ' and a.companies = ' + CAST(@.CompanyID as
varchar)
END
IF @.NewspaperID > 0
BEGIN
SET @.SQL = @.SQL + ' and a.newspapers = ' + CAST(@.NewspaperID as
varchar)
END
EXEC @.SQL
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Angela,
I've not forgotten this issue, as I'm currently working on other issues at
this time. Could you also post the sp_help Article output and a small sample
of the data?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"angela" <angela@.anon.com> wrote in message
news:eY0IypoDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Thanks for the reply John,
>
> Here's my SP so far, which works but is there another way (i.e. not
> using dynamic sql). On my asp page, if the user select 'Search ALL
> companies' then a 0 is passed on to the SP. And like wise with
> 'Newspapers'
>
> ----
> DECLARE @.CompanyID integer, @.NewspaperID integer, @.Keyword varchar(600)
> DECLARE @.SQL varchar(8000)
>
> SET @.SQL = ''
> SET @.SQL = @.SQL + 'Select * from Article a where freetext(a.*,
> @.Keyword)'
> IF @.CompanyID > 0
> BEGIN
> SET @.SQL = @.SQL + ' and a.companies = ' + CAST(@.CompanyID as
> varchar)
> END
> IF @.NewspaperID > 0
> BEGIN
> SET @.SQL = @.SQL + ' and a.newspapers = ' + CAST(@.NewspaperID as
> varchar)
> END
> EXEC @.SQL
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Freetext search not working after SQL 2005 upgrade

*** See next post as I have found it is nothing to do with freetext search I just assumed it was this ***

Hi

I have upgraded our test system to SQL 2005 from 2000 and restored a production database.

I have rebuilt the freetext index and it would seem that if you query this from an .ASP page using OLEDB it does not return any results even though there are some. If you then run the same query in Management studio you do get the expected results. The code worked perfectly under SQL 2000. Also all other queries that run from the .ASP page work correctly that do not involve a freetext seach.

I have made sure the client is using the lastest MDAC pack 2.8 sp1

The query is

SELECT title
FROM catalog
WHERE CONTAINS(title, 'green*')

Connection string in .ASP page

Provider=SQLOLEDB;Data Source=192.168.0.5;Initial Catalog=DVD;User Id=XXXXXX;Password=XXXXXX;

Thanks in Advance

MatRight after more digging I have found out it is NOT the freetext seach at all. I simplified the query above and removed a left join when I posted the question as I thought it could not be this.

I have now just created two simple tables in my database
linkme
linkme2

both of which have one column called [catalog-no]

I have put one record in linkme

select * from linkme -- pulls back one record correctly via .asp and management studio
select * from linkme left join linkme2 on linkme.[catalog-no] = linkme2.[catalog-no] -- pulls back one record in management studio and 0 records in .asp connection !!!

HELP|||Just to make sure I was not going mad I have created the same two table in SQL 2000 and pointed the .asp script to this and sure enough i get 1 record ?

Freetext Search - SQL CONTAINS (column,"R-483*") FAILS

Hi
I would like to search for "R-483" phase using SQL CONTAINS
function :
DECLARE @.i_FreeText Varchar(255)
SET @.i_FreeText= '"r-483*"'
SELECT TOP 10 * from iBlockFreeText
where
(@.i_FreeText is null or contains(iBlockFreeText.Content,
@.i_FreeText))
, but it fails. It's okay when i use
SET @.i_FreeText= '"r-483"' (without asterix)
It's also okay when I search for
SET @.i_FreeText= '"filip-483*"'
I thought it's noise character problem, but after deleting noise words
in noise.* files nothing has changed... I have to use double quots ""
due to exact phases with spaces ex. "exact phase".
Can somebody help me ? Thank's in advance
Ragards
Filip Fiolka
This works for me. Exactly what do you mean by fails?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Filip Fiolka" <filip@.lac.gda.pl> wrote in message
news:f1eb0b6.0501070414.153a4007@.posting.google.co m...
> Hi
> I would like to search for "R-483" phase using SQL CONTAINS
> function :
> DECLARE @.i_FreeText Varchar(255)
> SET @.i_FreeText= '"r-483*"'
> SELECT TOP 10 * from iBlockFreeText
> where
> (@.i_FreeText is null or contains(iBlockFreeText.Content,
> @.i_FreeText))
> , but it fails. It's okay when i use
> SET @.i_FreeText= '"r-483"' (without asterix)
> It's also okay when I search for
> SET @.i_FreeText= '"filip-483*"'
> I thought it's noise character problem, but after deleting noise words
> in noise.* files nothing has changed... I have to use double quots ""
> due to exact phases with spaces ex. "exact phase".
> Can somebody help me ? Thank's in advance
> Ragards
> Filip Fiolka
|||Filip,
Sure, I can. Could you post the full output of -- SELECT @.@.version -- as
this information is most helpful in troubleshooting SQL FTS issues. Did you
delete all words in the language specific noise word file or just the single
letters? What noise word file did you delete the words from? Did you run a
Full Population after deleting these words?
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Filip Fiolka" <filip@.lac.gda.pl> wrote in message
news:f1eb0b6.0501070414.153a4007@.posting.google.co m...
> Hi
> I would like to search for "R-483" phase using SQL CONTAINS
> function :
> DECLARE @.i_FreeText Varchar(255)
> SET @.i_FreeText= '"r-483*"'
> SELECT TOP 10 * from iBlockFreeText
> where
> (@.i_FreeText is null or contains(iBlockFreeText.Content,
> @.i_FreeText))
> , but it fails. It's okay when i use
> SET @.i_FreeText= '"r-483"' (without asterix)
> It's also okay when I search for
> SET @.i_FreeText= '"filip-483*"'
> I thought it's noise character problem, but after deleting noise words
> in noise.* files nothing has changed... I have to use double quots ""
> due to exact phases with spaces ex. "exact phase".
> Can somebody help me ? Thank's in advance
> Ragards
> Filip Fiolka
|||Thank's for reply. I realized that I cannot delete all noise words from
noise.* file- I should leave space at last. After that I could find my
'"R-483*"' product. But I cannot still see the reason why :
CONTAINS(column,'R-438')
and
CONTAINS(column,'"R-438"')
works, but
CONTAINS(column,'"R-438*"') fails when "r" is a noise word?!
Perhaps You will be able to explain that.
SELECT @.@.version :
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows NT 5.2 (Build 3790: )
Thank's in advance
Filip Fiolka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You're welcome, Filip Fiolka,
This is why I ask for the @.@.version information in nearly all of my initial
replies. You have SQL Server 2000 SP3 installed on Windows Server 2003
(Windows NT 5.2) and this OS Platform uses a new wordbreaker dll
(langwbrk.dll) that breaks the string "R-438" as follows:
Original text: 'R-438'
IWordSink::PutWord: cwcSrcLen 1, cwcSrcPos 0, cwc 1, 'R'
IWordSink::PutAltWord: cwcSrcLen 3, cwcSrcPos 2, cwc 3, '438'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 2, cwc 5, 'NN438'
NN438 indicates that Win2003 treats this as a number. In the FT-enable
table's column text, do you have other rows of values, such as "R-4385" or
"R-43800" or is the trailing values alphanumeric values?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"FIlip FIolka" <filip@.lac.gda.pl> wrote in message
news:eWU#n6u9EHA.3624@.TK2MSFTNGP10.phx.gbl...
> Thank's for reply. I realized that I cannot delete all noise words from
> noise.* file- I should leave space at last. After that I could find my
> '"R-483*"' product. But I cannot still see the reason why :
> CONTAINS(column,'R-438')
> and
> CONTAINS(column,'"R-438"')
> works, but
> CONTAINS(column,'"R-438*"') fails when "r" is a noise word?!
> Perhaps You will be able to explain that.
> SELECT @.@.version :
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows NT 5.2 (Build 3790: )
> Thank's in advance
> Filip Fiolka
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||There are no other values like %R-438%. "R-483" is a specyfic name, so
there are no trailing values of that ("R-483" is never a prefix). My
FT-enable table's column rows consist of file start content.
Thanks
Filip Fiolka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Filip,
Ok, then the Windows Server 2003 (Win2003) wordbreaker dll - langwbrk.dll -
is treating this number "438" as a number:
IWordSink::PutAltWord: cwcSrcLen 3, cwcSrcPos 2, cwc 3, '438'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 2, cwc 5, 'NN438'
and not allowing any stemming of this number and therefore ignoring the
trailing wildcard "*". Specifically, when this query is executed:
CONTAINS(column,'"R-438*"')
and "R" is a noise word, the hyphen or dash "-" is thrown away and all that
is left is to return rows that contain the number 438. This behavior is
specific to Win2003 and its wordbreaker langwbrk.dll
Hope that helps,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"FIlip FIolka" <filip@.lac.gda.pl> wrote in message
news:ejOnLPh#EHA.2580@.TK2MSFTNGP15.phx.gbl...
> There are no other values like %R-438%. "R-483" is a specyfic name, so
> there are no trailing values of that ("R-483" is never a prefix). My
> FT-enable table's column rows consist of file start content.
> Thanks
> Filip Fiolka
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

FREETEXT results

Using the following query I get my desired results. However, in the
resulting recordset, how can I show for example 10 words before and 10 words
after the keyword or phrase that was searched upon? If I get 20 resulting
records I think it would be easier for the user to decide which they want to
view. I know Google does this. Is this an SQL issue or ASP issue?
thanks
DECLARE @.SearchString varchar(100)
SET @.SearchString = ' "stress" '
SELECT KEY_TBL.RANK, Title, Body
FROM Articles INNER JOIN
FREETEXTTABLE(Articles,*, @.SearchString) AS KEY_TBL
ON Articles.ID = KEY_TBL.[KEY]
ORDER BY Rank DESC
string handling is more efficiently done on the client level as opposed to
within SQL Server.
"shank" <shank@.tampabay.rr.com> wrote in message
news:Obj%23ddqZEHA.1508@.TK2MSFTNGP09.phx.gbl...
> Using the following query I get my desired results. However, in the
> resulting recordset, how can I show for example 10 words before and 10
words
> after the keyword or phrase that was searched upon? If I get 20 resulting
> records I think it would be easier for the user to decide which they want
to
> view. I know Google does this. Is this an SQL issue or ASP issue?
> thanks
> DECLARE @.SearchString varchar(100)
> SET @.SearchString = ' "stress" '
> SELECT KEY_TBL.RANK, Title, Body
> FROM Articles INNER JOIN
> FREETEXTTABLE(Articles,*, @.SearchString) AS KEY_TBL
> ON Articles.ID = KEY_TBL.[KEY]
> ORDER BY Rank DESC
>
|||Hilary,
While in some cases, client-side process might be better, for example
client-side paging and sorting of results, however, in this case, I must
respectively disagree...
Shank, you can use Substring and PatIndex along with your FREETEXTTABLE
query and get the "Goggle like" results that you have requested.
Specifically, the following SQL FTS query on the pubs table pub_info will
return rows that match the FTS search word (books) and display the near by
words from 20 characters before the searched keyword(books) for a total
length of 100 characters.
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#eM2eLrZEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> string handling is more efficiently done on the client level as opposed to
> within SQL Server.
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:Obj%23ddqZEHA.1508@.TK2MSFTNGP09.phx.gbl...
> words
resulting[vbcol=seagreen]
want
> to
>
|||I really don't think so. In fact I know so. You can easily demonstrate this
within vbscript.
In fact I have written and ISAPI extension that does this - and posted it
here. Thinking about doing it within a database is simply not a good choice.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%2364LxM9ZEHA.2792@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hilary,
> While in some cases, client-side process might be better, for example
> client-side paging and sorting of results, however, in this case, I must
> respectively disagree...
> Shank, you can use Substring and PatIndex along with your FREETEXTTABLE
> query and get the "Goggle like" results that you have requested.
> Specifically, the following SQL FTS query on the pubs table pub_info will
> return rows that match the FTS search word (books) and display the near by
> words from 20 characters before the searched keyword(books) for a total
> length of 100 characters.
> SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
> FROM pub_info
> WHERE Contains(pr_info, 'books')
> Regards,
> John
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#eM2eLrZEHA.644@.tk2msftngp13.phx.gbl...
to
> resulting
> want
>
|||Hi Hilary,
Then I guess we can agree that we disagree <G>, as I did say in " some
cases" and not in all cases, so we can agree, on a case-by-case basis. Are
you saying that in all cases, that all types of "string handling" are "bad"
if handled on the server-side?
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ue$hecFaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> I really don't think so. In fact I know so. You can easily demonstrate
this
> within vbscript.
> In fact I have written and ISAPI extension that does this - and posted it
> here. Thinking about doing it within a database is simply not a good
choice.[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%2364LxM9ZEHA.2792@.TK2MSFTNGP09.phx.gbl...
will[vbcol=seagreen]
by[vbcol=seagreen]
opposed[vbcol=seagreen]
> to
10
>

FreeText on multiple columns

Hello!
I need to do a freetext-search on multiple columns in the same table,
searching with the same keywords.
For example
WHERE FREETEXT({col1, col2}, "searching for this")
I know that this can be accomplished through
WHERE FREETEXT(col1, "searching for this")
OR FREETEXT(col2, "searching for this")
but then it will query the FTS-database (in this case) two times. But
the more oclumns the more queryies against the FTS-database.
I tried to concatenate the columns, like
WHERE FREETEXT(col1+col2, "searching for this")
but that wont work.
I know that this is possible to do through contains/containstable, but I
need to use freetext this time.
Suggestions?
Thanks
I just figured out that since I only have one FT-catalog (which has all
the columns I want to query against) on the table, I can do
WHERE FREETEXT(*, "searching for this")
dotNet wrote:
> Hello!
> I need to do a freetext-search on multiple columns in the same table,
> searching with the same keywords.
> For example
> WHERE FREETEXT({col1, col2}, "searching for this")
> I know that this can be accomplished through
> WHERE FREETEXT(col1, "searching for this")
> OR FREETEXT(col2, "searching for this")
> but then it will query the FTS-database (in this case) two times. But
> the more oclumns the more queryies against the FTS-database.
> I tried to concatenate the columns, like
> WHERE FREETEXT(col1+col2, "searching for this")
> but that wont work.
> I know that this is possible to do through contains/containstable, but I
> need to use freetext this time.
> Suggestions?
> Thanks
|||This is correct. With FreeText and FreeTextTable if you issue queries where
you don't qualify the column name(s) and your search phrase consists of more
than 1 token, ie "searching for this" has three tokens or words, your search
results might come from different columns. IE Searching in col1, for in
col2, and this in col3. Keep in mind that for, and this are noise words.
With a Contains and ContainsTable searches, all the tokens/words will have
to be in the same column for you to get a hit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"dotNet" <dotnet@.brimba.nu> wrote in message
news:u0MXYc5AFHA.2608@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I just figured out that since I only have one FT-catalog (which has all
> the columns I want to query against) on the table, I can do
> WHERE FREETEXT(*, "searching for this")
>
>
> dotNet wrote:

FREETEXT Help please

Hi All... I'm trying to play around with the "FREETEXT" function to see if it might help us with our application. I have a column of type "text" and I understand it needs to have the Full-text specification set to yes. But I cant seem to figure out how to do that. Can anyone help me out with this please?

Thanks! -- Curt

FreeText is not a function, it is a predicate used for FullText index searches, if you cannot find Full Text the questions is what version and edition of SQL Server are you using. In 2000 it is a separate install and it is not avalable in Express by default you have to use code to implement it. All other editions of 2005 you can enable it either from the menu or right click in Management Studio. Here are all the Full Text predicates CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTBALE, run a search for all in the BOL(books online). Hope this helps.

FREETEXT FILTER ON FREETEXTTABLE USING COLUMN LIST FOR REFINED SEARCH

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)

Freetext takes either a column or all (*). So, your query should be changed.

e.g.

Code Snippet

and (

FREETEXT(BrandName, @.SearchWord)

or

and FREETEXT(ManufacturerName, @.SearchWord)

or

and FREETEXT(CategoryName, @.SearchWord)

)

FREETEXT Document Title search

Is there a way using full text search to search on the title of a document
(i.e. The title used by Microsoft Word in it's title field, not the actual
name of the file)? I have stored procedures using FreeTextTable, but this
doesn't seem to be returning results based on the titles. Is this the
expected behavior? Is there a way around this? Thanks in advance!
No, there is no way to do this natively as SQL FTS does not index the
properties of documents only its contents. You would have to extract the
Document Title and store it in another column in the table.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jeremy" <jmaddreysp@.cox.net> wrote in message
news:OvcQ2YfhEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Is there a way using full text search to search on the title of a document
> (i.e. The title used by Microsoft Word in it's title field, not the actual
> name of the file)? I have stored procedures using FreeTextTable, but this
> doesn't seem to be returning results based on the titles. Is this the
> expected behavior? Is there a way around this? Thanks in advance!
>
>

Freetext box + SQL

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="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?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="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?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

You do this:

myExecuteQuery= "Update content_text Set title=@.title, text=@.text, section_no=@.section_no where id=@.id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)

Note you set myExecuteQuery, but you then pass mySelectQuery to the SqlCommand constructor. I strongly suggest using Option Explicit On, sinc ein this case I think it would have caught your error.|||Stupid mistake!

anyways, made that change and now get this error

Must declare the variable '@.title'.
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: Must declare the variable '@.title'.

Any idea's?

<code>
<%@. 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
dim myExecuteCmd As SqlCommand

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"
myExecuteCmd = New SqlCommand( myExecuteQuery, 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="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?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="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?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>
</code|||You need to set @.title, etc. parameters.

Here is some information on parameters|||Thanks for that, made those changes, but now getting an error as below, which is strange as the code is similar to what I used to add entries

Sorry to be a pain!

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'Text' is not a member of 'String'.

Source Error:

Line 74: myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
Line 75: myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
Line 76: myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
Line 77: myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
Line 78: myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)

<%@. 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
dim myExecuteCmd As SqlCommand
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")
dim e_id as integer
dim e_title, e_text, e_section_no as string
myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), e_TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), e_FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), e_TextBox).Text
myExecuteCmd.Connection.Open()
myExecuteCmd.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="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?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="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?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

|||Well, what do YOU think that error means?

Look at this code:

dim e_title, e_text, e_section_no as string
myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)

e_title is a string, and so, look at the docs for string. Do you see a .Text property of string? No, you do not. So, the error message was telling you EXACTLY what the problem is. You can just pass e_Title, since that is a string and that is what you are passing as the @.Title.

Freetext / Freetexttable on multiple tables

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?

Check out this link:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20880133.html

For more details about FREETEXT: http://technet.microsoft.com/en-us/library/ms176078.aspx

Good luck.

|||

Hi,

Thanks for the link. But I do not wish to subscribe tohttp://www.experts-exchange.com even though they offer a 7-day free trial.

Will appreciate if the solution/hint can be shown on this forum instead so that others can benefit from it too.

Many Thanks.

freetext

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 columns

something like

freetext(column1+column2,@.Search)

this will give error "Incorrect syntax near '+'."

Any One know the correct syntax.

Thanks

According tothe documentation, you use a comma-separated list enclosed in parentheses:

freetext( (column1, column2) , @.Search )