[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Searching Access DB with ASP

Posted on 2005-04-06
26
Medium Priority
?
326 Views
Last Modified: 2012-06-22
i have created a search page using ASP with a MS Access backend.
the DB was 6 tables which are not related in anyway, but each table has a field called "Title"

i want to query all 6 tables based on the "Title" and return all the records that match from each table.

i thought i was all work with dummy data, but then problem that i came across, was it wasnt returning the correct records when the user would do a search.

e.g i know there is a record in the Title "FUNCTIONAL DESIGN SPECIFICATION"  in 1 of the 6 tables, so i enter into the search textfield on the ASP page, "design specification" and click search. then what happens is random documents are returns, documents that their Title dont contains are query words are returned, in some cases, all documents are returned from all 6 tables etc etc.

i need to get this sorted asap

any ideas what could be wrong? or any idea of how to query and search the db better than what ive already done?

all comments welcome.
any urls to better searching example in either PHP/ASP welcome but have to use Access DB as backend.

here is my code:
 
<html>
<head>
      <meta http-equiv="Expires" CONTENT="0">
        <meta http-equiv="Cache-Control" CONTENT="no-cache">
        <meta http-equiv="Pragma" CONTENT="no-cache">
      <title>Pegasus International (UK) Ltd Search Engine</title>
      <script language="Javascript">
      <!--
      function validate() {
            if (document.searchForm.search.value == "") {
                  alert('Sorry, your search query was empty.\n\nPlease search by Title for best results');
                  return false;
            }else{
                  return true;
            }
      }
      function resetSearch() {
            document.searchForm.search.value= "";
            document.searchForm.search2.value= "";
      }
//-->
</script>
<style type="text/css">
body{margin:5pxfont-size:9px;font-family:verdana;color:black;text-align:center;background-image: url(bk.png); background-repeat:no-repeat;}
table{font-size:12px;font-family:verdana;color:black;text-align:center;border-top-width: 1px;border-top-style: dotted;border-top-color: #888888;border-right-width: 1px;border-right-style: dotted;border-right-color: #888888;border-left-width: 1px;border-left-style: dotted;border-left-color: #888888;border-bottom-width: 1px;border-bottom-style: dotted;border-bottom-color: #888888;cellpadding:5px;cellspacing:3px;}
.results{font-size:12px;font-family:verdana;color:black;text-align:left;margin:0 0 10 8em;}
.header{font-size:22px;font-family:verdana;color:black;text-align:center;}
.footer{font-size:9px;font-family:verdana;color:black;text-align:center;}
.forms{font-size:12px;font-family:verdana;color:black;text-align:center;}
</style>
<%
Dim strURL
Dim blank
Dim numberOfRecords
Dim cnnSearch
Dim rstSearch
Dim strDBPath
Dim strSQL
Dim strSQLCount
Dim strSQLCount2
Dim strSQLCount3
Dim strSQLCount4
Dim strSQLCount5
Dim strSQLCount6
Dim strSearch
Dim strSearch2
strURL = Request.ServerVariables("URL")
strSearch = Request.QueryString("search")
strSearch2 = Request.QueryString("search2")
%>
</head>
<body>
<center><br><br><p class="header">Venture Database Search Engine</p>
<p class="forms">Please Search By Title  (% returns all)</p>
<form name="searchForm" action="<%= strURL %>" method="get" onsubmit="return validate()">
      <input name="search" value="<%= strSearch %>" / size="20"> OR
      <input name="search2" value="<%= strSearch2 %>" / size="20"><br><br>
      <input type="submit" value="  Start Searching  ">&nbsp;<input type="button" value="Clear Search Field" onclick="resetSearch();">
</form>
</center>
<%
blank = "&nbsp;"
If strSearch <> "" Then
strDBPath = Server.MapPath("venture.mdb")
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
%>
<%
strSQLCount = "SELECT COUNT(Title) as numRecords " _
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch = cnnSearch.Execute(strSQLCount)
%>
<br><br>
<p class="results"><b>ABB Vetco Gray Trees Project Documents:<br>Number of records found: <% =rstSearch("numRecords") %></b></p>
<%
strSQL = "SELECT * " _
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch = cnnSearch.Execute(strSQL)
%>
<table border="0" width="80%" align="center">
<tr><th>Project Document No</th><th>Vendor Document No</th><th>Title</th></tr>
<%
if not (rstSearch.EOF And rstSearch.BOF) then
      Do While Not rstSearch.EOF
             %>
            <tr onMouseOver="this.style.background='#DDDDFC'" onMouseOut="this.style.background='#ffffff'" style="cursor:hand;">
                  <td><% if trim(rstSearch.Fields("Project Document No").Value) <> "" then  response.write(rstSearch.Fields("Project Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch.Fields("Vendor Document No").Value) <> "" then  response.write(rstSearch.Fields("Vendor Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch.Fields("Title").Value) <> "" then  response.write(rstSearch.Fields("Title").Value) else response.write(blank) %></td>
            </tr>
            <%
            rstSearch.MoveNext
      Loop
else
      response.write ("<tr><td colspan=3><br><i>Sorry, No records found!<i></td></tr>")
end if
%>
</table>
<%
rstSearch.Close
Set rstSearch = Nothing
%>
<br><br>
<%
strSQLCount = "SELECT COUNT(Title) as numRecords " _
& "FROM DrilQuipTreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch2 = cnnSearch.Execute(strSQLCount)
%>
<br><br>
<p class="results"><b>Dril-Quip Trees Project Documents:<br>Number of records found: <% =rstSearch2("numRecords") %></b></p>
<%
strSQL = "SELECT * " _
& "FROM DrilQuipTreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch2 = cnnSearch.Execute(strSQL)
%>
<table border="0" width="80%" align="center">
<tr><th>Project Document No</th><th>Vendor Document No</th><th>Title</th></tr>
<%
if not (rstSearch2.EOF And rstSearch2.BOF) then
      Do While Not rstSearch2.EOF
            %>
            <tr onMouseOver="this.style.background='#DDDDFC'" onMouseOut="this.style.background='#ffffff'" style="cursor:hand;" >
                  <td><% if trim(rstSearch2.Fields("Project Document No").Value) <> "" then  response.write(rstSearch2.Fields("Project Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch2.Fields("Vendor Document No").Value) <> "" then  response.write(rstSearch2.Fields("Vendor Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch2.Fields("Title").Value) <> "" then  response.write(rstSearch2.Fields("Title").Value) else response.write(blank) %></td>
            </tr>
            <%
            rstSearch2.MoveNext
      Loop
else
      response.write ("<tr><td colspan=3><br><i>Sorry, No records found!<i></td></tr>")
end if
%>
</table>
<%
rstSearch2.Close
Set rstSearch2 = Nothing
%>
<br><br>
<%
strSQLCount = "SELECT COUNT(Title) as numRecords " _
& "FROM KvaernerTreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch3 = cnnSearch.Execute(strSQLCount)
%>
<br><br>
<p class="results"><b>Kvaerner Trees Project Documents:<br>Number of records found: <% =rstSearch3("numRecords") %></b></p>
<%
strSQL = "SELECT * " _
& "FROM KvaernerTreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch3 = cnnSearch.Execute(strSQL)
%>
<table border="0" width="80%" align="center">
<tr><th>Project Document No</th><th>Vendor Document No</th><th>Title</th></tr>
<%
if not (rstSearch3.EOF And rstSearch3.BOF) then
      Do While Not rstSearch3.EOF
             %>
            <tr onMouseOver="this.style.background='#DDDDFC'" onMouseOut="this.style.background='#ffffff'" style="cursor:hand;" >
                  <td><% if trim(rstSearch3.Fields("Project Document No").Value) <> "" then  response.write(rstSearch3.Fields("Project Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch3.Fields("Vendor Document No").Value) <> "" then  response.write(rstSearch3.Fields("Vendor Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch3.Fields("Title").Value) <> "" then  response.write(rstSearch3.Fields("Title").Value) else response.write(blank) %></td>
            </tr>
            <%
            rstSearch3.MoveNext
      Loop
else
      response.write ("<tr><td colspan=3><br><i>Sorry, No records found!<i></td></tr>")
end if
%>
</table>
<%
rstSearch3.Close
Set rstSearch3 = Nothing
%>
<br><br>
<%
strSQLCount = "SELECT COUNT(Title) as numRecords " _
& "FROM LasmoBirchLarchProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch4 = cnnSearch.Execute(strSQLCount)
%>
<br><br>
<p class="results"><b>Lasmo Birch Larch Project Documents:<br>Number of records found: <% =rstSearch4("numRecords") %></b></p>
<%
strSQL = "SELECT * " _
& "FROM LasmoBirchLarchProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch4 = cnnSearch.Execute(strSQL)
%>
<table border="0" width="80%" align="center">
<tr><th>Document Number</th><th>Title</th></tr>
<%
if not (rstSearch4.EOF And rstSearch4.BOF) then
      Do While Not rstSearch4.EOF
             %>
            <tr onMouseOver="this.style.background='#DDDDFC'" onMouseOut="this.style.background='#ffffff'" style="cursor:hand;" >
                  <td><% if trim(rstSearch4.Fields("Document Number").Value) <> "" then  response.write(rstSearch4.Fields("Document Number").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch4.Fields("Title").Value) <> "" then  response.write(rstSearch4.Fields("Title").Value) else response.write(blank) %></td>
            </tr>
            <%
            rstSearch4.MoveNext
      Loop
else
      response.write ("<tr><td colspan=2><br><i>Sorry, No records found!<i></td></tr>")
end if
%>
</table>
<%
rstSearch4.Close
Set rstSearch4 = Nothing
%>
<br><br>
<%
strSQLCount = "SELECT COUNT(Title) as numRecords " _
& "FROM Subsea7TreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch5 = cnnSearch.Execute(strSQLCount)
%>
<br><br>
<p class="results"><b>Subsea7 Trees Project Documents:<br>Number of records found: <% =rstSearch5("numRecords") %></b></p>
<%
strSQL = "SELECT * " _
& "FROM Subsea7TreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch5 = cnnSearch.Execute(strSQL)
%>
<table border="0" width="80%" align="center">
<tr><th>Project Document No</th><th>Vendor Document No</th><th>Title</th></tr>
<%
if not (rstSearch5.EOF And rstSearch5.BOF) then
      Do While Not rstSearch5.EOF
             %>
            <tr onMouseOver="this.style.background='#DDDDFC'" onMouseOut="this.style.background='#ffffff'" style="cursor:hand;" >
                  <td><% if trim(rstSearch5.Fields("Project Document No").Value) <> "" then  response.write(rstSearch5.Fields("Project Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch5.Fields("Vendor Document No").Value) <> "" then  response.write(rstSearch5.Fields("Vendor Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch5.Fields("Title").Value) <> "" then  response.write(rstSearch5.Fields("Title").Value) else response.write(blank) %></td>
            </tr>
            <%
            rstSearch5.MoveNext
      Loop
else
      response.write ("<tr><td colspan=3><br><i>Sorry, No records found!<i></td></tr>")
end if
%>
</table>
<%
rstSearch5.Close
Set rstSearch5 = Nothing
%>
<br><br>
<%
strSQLCount = "SELECT COUNT(Title) as numRecords " _
& "FROM TreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch6 = cnnSearch.Execute(strSQLCount)
%>
<br><br>
<p class="results"><b>Trees Project Documents:<br>Number of records found: <% =rstSearch6("numRecords") %></b></p>
<%
strSQL = "SELECT * " _
& "FROM TreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
& ";"
Set rstSearch6 = cnnSearch.Execute(strSQL)
%>
<table border="0" width="80%" align="center">
<tr><th>Project Document No</th><th>Vendor Document No</th><th>Title</th></tr>
<%
if not (rstSearch6.EOF And rstSearch6.BOF) then
      Do While Not rstSearch6.EOF
            %>
            <tr onMouseOver="this.style.background='#DDDDFC'" onMouseOut="this.style.background='#ffffff'" style="cursor:hand;" >
                  <td><% if trim(rstSearch6.Fields("Project Document No").Value) <> "" then  response.write(rstSearch6.Fields("Project Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch6.Fields("Vendor Document No").Value) <> "" then  response.write(rstSearch6.Fields("Vendor Document No").Value) else response.write(blank) %></td>
                  <td><% if trim(rstSearch6.Fields("Title").Value) <> "" then  response.write(rstSearch6.Fields("Title").Value) else response.write(blank) %></td>
            </tr>
            <%
            rstSearch6.MoveNext
      Loop
else
      response.write ("<tr><td colspan=3><br><i>Sorry, No records found!<i></td></tr>")
end if
%>
</table>
<%
rstSearch6.Close
Set rstSearch6 = Nothing
%>
<p class="footer">Copyright © 2005, Pegasus International (UK) Ltd, All Rights Reserved.</p>
</body>
</html>
<%
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>

Ellandrd
0
Comment
Question by:ellandrd
  • 16
  • 8
  • 2
26 Comments
 
LVL 16

Author Comment

by:ellandrd
ID: 13715379
i have been informed that my problem could be that im just querying the DB using LIKE which will return every title LIKE query, so i was told i need to search for titles that are exactly the same as search query?

any ideas how to incorporate that into my code?
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13715438
select * from mytable where myfield LIKE '" & myvariable & "'"    ' gets only exact matching records

select * from mytable where myfield LIKE '%" & myvariable & "'"    ' gets records ending with myvariable

select * from mytable where myfield LIKE '" & myvariable & "&'"    ' gets records starting with myvariable

select * from mytable where myfield LIKE '%" & myvariable & "%'"    ' gets records matching with myvariable

0
 
LVL 16

Author Comment

by:ellandrd
ID: 13715578
still returning unwanted records

here is my statement that im now using after looking at your answer:

strSQL = "SELECT * " _
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title LIKE '" & Replace(strSearch, "'", "''") & "' " _
& ";"

i know that there is a record that has a Title "EDP LIFT CAP" in the ABBVetcoGrayTreesProjectDocuments table.

so i search for all records that contain Titles like "caps"

result: i get records returned where the title is totally different and does not contains my query...

HELP!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Author Comment

by:ellandrd
ID: 13715672
update on my code:

strSQL = "SELECT * " _
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Title LIKE '" & Replace(strSearch, "'", "''") & "' " _
& ";"

still makes no changes
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13715691
my query above does work if i was to enter "cap" , i would return the record with the title "EDP LIFT CAP"
but if i was to enter "caps", it dont work

i need to be able to query the db with words that are exact match or like

Ellandrd
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13715708
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title = '" & Replace(strSearch, "'", "''") & "' " _
& "OR Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& ";"

not working either...
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13715743
title = "EDP LIFT CAP 1"
title = "EDP LIFT CAPS 2"

strSearch = "cap"
& "WHERE Title = '%" & Replace(strSearch, "'", "''") & "%' " _

should return "EDP LIFT CAP 1" and "EDP LIFT CAPS 2" 'both maches "cap" is in both of them


if strSearch = "caps" then

& "WHERE Title = '%" & Replace(strSearch, "'", "''") & "%' " _

this would return the "EDP LIFT CAPS 2"  'looks for "caps" not "cap"

bacause, "caps" and "cap"  are not same



0
 
LVL 16

Author Comment

by:ellandrd
ID: 13715983
yes i know cap and caps are diff, but im only using OR Title LIKE  also, if you look at my last piece of code:

& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title = '" & Replace(strSearch, "'", "''") & "' " _            ' gets only exact matching records
& "OR Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _         ' gets records matching records
& ";"

what you posted above is nto solving my problem...

i have over 1000 records to search through!

Ellandrd
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13716003
sorry this line should read:

yes i know cap and caps are diff, but im also using OR Title LIKE  too, if you look at my last piece of code:
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13716171
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title = '%" & Replace(strSearch, "'", "''") & "%' " _
& ";"

----------------------------
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title = '" & Replace(strSearch, "'", "''") & "' " _      ' no neeed for this expression 'exact match
& "OR Title LIKE '%" & Replace(strSearch, "'", "''") & "%' " _  ' this expression alredy contains any matchings
& ";"
---------------------------

try to run simple query
mSQL = "SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title LIKE '%cap%';"

see what is returns




0
 
LVL 16

Author Comment

by:ellandrd
ID: 13716233
ok so your syaing this should work returning records which titles are like "cap" and "caps" if query was "cap"

so then this code should work:

& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title = '%" & Replace(strSearch, "'", "''") & "%' " _
& ";"

well big surprize, it dont!

either no records are returned or i get all sorts of records returned thats titles are totally diff to query enter....
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13716305
>>ok so your syaing this should work returning records which titles are like "cap" and "caps" if query was "cap"

no, it will return only matchings "cap"

also (= changed to LIKE )

& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title  LIKE  '%" & Replace(strSearch, "'", "''") & "%' " _
& ";"





0
 
LVL 16

Author Comment

by:ellandrd
ID: 13716724
ok the above works IF i search for a record where the record title is LIKE e.g "material selection" as i know there is a record where the title is "LNS3-M-B-0044 - BIRCH SERVICE PIPELINE MATERIAL SELECTION SUMMARY" and the query is "material selection" so it matches. all well and good,

but how can i get the same record returned if my users search for "pipeline selection"

i need to be able to search for 2 key words for the same title thats why i was using OR...

can you recode my code below so the above would work


strSQL = "SELECT * " _
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title Like '%" & strSearch & "%' " _
& ";"

remember the user can enter any of the keys in the title, weather it be material selection or pipeline selection and both times the same records should be returned....


0
 
LVL 13

Expert Comment

by:nurbek
ID: 13716939
ok i got it now :)

try this
 
sCriterias = Replace(strSearch," ","' OR title LIKE '%")  'replace " " with search criterias
sCriterias = Replace(sCriterias,"'","''")

strSQL = "SELECT * " _
& "FROM ABBVetcoGrayTreesProjectDocuments " _
& "WHERE Title Like '%" & sCriterias & "%' " _
& ";"
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13717217
sorry there was a fired alarm and everyone had to leave the building.. im back now

right im abit confused with this:

sCriterias = Replace(strSearch," ","' OR title LIKE '%")  'replace " " with search criterias
sCriterias = Replace(sCriterias,"'","''")

do i just leave this the way it is and add it to my code or what?
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13717426
there is a record in db with a title "LNS3-M-B-0044 - BIRCH SERVICE PIPELINE MATERIAL SELECTION SUMMARY"

test 1:
i queried the db for "pipeline selection" and i got no record found...

test 2:
i queried the db again for "material selection" and i got 4 record found...

test 3:
i queried the db again for "material" and i got 100+ records found...

test 4:
i queried the db again for "selection" and i got 100+ records found...

test 5:
i queried the db again for "pipeline" and i got 3000+ records found...

the first test i need working...

Ellandrd
0
 
LVL 12

Expert Comment

by:fruhj
ID: 13718513
I'll pipe in here since I haven't seen this mentioned:

I would do the search as a single, multipart query, starting with a select distinct or select distinctrow with each part unioned together,

THe distinct/distinctrow is used to weed out duplicates (which could arise becuase of your or statement)

The union works like this

Select distinctrow from
(Select afield1 as 'F1', afield2 as 'F2' where ..
  UNION
  Select bfield1 as 'F1', bfield2 as 'F2' where...
  UNION
 etc...

the as statements give the fields a common name across each select statement - thats needed so the union statements can properly join them all together.

hope this helps
- Jack
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13719022
>>i queried the db for "pipeline selection" and i got no record found...

use the below code as provided (add to your code)


strSearch = "pipeline selection"
sCriterias = Replace(strSearch," ","%' OR title LIKE '%")  

strSQL = "SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title Like '%" & sCriterias & "%';"
-----query ---
SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title Like '%pipeline%' OR title LIKE '%selection%' ;

this should return any matchings (that contains "pipeline" or "selection")

now (changind OR with AND)

strSearch = "pipeline selection"
sCriterias = Replace(strSearch," ","%' AND title LIKE '%")  

strSQL = "SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title Like '%" & sCriterias & "%';"
-----query ---
SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title Like '%pipeline%' AND title LIKE '%selection%' ;

this should return records that contains both "pipeline" and "selection"





0
 
LVL 16

Author Comment

by:ellandrd
ID: 13719339
jack,

hope would i recode the code i have to use what you suggested?

nurbek,

i will try this when i get bk into work, as i dont have access the database at home...

so say for another example...

record title = "LNS3-M-B-0044 - BIRCH SERVICE PIPELINE MATERIAL SELECTION SUMMARY"
user enters "title "BIRCH PIPELINE SELECTION"

this code would still work:

strSearch = "birch pipeline selection"
sCriterias = Replace(strSearch," ","%' OR title LIKE '%")  

strSQL = "SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title Like '%" & sCriterias & "%';"

Ellandrd
0
 
LVL 12

Expert Comment

by:fruhj
ID: 13719520
You start opening a can of worms if you want to allow a google style search - lots of options on a single text line - I think I spent 2-3 weeks working on a text parser for our search project.

for example if someone searches for "this  has  two  spaces" yourre replace would insert extra or title like statements
so you'd first want to reduce every instance of two spaces down to one
do until instr(strSearch, "  ") =0 'theres' two spaces between the quotes
   strSearch = replace(strSearch, "  ", " ")
loop

Then there's other issues that come up - if you want the user to be able to enter AND and OR statements etc...

However there's one assumption I think you can make...
if a user searches on "birch pipeline selection" - they probably want ALL 3 in the results.
then the problem becomes how to do this across 6 tables - one table might have one, another might have the last two - does that count as a hit. etc...

I probably won't have time to work on your code till tomorrow.

- Jack
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13720706
its ok, i have time...
0
 
LVL 13

Accepted Solution

by:
nurbek earned 2000 total points
ID: 13724346
fruhj:

Trim function does the same thing as your loop :), removes extra blank spaces


there may be choice for the user (Any Words  or All Words) radio button

<input name="AnyOrAll" type="radio" value="OR"> Any Words
<input name="AnyOrAll" type="radio" value="AND"> All Words

strSearch = " pipeline selection  "
'AnyOrAll = "AND"
AnyOrAll = Request("AnyOrAll")

strSearch = Trim(strSearch)
sCriterias = Replace(strSearch," ","%' " & AnyOrAll & " title LIKE '%")  

strSQL = "SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title Like '%" & sCriterias & "%';"

Regards

0
 
LVL 16

Author Comment

by:ellandrd
ID: 13724415
nurbek,

great idea to use the radio buttons, i never though of that!

will try this idea right now and will get back to you...

Ellandrd
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13724449
nurbek,

remember i couldnt try this code:

strSearch = "pipeline selection"
sCriterias = Replace(strSearch," ","%' OR title LIKE '%")  

strSQL = "SELECT * FROM ABBVetcoGrayTreesProjectDocuments WHERE Title Like '%" & sCriterias & "%';"

till i got back into work, well i did, and it has worked, but i still want to use the radio button idea now...

will try to add the radio button code into what ive got so far and get back to you...
0
 
LVL 16

Author Comment

by:ellandrd
ID: 13724484
nurbek this idea of using radio buttons has worked...

i just want to test the rest fo the tables as im only testing 1 table at the moment.

0
 
LVL 13

Expert Comment

by:nurbek
ID: 13724622
use the same logic for other tables

and i am sure that others also work :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question