asked on

Simple ASP interrogation of an ODBC database

Please can someone sort me out a simple ASP search page which will search an Access database field for keywords in two indexed text fields.
I need the page to work on NT server with Front Page.
I know next to nothing about ASP.

I am presently using Personal Web Server, a whole bunch of ASP stuff and suspect I may have some Frontpage files lurking in the system (whether this makes a difference I don't know). I recently got a couple of downloaded and adulterated pages working, they are at the end of this message. I reckon the main page is mostly "unnecessary" stuff but I can't work it out.

The pages only find fields which totally match the user input, it doesn't recognise part of a string of keywords. Neither are there any boolean options. I need both of these.

Please can you help me with this.

search input page (searchform.asp):

<html>
<body>
<form method="POST" action="Scripts/mainlibsearch.asp">
<p><strong>Keywords:</strong> <input type="text" name="Keywords" size="20"><br>
<strong>Authors:</strong> <input type="text" name="Authors" size="20"><br>
<br>
<input type="submit" value="Search" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>

</body>
</html>

main page returning results (mainlibsearch.asp):

<html>

<head>

<%
' Substitute in form parameters into the query string
fp_sQry = "SELECT Keywords, Authors, Date FROM library WHERE Keywords = '%%Keywords%%' OR Authors= '%%Authors%%'"
fp_sDefault = ""
fp_sNoRecords = "No Records Returned"
fp_iMaxRecords = 0
fp_iTimeout = 0
fp_iCurrent = 1
fp_fError = False
fp_bBlankField = False
If fp_iTimeout <> 0 Then Server.ScriptTimeout = fp_iTimeout
Do While (Not fp_fError) And (InStr(fp_iCurrent, fp_sQry, "%%") <> 0)
      ' found a opening quote, find the close quote
      fp_iStart = InStr(fp_iCurrent, fp_sQry, "%%")
      fp_iEnd = InStr(fp_iStart + 2, fp_sQry, "%%")
      If fp_iEnd = 0 Then
            fp_fError = True
            Response.Write "<B>Database Region Error: mismatched parameter delimiters</B>"
      Else
            fp_sField = Mid(fp_sQry, fp_iStart + 2, fp_iEnd - fp_iStart - 2)
            If Mid(fp_sField,1,1) = "%" Then
                  fp_sWildcard = "%"
                  fp_sField = Mid(fp_sField, 2)
            Else
                  fp_sWildCard = ""
            End If
            fp_sValue = Request.Form(fp_sField)

            ' if the named form field doesn't exist, make a note of it
            If (len(fp_sValue) = 0) Then
                  fp_iCurrentField = 1
                  fp_bFoundField = False
                  Do While (InStr(fp_iCurrentField, fp_sDefault, fp_sField) <> 0) _
                        And Not fp_bFoundField
                        fp_iCurrentField = InStr(fp_iCurrentField, fp_sDefault, fp_sField)
                        fp_iStartField = InStr(fp_iCurrentField, fp_sDefault, "=")
                        If fp_iStartField = fp_iCurrentField + len(fp_sField) Then
                              fp_iEndField = InStr(fp_iCurrentField, fp_sDefault, "&")
                              If (fp_iEndField = 0) Then fp_iEndField = len(fp_sDefault) + 1
                              fp_sValue = Mid(fp_sDefault, fp_iStartField+1, fp_iEndField-1)
                              fp_bFoundField = True
                        Else
                              fp_iCurrentField = fp_iCurrentField + len(fp_sField) - 1
                        End If
                  Loop
            End If

            ' this next finds the named form field value, and substitutes in
            ' doubled single-quotes for all single quotes in the literal value
            ' so that SQL doesn't get confused by seeing unpaired single-quotes
            If (Mid(fp_sQry, fp_iStart - 1, 1) = """") Then
                  fp_sValue = Replace(fp_sValue, """", """""")
            ElseIf (Mid(fp_sQry, fp_iStart - 1, 1) = "'") Then
                  fp_sValue = Replace(fp_sValue, "'", "''")
            ElseIf Not IsNumeric(fp_sValue) Then
                  fp_sValue = ""
            End If

            If (len(fp_sValue) = 0) Then fp_bBlankField = True

            fp_sQry = Left(fp_sQry, fp_iStart - 1) + fp_sWildCard + fp_sValue + _
                  Right(fp_sQry, Len(fp_sQry) - fp_iEnd - 1)

            ' Fixup the new current position to be after the substituted value
            fp_iCurrent = fp_iStart + Len(fp_sValue) + Len(fp_sWildCard)
      End If
Loop

If Not fp_fError Then
      ' Use the connection string directly as entered from the wizard
      On Error Resume Next
      set fp_rs = CreateObject("ADODB.Recordset")
      If fp_iMaxRecords <> 0 Then fp_rs.MaxRecords = fp_iMaxRecords
      fp_rs.Open fp_sQry, "DSN=library"
      If Err.Description <> "" Then
            Response.Write "<B>Database Error: " + Err.Description + "</B>"
            if fp_bBlankField Then
                  Response.Write " One or more form fields were empty."
            End If
      Else
            ' Check for the no-record case
            If fp_rs.EOF And fp_rs.BOF Then
                  Response.Write fp_sNoRecords
            Else
                  ' Start a while loop to fetch each record in the result set
                  Do Until fp_rs.EOF
%>


<tr>
<td><font color="#800080"><strong><%
If Not IsEmpty(fp_rs) And Not (fp_rs Is Nothing) Then Response.Write CStr(fp_rs("Keywords"))
%>

<%
If Not IsEmpty(fp_rs) And Not (fp_rs Is Nothing) Then Response.Write CStr(fp_rs("Date"))
%>

 </strong></font></td>
<td><font color="#800080"><strong><%
If Not IsEmpty(fp_rs) And Not (fp_rs Is Nothing) Then Response.Write CStr(fp_rs("Authors"))
%>
 </strong></font></td>
</tr>
<%
                        ' Close the loop iterating records
                        fp_rs.MoveNext
                  Loop
            End If
            fp_rs.Close
      ' Close the If condition checking for a connection error
      End If
' Close the If condition checking for a parse error when replacing form field params
End If
set fp_rs = Nothing
%>


</table>

<p> </p>

<p> </p>

<p> </p>

<p align="center"><a href="../searchform.asp" style="text-decoration: none; color: rgb(0,0,0)"><img src="../images/img0010.gif" alt="img0010.gif (94 bytes)" border="0" WIDTH="7" HEIGHT="11"><small><strong>Back</a> To Forms</strong></small></p>
</body>
</html>

englishman

ASKER

Edited text of question

sybe

Replace this

fp_sQry = "SELECT Keywords, Authors, Date FROM library WHERE Keywords = '%%Keywords%%' OR Authors= '%%Authors%%'"

with

fp_sQry = "SELECT Keywords, Authors, Date FROM library WHERE Keywords LIKE '%" & Request.Form("Keywords") & "%' OR Authors LIKE '%" & Request.Form("Authors") & "%'"

englishman

ASKER

Cheers, but,
this is the errormessage I get:

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/libasp/Scripts/mainlibsearch.asp, line 9

fp_sQry = "SELECT Keywords, Authors, Date FROM library WHERE Keywords LIKE '%" &
Request.Form("Keywords") & "%' OR

englishman

ASKER

Development:
I took out a line break after the OR and got the following error message:

Database Region Error: mismatched parameter delimiters

sybe

hmmm,
Try this:

take the original statement and change the = into LIKE

fp_sQry = "SELECT Keywords, Authors, Date FROM library WHERE Keywords LIKE '%%Keywords%%' OR Authors LIKE '%%Authors%%'"

englishman

ASKER

Development:
I took out a line break after the OR and got the following error message:

Database Region Error: mismatched parameter delimiters

englishman

ASKER

Hi,
Just in case the messages order is getting screwed up:
I get the mismatched parameter error after your first suggestion, your second suggestion works exactly the same as my original.

Any more ideas - I live in hope,
cheers,
NM

englishman

ASKER

The answer is:
SELECT Keywords, Authors, Date FROM library WHERE (library.Keywords like '%%%KeywordEntry%%%' AND library.Keywords like '%%%KeywordEntry1%%%')

ASKER CERTIFIED SOLUTION

linda101698

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial