Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Seach for whole word with ASP / /MS Access

Reply
Thread Tools

Seach for whole word with ASP / /MS Access

 
 
Giles
Guest
Posts: n/a
 
      05-18-2005
Is there a way to get records containing a whole word? I've heard regular
expressions can do it, but I can't make one work in an ASP / MS Access SQL
Query

sSQL="SELECT PageTitle FROM Pages WHERE PageTitle LIKE '%cat%' "
rs.open etc

returns scatty, catatonic etc. Thanks for your help
Giles



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-18-2005
Giles wrote:
> Is there a way to get records containing a whole word? I've heard
> regular expressions can do it, but I can't make one work in an ASP /
> MS Access SQL Query
>
> sSQL="SELECT PageTitle FROM Pages WHERE PageTitle LIKE '%cat%' "
> rs.open etc
>
> returns scatty, catatonic etc. Thanks for your help
> Giles

The surest way to do this would be to create an "index" table. I would not
perform this task in asp. VBA code would be best. You can use Windows
Scheduler to casue the task to run periodically. you should ask in an Access
newsgroup for details about running VBA code from the command-line used to
open your database in Access.

Here are the bare bones (this is untested "air" code) of what this task
would do (I'm assuming your table has an autonumber PageID field ...):

First create the PageIndex table. It should have two fields: PageID and
Words

Sub RebuildIndex()
dim cn as adodb.connection
dim rs as adodb.recordset
dim cmd as adodb.command
dim ar as variant
dim arParms as variant
dim sSQL As String,
dim data as string
dim i as integer

set cn = Application.CurrentProject.AccessConnection

'first, clear the pageindex table:
cn.execute "delete from PageIndex",, _
adCmdText + adExecuteNoRecords

set rs = new adodb.recordset
rs.cursorlocation=adUseClient
rs.Open "Select PageID, PgeTitle FROM Pages", cn,,,adCmdText
set rs.activeconnection=nothing
sSQL = "INSERT INTO PageIndex (PageID, Words) VALUES(?,?)
set cmd=new adodb.command
cmd.activeconnection=cn
cmd.CommandText=sSQL
do until rs.eof
data=rs(1)
data = replace(data,"."," ")
'repeat to remove the other punctuation marks
'hopefully somebody wil jump in with a regexp patrern to
'replace them all in one shot

'Optionally, use regexp to eliminate "nuisance" words, such as articles

ar=Split(data, " ")
for i = 0 to ubound(ar)
arParms=Array( rs(0) , ar(i))
cmd.Execute ,arParms,adCmdText + adExecuteNoRecords
next

loop

End Sub


Now, querying for specific words will be as simple as:
select DISTINCT PageTitle
FROM Pages p INNER JOIN PageIndex i
ON p.PageID = i.PageID
WHERE Words = "cat"

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
asp.net, code for seach button to parse the value sherry ASP .Net 0 04-15-2009 01:43 PM
Does seach engines, find text that is not visible on the page. jambiani HTML 4 09-04-2006 09:31 AM
*WITHOUT* using: ValidateRequest="False" for the whole page (or my whole site).... \A_Michigan_User\ ASP .Net 2 08-21-2006 02:13 PM
Problem with Content Pages and Seach Capability Chris Lincoln ASP .Net 3 07-19-2006 08:42 PM
Seach engine? Word searching for, in not on web page? lbbs Computer Support 7 10-10-2003 10:59 AM



Advertisments