problem with my sql statement

[aamaker]

Im working on a page within a site where users need to be able to search for combinations of a 3 digit number that are stored in an access 2000 database.

So if John Doe wants to know what combinations of the sequence 1 - 2 - 3 exist in the database the following types of data would be returned:

3 - 1 - 2
2 - 3 - 1
1 - 2 - 3


Im using a form that posts the results to itself and heres my sql statement... (below) ... the problem is that it retrieves instances where even ONE digit matches as opposed to returning ONLY the records where the 3 numbers are represented... what am I doing wrong - still somewhat a newbie with ASP - but I know that this is possible ... it has to be.



' Build our query based on the input.
strSQL = "SELECT id, drawmonth, drawday, drawyear, digit1, digit2, digit3 " _
& "FROM pick3 " _
& "WHERE digit1 LIKE '%" & strSearch1 & "%' OR digit1 LIKE '%" & strSearch2 & "%' OR digit1 LIKE '%" & strSearch3 & "%'" _
& "And digit2 LIKE '%" & strSearch2 & "%' OR digit2 LIKE '%" & strSearch1 & "%' OR digit2 LIKE '%" & strSearch3 & "%'" _
& "And digit3 LIKE '%" & strSearch3 & "%' OR digit3 LIKE '%" & strSearch1 & "%' OR digit3 LIKE '%" & strSearch2 & "%'" _
& "ORDER BY id;"

 

[madhouse]

You've shown your SQL statement but how are you posting the values i.e. what fields do you have in your form? Have you just got a single text box where a user enters the numbers?

 

[Tarwn]

You need to section off parts of your SQL statement so that the boolean test will work better, basically you want:
If digit1 is in there AND digit two is in there AND digit 3 is in there, so you structure should look like:
SELECT ___ FROM pick3 WHERE
(Digit1 LIKE '%%' OR Digit2 LIKE '%%' OR Digit3 LIKE '%%')
AND (Digit1 LIKE '%%' OR Digit2 LIKE '%%' OR Digit3 LIKE '%%')
AND (Digit1 LIKE '%%' OR Digit2 LIKE '%%' OR Digit3 LIKE '%%')
Now we have three groups where each group must have one tru item in it to make the whole statement true.

strSQL = "SELECT id, drawmonth, drawday, drawyear, digit1, digit2, digit3 " _
        & "FROM pick3 " _
        & "WHERE (digit1 LIKE '%" & strSearch1 & "%' OR digit1 LIKE '%" & strSearch2 & "%' OR digit1 LIKE '%" & strSearch3 & "%')" _
        & "And (digit2 LIKE '%" & strSearch1 & "%' OR digit2 LIKE '%" & strSearch2 & "%' OR digit2 LIKE '%" & strSearch3 & "%')" _
        & "And (digit3 LIKE '%" & strSearch1 & "%' OR digit3 LIKE '%" & strSearch2 & "%' OR digit3 LIKE '%" & strSearch3 & "%')" _
        & "ORDER BY id;"

One other change you may want to make, if the three digit field are only a single character text field you shouldn't need to use LIKE, you can use =.

Hope this helps,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
This space has nothing in it, it's all ni your imagination

 

[aamaker]

Thanks... heres all the ASP from that page... let me know what I need to do to get this to work correctly:


------------------ code excerpt follows ----------------

