Need Dynamic SQL Select turotrial

Need a tutorial on how to use ASP.net to dynamically build a SQL2000 compatible SELECT statement for a multiple keyword search. I need a search that will find any combination of one or more keywords in the Keyword string in any order.

Thank you

Do you mean full text searching? (Searching for a particular word within a lot of text)

|||

I have a database column 'Description' varchar(600) that contains product descriptions. I need to search it looking for any of the keywords contained in a Keyword string.

Example Keyword string - sail boat hardware

I need it to find all rows WHERE 'sail' or 'boat' or 'hardware' is found.

Thank you

|||

That form of search is more a full text search rather than a dynamic search. The latter is searching on one to many columns as selected at runtime.

|||

I am using a shared SQL2000 server. What I want to do is find a way in ASP.Net to take the keyword string entered and convert it into a Sql2000 select statement like below.

Keywords entered: sail boat hardware

SELECT Company, Phone, Description
FROM tblCompanyInfo
WHERE (tbl.CompanyInfo.Description LIKE '%' + "sail" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "boat" + '%') OR (tbl.CompanyInfo.Description LIKE '%' + "hardware" + '%')

|||

Look athttp://msdn2.microsoft.com/en-gb/sql/aa336289.aspx

|||

I am actualy looking for an ASP routine that splits the Keyword string and creates the OR elements for a Sql2000 compatible Sql SELECT command. I have looked at the many CONTAINS options discussed in the article but did not find any that offer a split like solution. As for a full text search, that option is not available at least not in the shared SQL environment that I am using.

Thank you

|||

In which case you need to roll your own text search solutions.

Create a Noise words table to contain words that you do not want indexed.

|||

Looks like someone should create a new ASP.Net SearchControl. A bunch of us out here could sure use it.

Thank you for the follow up.

sql

Need child TABLE object to ref diff DATASET than parent LIST object.

Dang, I wish I had figured this out without having to ask.
Looked and looked for a similar thread - but no real "keyword" for
this issue.
Here's the situation; imagine you have customers and one database
table has demographic information while another database table has a
list of customer orders. Now you want a report that shows all that on
a document map-driven report.
Here's the page layout:
LIST OBJECT // DATASET(dsDemographics) GROUPBY(customer)
DOCMAP(customer)
>> TABLE OBJECT DATASET(dsDemographics) GROUPBY()
>> TABLE OBJECT DATASET(dsOrders) GROUPBY(orderdate)
>> TABLE OBJECT DATASET(dsTickets) GROUPBY(createdate)
Here's the problem: the second table containing orders will not
reference the dsOrders DATASET unless through aggregations - which is
obviously not what I am wanting to do. I want to list all the orders
for the current customer. Same problem with the third table - and
others.
Now, I see the problem is very clear. The orders DATASET does not know
how to filter itself on the current customer. That much I understand.
How do I do it?
Thanks in advance, JerrySounds like your needing to join the datasets. Easiest way would be to join
the data prior to pulling it into RS because, as you noted, RS can only join
data from one dataset to another using an aggregate. If one of your
datasets is SQL, you might use a linked server to pre-join your data.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410221151.4074f9f8@.posting.google.com...
> Dang, I wish I had figured this out without having to ask.
> Looked and looked for a similar thread - but no real "keyword" for
> this issue.
> Here's the situation; imagine you have customers and one database
> table has demographic information while another database table has a
> list of customer orders. Now you want a report that shows all that on
> a document map-driven report.
> Here's the page layout:
> LIST OBJECT // DATASET(dsDemographics) GROUPBY(customer)
> DOCMAP(customer)
>> TABLE OBJECT DATASET(dsDemographics) GROUPBY()
>> TABLE OBJECT DATASET(dsOrders) GROUPBY(orderdate)
>> TABLE OBJECT DATASET(dsTickets) GROUPBY(createdate)
> Here's the problem: the second table containing orders will not
> reference the dsOrders DATASET unless through aggregations - which is
> obviously not what I am wanting to do. I want to list all the orders
> for the current customer. Same problem with the third table - and
> others.
> Now, I see the problem is very clear. The orders DATASET does not know
> how to filter itself on the current customer. That much I understand.
> How do I do it?
> Thanks in advance, Jerry

Near keyword in Full Text Search SQL Server 2005

I have a SQL Server 2005 with SP2 and when use the FTS, a could observe that
AND and NEAR opertors are returning exactly the same results.
Anyone has any ideas?
Tanks in advanced
With FreeText they do. With contains if they are near to each other they
will as well, with containstable the rank will drop off after a separation
distance of 50 words, results with a separation distance in excess of 50
words will not show up. With freetexttable after 50 words the rank will be 0
but they will show up until 1326 (IIRC) words separation - this is by
design.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BrunoX" <BrunoX@.discussions.microsoft.com> wrote in message
news:87CE32E0-180E-4327-A264-D9ABFFE5D3CB@.microsoft.com...
>I have a SQL Server 2005 with SP2 and when use the FTS, a could observe
>that
> AND and NEAR opertors are returning exactly the same results.
> Anyone has any ideas?
> Tanks in advanced
|||Tanks Hilary,
Now I am using CONTAINSTABLE and analyzing the proximity by the value of rank.
"Hilary Cotter" wrote:

> With FreeText they do. With contains if they are near to each other they
> will as well, with containstable the rank will drop off after a separation
> distance of 50 words, results with a separation distance in excess of 50
> words will not show up. With freetexttable after 50 words the rank will be 0
> but they will show up until 1326 (IIRC) words separation - this is by
> design.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "BrunoX" <BrunoX@.discussions.microsoft.com> wrote in message
> news:87CE32E0-180E-4327-A264-D9ABFFE5D3CB@.microsoft.com...
>
>

Near keyword in Full Text Search SQL server 2005

We've migrated to SQL 2005 and we are seeing that AND and NEAR opertors are
returning exactly the same results, this behavior did not occur in SQL 2000.
example:
select count(*) from table1 where contains(*,'foo near bar') = 2494
select count(*) from table1 where contains(*,'"foo" and "bar"') = 2494
I have tried all of the syntax options I could find and am also able to
provide examples where foo and bar are quite far apart (~3000 characters) far
more than 50 words I've seen quoted for 2005. Appreciate if anyone has any
ideas on why. Do I need to change some setting in the SQL Server?
ThanksI reported this as a bug to Microsoft some time ago, and I was told that
this behavior is by design due to a customer request. Here is my original
post.
http://groups.google.com/group/microsoft.public.sqlserver.fulltext/msg/252dd1d3d6fedaa0?dmode=source
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"so3" <so3@.discussions.microsoft.com> wrote in message
news:31E328D2-CB77-47A1-9B19-A319DBE29320@.microsoft.com...
> We've migrated to SQL 2005 and we are seeing that AND and NEAR opertors
> are
> returning exactly the same results, this behavior did not occur in SQL
> 2000.
> example:
> select count(*) from table1 where contains(*,'foo near bar') = 2494
> select count(*) from table1 where contains(*,'"foo" and "bar"') = 2494
> I have tried all of the syntax options I could find and am also able to
> provide examples where foo and bar are quite far apart (~3000 characters)
> far
> more than 50 words I've seen quoted for 2005. Appreciate if anyone has
> any
> ideas on why. Do I need to change some setting in the SQL Server?
> Thanks

Near keyword in Full Text Search SQL server 2005

We've migrated to SQL 2005 and we are seeing that AND and NEAR opertors are
returning exactly the same results, this behavior did not occur in SQL 2000.
example:
select count(*) from table1 where contains(*,'foo near bar') = 2494
select count(*) from table1 where contains(*,'"foo" and "bar"') = 2494
I have tried all of the syntax options I could find and am also able to
provide examples where foo and bar are quite far apart (~3000 characters) fa
r
more than 50 words I've seen quoted for 2005. Appreciate if anyone has any
ideas on why. Do I need to change some setting in the SQL Server?
ThanksI reported this as a bug to Microsoft some time ago, and I was told that
this behavior is by design due to a customer request. Here is my original
post.
http://groups.google.com/group/micr...a0?dmode=source
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"so3" <so3@.discussions.microsoft.com> wrote in message
news:31E328D2-CB77-47A1-9B19-A319DBE29320@.microsoft.com...
> We've migrated to SQL 2005 and we are seeing that AND and NEAR opertors
> are
> returning exactly the same results, this behavior did not occur in SQL
> 2000.
> example:
> select count(*) from table1 where contains(*,'foo near bar') = 2494
> select count(*) from table1 where contains(*,'"foo" and "bar"') = 2494
> I have tried all of the syntax options I could find and am also able to
> provide examples where foo and bar are quite far apart (~3000 characters)
> far
> more than 50 words I've seen quoted for 2005. Appreciate if anyone has
> any
> ideas on why. Do I need to change some setting in the SQL Server?
> Thanks|||I reported this as a bug to Microsoft some time ago, and I was told that
this behavior is by design due to a customer request. Here is my original
post.
http://groups.google.com/group/micr...a0?dmode=source
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"so3" <so3@.discussions.microsoft.com> wrote in message
news:31E328D2-CB77-47A1-9B19-A319DBE29320@.microsoft.com...
> We've migrated to SQL 2005 and we are seeing that AND and NEAR opertors
> are
> returning exactly the same results, this behavior did not occur in SQL
> 2000.
> example:
> select count(*) from table1 where contains(*,'foo near bar') = 2494
> select count(*) from table1 where contains(*,'"foo" and "bar"') = 2494
> I have tried all of the syntax options I could find and am also able to
> provide examples where foo and bar are quite far apart (~3000 characters)
> far
> more than 50 words I've seen quoted for 2005. Appreciate if anyone has
> any
> ideas on why. Do I need to change some setting in the SQL Server?
> Thanks