SQL Syntax

[MediaDoc]

Hi Everyone,

Using Dreamweaver MX to create an ASP site. Its spitting out some SQL syntax, that looks right, but is not functioning.

I need a SELECT statement that will search across multiple fields.

Something like:

SELECT * FROM qrySearchResults
WHERE
(ProjectTitle OR SubTitle OR Objectives) LIKE 'variable'

It always chokes (no results) when I put in multiple fields to search in.

In case there are any dreamweaver folks out there, the exact code in VB for ASP is...

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/miteRNmetadata.asp" -->
<%
Dim rsSearchResults__varEntireRec
rsSearchResults__varEntireRec = "%"
If (Request.QueryString("EntireRec") <> "") Then
rsSearchResults__varEntireRec = Request.QueryString("EntireRec")
End If
%>
<%
Dim rsSearchResults
Dim rsSearchResults_numRows

Set rsSearchResults = Server.CreateObject("ADODB.Recordset")
rsSearchResults.ActiveConnection = MM_miteRNmetadata_STRING
rsSearchResults.Source = "SELECT * FROM qrySearchResults WHERE (ProjectTitle OR SubTitle OR Objectives) LIKE '" + Replace(rsSearchResults__varEntireRec, "'", "''") + "'"
rsSearchResults.CursorType = 0
rsSearchResults.CursorLocation = 2
rsSearchResults.LockType = 1
rsSearchResults.Open()

rsSearchResults_numRows = 0
%>

 

[glynch]

How about:

WHERE ProjectTitle LIKE variable OR SubTitle LIKE variable OR Objectives LIKE variable

I don't know Dreamweaver, but I think the syntax you tried would choke in a lot of different types of code.

HTH

 

[MediaDoc]

Solution

Solution found. The SQL is below (via VBScript of course).

The problem was with the form using POST, for some reason in this case the Form.Variable was never being passed to this script, thus nothing was being done. Changed the form method to "GET" and viola, it worked.

Ive added a bit more VBScript to it to make the search more flexible (via % - wildcards).

Hope this can help someone avoid the hours, and loss of hair!

Matts
-------------------------------------



<%
Dim rsMultiple__varEntireRec
rsMultiple__varEntireRec = "%"
If (Request.QueryString("EntireRec") <> "") Then
rsMultiple__varEntireRec = Request.QueryString("EntireRec")
rsMultiple__varEntireRec = "%" + rsMultiple__varEntireRec + "%"
End If
%>
<%
Dim rsMultiple
Dim rsMultiple_numRows

Set rsMultiple = Server.CreateObject("ADODB.Recordset")
rsMultiple.ActiveConnection = MM_miteRNmetadata_STRING
rsMultiple.Source = "SELECT MetaDataID, ProjectTitle, DomainName FROM qrySearchResults WHERE ProjectTitle LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR SubTitle LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR Objectives LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR StudyDesign LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR FieldSites LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR BiotaSpecies LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR BiotaMetals LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR BiotaEndpoints LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR MaterialsMedium LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR MaterialsMetals LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "' OR DomainName LIKE '" + Replace(rsMultiple__varEntireRec, "'", "''") + "'"
rsMultiple.CursorType = 0
rsMultiple.CursorLocation = 2
rsMultiple.LockType = 1
rsMultiple.Open()

rsMultiple_numRows = 0
%>
<%
Dim Repeat2__numRows
Dim Repeat2__index

Repeat2__numRows = 10
Repeat2__index = 0
rsMultiple_numRows = rsMultiple_numRows + Repeat2__numRows
%>

 

[Pat Hartman]

Are you aware that the LIKE operator forces a full table scan? The query optomizer cannot use any indexes so it must read EVERY record in the table to evaluate the selection criteria. You are not surrounding the variables with asterisks so the LIKE operator is essentially working as the = operator would but MUCH less efficiently.

 

[MediaDoc]

Efficiency of the LIKE operator

Hi Pat,

In my case, I think I have to use the LIKE, as the database contains a whack of memo and text fields, and I do have a line in there that surrounds the search term with % (wildcard).

I think to make my searches much more efficient I should build in the option on the search form for AND / OR / PRHASE options like you see on most common search engines.

That is the next thing to figure out! (Crash course on ASP / SQL / VBScript - all new to me).

Matts