SQL Troubles...

[dodgyone]

I have a form with a drop down-menu to select the appropriate field from the database to search through and a text field to enter the search criteria. The drop-down menu also has a joint field to look up two memo fields in one go.

The problem is that I cannot get the SQL to search the database by the drop-down list joint field search. I've managed to get it working with single field searches but now I've hit a brick wall!

INFO... the drop-down list is called 'param'; the text box is called 'data; the twin field search is called 'joint'; the table is caleld 'pic_table'

<% Param = Request.QueryString(&quot;Param&quot
Data = Request.QueryString(&quot;Data&quot
data=replace(data,&quot;'&quot;,&quot;''&quot
data=replace(data,&quot;&quot;&quot;&quot;,&quot;&quot;&quot;&quot;&quot;&quot
%>
<font face=&quot;Arial&quot;><%If Param<>&quot;&quot; And Data <>&quot;&quot; then%>
</font>
<%
' set up database conection
' this connection does not require an ODBC reference
path = Trim(Server.MapPath(&quot;/fpdb&quot)
conn_string = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; + path + &quot;\images.mdb&quot;
Set con = Server.CreateObject(&quot;ADODB.Connection&quot
con.open conn_string

mode = &quot;filtered&quot;
mysql = &quot;SELECT * FROM [pic_table] where&quot;

If cstr(Param) <> &quot;&quot; And cstr(Data) <> &quot;&quot; Then
sql=&quot;[&quot; & cstr(Param) & &quot;] Like &quot; & chr(39) &&quot;%&quot;& cstr(Data) &&quot;%&quot;& chr(39)& &quot;;&quot;

End If

Else

If cstr(Param) = &quot;joint&quot; And cstr(Data) <> &quot;&quot; Then
sql=&quot;[Pic_table].[details] or [keywords] like '&quot; & cstr(data) & &quot;'&quot;

End If

rem single quote = 39, double quote = 34

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql, conn, 0,1

end if%>

Any comments would be appreciated...

Thanks in advance for your help,

Marcus

 

[rhyswilliams]

I think that your problem is with the where statement in you sql.
instead of reading:
&quot;where <FIELD1> or <FIELD2> like '%%%<CRITERIA>%%%'&quot;,
it should be in the form:
&quot;where <FIELD1> like '%%%<CRITERIA>%%%' or <FIELD2> like '%%%<CRITERIA>%%%'&quot;

i think i also spotted a typo in your code, i don't think that you are referencing a valid SQL statement when you open the recordset, instead of:
&quot;rs.Open sql, conn, 0,1&quot; which is using the WHERE criteria only, you should use something along the lines of:
&quot;mysql = mysql & &quot; &quot; & sql
'you need to add a space between WHERE and the query
'criteria when you combine the two strings
rs.Open mysql, conn, 0,1&quot;,
which combines the two parts of your sql string.

i hope that this solves your problem.
rhys.

 

[dodgyone]

Sorry to be a nuisance rhys but you have totally lost me... any chances of an example to help or some more detail.

Thanks, I really appreciate your efforts to help,

Marcus

 

[rhyswilliams]

don't worry about it...
ok, basically for each field that you want to search you need to set the WHERE criteria. that is, instead of writing
&quot;WHERE <FIELD1> or <FIELD2> like '%%%<CRITERIA>%%%'&quot; which is trying to share the criteria for two fields, you need to write
&quot;where <FIELD1> like '%%%<CRITERIA>%%%' or <FIELD2> like '%%%<CRITERIA>%%%'&quot;.

the sql variable in your code for the 'joint' search should read:
&quot;[Pic_table].[details] like '%%%&quot; & cstr(data) & &quot;%%%' or [keywords] like '%%%&quot; & cstr(data) & &quot;%%%'&quot;

an example of code that i have used follows:

set rst =Server.CreateObject(&quot;ADODB.Recordset&quot
rst.open &quot;SELECT Countries.*, Cities.*, Cities.City, Countries.Country &quot; & _
&quot;FROM Countries INNER JOIN Cities ON Countries.CountryID = Cities.CountryID &quot; & _
&quot;WHERE (((Cities.City) Like '%%%&quot;&request.form(&quot;OfficeSearch&quot&&quot;%%%')) OR &quot; & _
&quot;(((Countries.Country) Like '%%%&quot;&request.form(&quot;OfficeSearch&quot&&quot;%%%')) &quot; & _
&quot;order by countries.country, cities.city;&quot;, cnData, adOpenStatic, adLockOptimistic

as you can see, i am using this SQL statement to open a recordset of country and city information from the Countries and Cities tables where the City or Country field is like the value in the 'officesearch' control on the form requesting the page.

If you are having problems with SQL i can reccomend building the query using the access query editor using dummy values for criteria, pasting the SQL code into your ASP page and substituting the 'dummy' values you used in Access with variables from your site.

I hope this is clearer...

 

[dodgyone]

Cheers Rhys for your help.... I've set up the following but I keep on getting this error:

ADODB.Recordset error '800a0e7d'

The application requested an operation on an object with a reference to a closed or invalid Connection object.

/IDCNET Live/Image Database Copy/IMAGE_Process.asp, line 38

....it seems to have a problem with the line 'con.open conn_string' in my code below

THE CODE IS AS FOLLOWS:

<%
If Param<>&quot;&quot; And Data <>&quot;&quot; then

' set up database conection
' this connection does not require an ODBC reference
path = Trim(Server.MapPath(&quot;/fpdb&quot)
conn_string = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; + path + &quot;\images.mdb&quot;
set con = Server.CreateObject(&quot;ADODB.Recordset&quot
con.open conn_string

end if
%>

</font>

<%
sq11 = &quot;SELECT * FROM [pic_table] where&quot;

'the standard search after entering a field from the drop-down list and text criteria
IF cstr(Param) <> &quot;&quot; AND cstr(Data) <> &quot;&quot; THEN
sql2= &quot;[pic_table].[&quot; & cstr(Param) & &quot;] LIKE &quot; & chr(39) &&quot;%&quot;& cstr(Data) &&quot;%&quot;& chr(39)& &quot;;&quot;

END IF

'the search if the drop-down list displays the keywords and details joint search along with the text criteria
IF cstr(Param) = &quot;Details_&_Keywords_Joint_Search&quot; AND cstr(Data) <> &quot;&quot; THEN
sql3= &quot;[Pic_table].[details] LIKE &quot; & chr(39) &&quot;%&quot;& cstr(Data) &&quot;%&quot;& chr(39)& &quot; OR [keywords] LIKE &quot; & chr(39) &&quot;%&quot;& cstr(Data) &&quot;%&quot;& chr(39)& &quot;;&quot;

END IF

'to run the sql search statements above
sql=sql1 & sql2 & sql3

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql, conn, 0,1
%>

Any ideas?

Cheers,

Marcus

 

[rhyswilliams]

Spelling!! you created a connection spelt &quot;con&quot; and tried to reference one named &quot;conn&quot; (2 n's) when you open the recordset.
good luck!

 

[jonelf]

Always use
Option Explicit

 

[dodgyone]

For some reason it still comes up as an error when changing conn to con... I had already tried that but got desperate and starting putting anything in!

Helllllllllllllllp!!!!! ;o)

 

[rhyswilliams]

Right,
the code below should work. as you can see i renamed some variables but it is all pretty much as you left it.

good luck..

<%
If Param<>&quot;&quot; And Data <>&quot;&quot; then

' set up database conection
' this connection does not require an ODBC reference
path = Trim(Server.MapPath(&quot;/fpdb&quot)
conn_string = &quot;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=&quot; + path + &quot;\images.mdb&quot;
set cndata = Server.CreateObject(&quot;ADODB.Recordset&quot
cndata.open conn_string
end if

strSQL = &quot;SELECT * FROM [pic_table] where &quot;

'the standard search after entering a field from the drop-down list and text criteria
IF cstr(Param) <> &quot;&quot; AND cstr(Data) <> &quot;&quot; THEN
strSQL = strSQL & &quot;[pic_table].[&quot; & cstr(Param) & &quot;] LIKE '%&quot;& cstr(Data) &&quot;%';&quot;

END IF

'the search if the drop-down list displays the keywords and details joint search along with the text criteria
IF cstr(Param) = &quot;Details_&_Keywords_Joint_Search&quot; AND cstr(Data) <> &quot;&quot; THEN
strSQL = strSQL & &quot;[Pic_table].[details] LIKE '%&quot;& cstr(Data) &&quot;%' OR [keywords] LIKE '%&quot;& cstr(Data) &&quot;%';&quot;

END IF

'to run the sql search statements above

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open strSQL, cndata, 0,1
%>