Catalog of Fishes: <%= VisTableName %>

<% strContains=request.form("contains") Tbl=request.form("tbl") Select Case Tbl Case "Species" VisTableName = Tbl AtrList = "id, fmt, seq" OrderBy = "ORDER BY seq" Case "Genus" VisTableName = "Genera" AtrList = "id, fmt" OrderBy = "ORDER BY seq" Case "Ref" VisTableName = "References" AtrList = "id, PubDate, txtAuth, fmtAuth, fmtRef" OrderBy = "ORDER BY txtAuth, PubDate" End Select %> Catalog of Fishes: <%= VisTableName %> <% Dim iRowCnt ' Process the user input (search pattern in strContains) ' if " and ", " or ", "(", or a double quote ["] was entered, then don't do anything ' else user might be naive; check to see if pattern contains a space after the first character ' replace all spaces with [ and ] as the logical operator strContains = Trim (strContains) if ( InStr(1, strContains, " and ", 1) + _ instr(1, strContains, " or ", 1) + _ instr(strContains, "(" ) + _ instr(strContains, """") > 0 ) then ' response.write("

user entered logical operators

" & vbcr) ElseIf instr(2, strContains, " ") > 0 then strContains = replace(strContains, " ", " and ") End If ' initialize the recordset object (need to set MaxRecords before query) set rsFishCat = Server.CreateObject("adodb.recordset") '************************************************************ '************* THE SELECT COMMAND *********************** sql_cmd = "SELECT " & AtrList & _ " FROM " & Tbl & _ " WHERE CONTAINS (*, ' " & strContains & " ') " & _ OrderBy '************************************************************ %>

SEARCH RESULTS FROM THE

Select the database to search:
> GENERA > SPECIES > REFERENCES

	Comments:

<% if (strContains <> "") then ' Response.Write "

Form parsed, sql constructed.

" & vbCRLF & vbCRLF response.write VisTableName & " that contain: " & strContains & " " MakeDBconnection dbCnxn,rsFishCat,"CoFWeb" rsFishCat.Open sql_cmd, dbCnxn, 3 ' 3 specifies a Static cursor for the recordset ' Response.Write "

record set opened by sql_cmd over connection.

" & vbCRLF & vbCRLF iRowCnt = rsFishCat.RecordCount Call LogHits ("CoF", (Tbl & ": " & strContains), iRowCnt) ' Response.Write "

Hits logged.

" & vbCRLF & vbCRLF %> [ <%=iRowCnt %> ] records

<% if Not Tbl = "Ref" Then Response.Write("

" & vbCR) DO UNTIL rsFishCat.eof Response.Write "

" & rsFishCat("fmt") & "

" & vbCRLF & vbCRLF rsFishCat.movenext LOOP Response.Write(vbCR) Else DO UNTIL rsFishCat.eof %>

<%= rsFishCat("fmtAuth") %> <%= rsFishCat("PubDate") %> [ref. <%= rsFishCat("ID") %>]: <%= rsFishCat("fmtRef") %>

<% rsFishCat.movenext LOOP End If ' explicitly close and dispose of recordset and connection objects rsFishCat.close set rsFishCat=nothing dbCnxn.close set dbCnxn=nothing else response.write "

Please enter a search term.

" end if %>