Solved

Simple ASP interrogation of an ODBC database

Posted on 1998-07-13
9
259 Views
Last Modified: 2013-12-25
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
%>
<!--webbot bot="DatabaseRegionStart" i-checksum="55572" endspan -->

  <tr>
    <td><font color="#800080"><strong><!--webbot bot="DatabaseResultColumn" startspan s-columnnames="Keywords,Authors,Date" s-column="Keywords" b-tableformat="TRUE" clientside local_preview="Database: Keywords" preview="Database: Keywords" --><%
If Not IsEmpty(fp_rs) And Not (fp_rs Is Nothing) Then Response.Write CStr(fp_rs("Keywords"))
%>

<!--webbot bot="DatabaseResultColumn" startspan s-columnnames="Keywords,Authors,Date" s-column="Date" b-tableformat="TRUE" clientside local_preview="Database: Date" preview="Database: Date" --><%
If Not IsEmpty(fp_rs) And Not (fp_rs Is Nothing) Then Response.Write CStr(fp_rs("Date"))
%>

<!--webbot bot="DatabaseResultColumn" i-checksum="32411" endspan --> </strong></font></td>
    <td><font color="#800080"><strong><!--webbot bot="DatabaseResultColumn" startspan s-columnnames="Keywords,Authors,Date" s-column="Authors" b-tableformat="TRUE" clientside local_preview="Database: Authors" preview="Database: Authors" --><%
If Not IsEmpty(fp_rs) And Not (fp_rs Is Nothing) Then Response.Write CStr(fp_rs("Authors"))
%>
<!--webbot bot="DatabaseResultColumn" i-checksum="29199" endspan --> </strong></font></td>
  </tr>
  <!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="TRUE" local_preview preview clientside tag="BODY" --><%
                        ' 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
%>
<!--webbot bot="DatabaseRegionEnd" i-checksum="55813" endspan -->

</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>
0
Comment
Question by:englishman
  • 6
  • 2
9 Comments
 
LVL 2

Author Comment

by:englishman
ID: 1858541
Edited text of question
0
 
LVL 28

Expert Comment

by:sybe
ID: 1858542
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") & "%'"



0
 
LVL 2

Author Comment

by:englishman
ID: 1858543
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
0
 
LVL 2

Author Comment

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

Database Region Error: mismatched parameter delimiters
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 28

Expert Comment

by:sybe
ID: 1858545
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%%'"


0
 
LVL 2

Author Comment

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

Database Region Error: mismatched parameter delimiters
0
 
LVL 2

Author Comment

by:englishman
ID: 1858547
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
0
 
LVL 2

Author Comment

by:englishman
ID: 1858548
The answer is:
SELECT Keywords, Authors, Date FROM library WHERE (library.Keywords like  '%%%KeywordEntry%%%' AND library.Keywords   like  '%%%KeywordEntry1%%%')
0
 
LVL 7

Accepted Solution

by:
linda101698 earned 120 total points
ID: 1858549
I'm posting the solution found by englishman so this question can be saved in the previously asked questions.

Linda Gardner
Customer Service @ Experts Exchange

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now