<%
If strSearch3 <> &quot;&quot; and strSearch2 <> &quot;&quot; and strSearch1 <> &quot;&quot; Then
strDBPath = Server.MapPath(&quot;secure\pick3\database\winning_numbers.mdb&quot


' Create an ADO Connection to connect to the database.
Set cnnSearch = Server.CreateObject(&quot;ADODB.Connection&quot

' This line is for the Access sample database:
cnnSearch.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & strDBPath & &quot;;&quot;


' Build our query based on the input.
strSQL = &quot;SELECT id, drawmonth, drawday, drawyear, digit1, digit2, digit3 &quot; _
& &quot;FROM pick3 &quot; _
& &quot;WHERE digit1 LIKE '%&quot; & strSearch1 & &quot;%' OR digit1 LIKE '%&quot; & strSearch2 & &quot;%' OR digit1 LIKE '%&quot; & strSearch3 & &quot;%'&quot; _
& &quot;And digit2 LIKE '%&quot; & strSearch2 & &quot;%' OR digit2 LIKE '%&quot; & strSearch1 & &quot;%' OR digit2 LIKE '%&quot; & strSearch3 & &quot;%'&quot; _
& &quot;And digit3 LIKE '%&quot; & strSearch3 & &quot;%' OR digit3 LIKE '%&quot; & strSearch1 & &quot;%' OR digit3 LIKE '%&quot; & strSearch2 & &quot;%'&quot; _
& &quot;ORDER BY id;&quot;

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.Execute(strSQL)
%>
<table border=&quot;0&quot;>
<tr>

<th colspan=2 bgcolor=&quot;silver&quot;>Search Results for <font color=&quot;blue&quot;><%= strSearch1 %> - <%= strSearch2 %> - <%= strSearch3 %></font> </th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><li><%= rstSearch.Fields(&quot;drawmonth&quot.Value %> <%= rstSearch.Fields(&quot;drawday&quot.Value %>, <%= rstSearch.Fields(&quot;drawyear&quot.Value %> </td><td><%= rstSearch.Fields(&quot;digit1&quot.Value %> - <%= rstSearch.Fields(&quot;digit2&quot.Value %> - <%= rstSearch.Fields(&quot;digit3&quot.Value %> </td>
</tr>

<%

rstSearch.MoveNext
Loop
%>


<%
if rstSearch.BOF then
Response.write(&quot;<tr><td colspan=2><font color=red size=2>Sorry we did not find any numbers for your entry.</font></td></tr>&quot
end if
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If


%>

--------------------- end code excerpt -------------------

 

[mwolf00]

Since you are using single digits, I think the follwong structure would be easier...

digits = &quot;(&quot; & strSearch1 & &quot;,&quot; & strSearch2 & &quot;,&quot; & strSearch3 & &quot&quot;

strSQL = &quot;SELECT id, drawmonth, drawday, drawyear, digit1, digit2, digit3 &quot; _
& &quot;FROM pick3 &quot; _
& &quot;WHERE digit1 IN &quot; & digits &&quot; AND digit2 IN &quot; & digits & &quot; AND digit3 IN &quot; & digits _
& &quot;ORDER BY id;&quot; -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[aamaker]

Thanks - I knew there was a simpler way I was overlooking --

but now I get the following error:

Data type mismatch in criteria expression. when I try to execute a search.

--any idea where the problem lies?


-- Thanks again.

 

[mwolf00]

After you form the SQL statement, write it out and stop execution of you code - that's the easiest way to debug a concatenated string that is causing errors. If you don't see the error in the string, then just post the result here and we'll debug it for you.

digits = &quot;(&quot; & strSearch1 & &quot;,&quot; & strSearch2 & &quot;,&quot; & strSearch3 & &quot&quot;

strSQL = &quot;SELECT id, drawmonth, drawday, drawyear, digit1, digit2, digit3 &quot; _
& &quot;FROM pick3 &quot; _
& &quot;WHERE digit1 IN &quot; & digits &&quot; AND digit2 IN &quot; & digits & &quot; AND digit3 IN &quot; & digits _
& &quot;ORDER BY id;&quot;

response.write strSQL
response.end ---------------------------------------
HAPPY THANKSGIVING!!!!
mikewolf@tst-us.com

 

[Tarwn]

It appears from earlier posts that your digit fields are string or characters fields, in that case you will need to place single quotes around the digits in your list:

digits = "('" & strSearch1 & "','" & strSearch2 & "','" & strSearch3 & "')"

this way they will be evaluated as strings.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

http://www.tuxedo.org/~esr/faqs/smart-questions.html