Need help searching a Access database

Results 1 to 5 of 5

Thread: Need help searching a Access database

  1. #1
    Join Date
    Dec 1969
    Posts
    13

    Default Need help searching a Access database

    I am a beginner at ASP&#039s and I am having a hell&#039va time modifying the tutorials and script examples I have seen on the Internet.<BR><BR>I have a Microsoft Access 97 database that has 1 table, 15000 records, and 8 fields. The field names are: 1)Key (Autonumber - Primary Key) 2)Authors 3)Title 4)YearPublished 5)DocumentType 6)Publisher 7)Have 8)PagesInBook. The format for inputting Authors names into the Authors field in the database is as follows: (King,Stephan and Donald Duck) - the first author is listed as last name, comma, and first name - if there are other authors, they are listed as first name, last name. <BR><BR>I am trying to create a html webpage that will query the database (search on an authors name - ex. Mickey Mouse) and return the results. I want to display 10 records per page (records are displayed in an html table), with 3 fields displayed (Authors, Title and Have - ex. Mouse, Mickey Where is Goofy? Hardcover in a 3 column table). I would then like to be able to click on the Authors name (ex. Mouse, Mickey) in the displayed results which would display all the information pertaining to the record (the 8 fields - another ASP page???). <BR><BR>Using several examples on the web I have been able to do most of the above. The big problem I am having is to do it at the same time. Using Jerry Wood&#039s "A Nifty Table Searching Script", I can query my database, bringing back only the records that I want (retrieves all records with Mickey and Mouse in the Authors field). I cannot figure out how to modify the script though. I want to be able to add "paging through database results 10 records at a time" but have had absolutely no luck. I also cannot figure out how to display 3 fields instead of all fields for the record. All attempts at modifying script has rendered it useless. <BR><BR>If any of this makes sense, could someone please point me in the right direction.<BR><BR>Thanks<BR>Jerry LeBlanc

  2. #2
    Chris Willey Guest

    Default RE: Need help searching a Access database

    Hmmm.... (don&#039t you love it when a response begins like that?)<BR><BR>This is a little tricky because I&#039m not familiar with Jerry Woods or his "A Nifty Table Searching Script", so any advice I might give you may be even more confusing. This is because it&#039s likely that Jerry and I have different styles. If I could see some of the code you&#039re using, it would be easier.<BR><BR>Now that you&#039ve given up all hope for an easy answer, I will say this. Choosing to display all or some fields of a record on a page is handled precisely the same way in each case. In other words, you ultimately decide which fields to display, there&#039s no magic "display all fields" command in ASP. At some point, you&#039re asking the server to show each field. So what you need to do (and what I might be able to help you with if I see the code) is determine how the script you&#039re trying to modify is displaying the fields. Typically, this is done in a couple of ways:<BR><BR>Response.Write(rs("fieldname"))<BR>-or-<BR>&#060;%=rs("fieldname")&#037;&#062;<BR><BR>I&# 039ve also seen some scripts loop through fields in a record using an array. Anyway, the trick here is to remove references to fields you don&#039t want to display. So if you don&#039t want people to see the information in "Publisher", for example, remove any references to that field that you see using one or the other of the above methods of displaying field information.<BR><BR>As for paging through results, I could probably show you half a dozen ways of doing that, but by seeing the code I could show you the way that best fits what you already have. As a general rule of thumb, you create a paging system by keeping track of:<BR><BR>a) how many records you have displayed already on the current page<BR>b) what record number you last displayed on the previous page (and when I say record number, I mean result number, not your "Key" or any other Autonumber field)<BR>c) whether you&#039re on the first or last page or somewhere in between (something of a function of a and b)<BR><BR>You can also keep track of how many pages your users will have to go through to get to the end of your recordset. This is handy for creating a set of numbered links to jump ahead more than just one page. You can get really sophisticated with it, or not, depending on your preference.<BR><BR>So show us the code. If it&#039s really long, email it to me (cwilley@enterprisefoundation.org) rather than printing it all out here.

  3. #3
    Join Date
    Dec 1969
    Posts
    13

    Default RE: Need help searching a Access database

    Thanks for the response Chris. I have two pages - test.htm and search.asp. The records that are retrieved are exactly what I want. It does return an error if the author has an apostrophe in his name, but I figured I would try to fix that one later.<BR><BR>Search.htm<BR>&#060;html><BR>&#060;h ead>&#060;title>&#060;/title><BR>&#060;/head><BR>&#060;body>&#060;FORM METHOD=POST ACTION="search.asp"><BR> <BR> &#060;INPUT TYPE=HIDDEN NAME="SType" VALUE="AllWords"><BR> &#060;INPUT TYPE=HIDDEN NAME="Column" VALUE="Authors"><BR> &#060;INPUT TYPE=HIDDEN NAME="Table" VALUE="baseballbooks"><BR> &#060;INPUT TYPE=HIDDEN NAME="Field" VALUE="Key"><BR> Enter the Authors name:<BR> &#060;INPUT TYPE=TEXT NAME="SValue" SIZE=30><BR> &#060;P><BR> &#060;INPUT TYPE=SUBMIT><BR>&#060;/FORM><BR>&#060;/body><BR>&#060;/html><BR><BR><BR>Search.asp<BR>&#060;HTML><BR>&#06 0;BODY><BR>&#060;%<BR>&#039 Pass in the table to search, and the field to search,<BR>&#039 and, optionally, the column to sort by<BR>strFieldName = Request("Column") &#039the Authors field<BR>strTableName = Request("Table") &#039the table baseballbooks<BR>strOrderByField = Request("Field") &#039the field to order by - Key<BR>&#039 SValue = Seach String From Form / QueryString<BR>&#039 SType = Search Type From Form / QueryString<BR>SValue = Request("SValue") &#039the Authors name<BR>SType = Request("SType") &#039used for an AllWord search, this script was originally created to handled AllWords or Any Words<BR><BR>&#039 For Loop To Move Through Search String, Counting Spaces, and<BR>&#039 Incrementing WordCounter By One At Each Occurance<BR>For i = 1 to len(SValue)<BR> If mid(SValue, i, 1) = " " Then <BR> WordCounter = WordCounter + 1<BR> End If<BR>Next<BR><BR>&#039 Now, We Add One To Include The First Word<BR>WordCounter = WordCounter + 1<BR><BR>&#039 Next We Dim Word As An Array, With The Maximum Number Of Words<BR>&#039 To Allow - In This Case, 100<BR>Dim Word(100)<BR><BR>&#039 Now, We Fill The Array With The Words<BR>CurrentWord = 1<BR>For i = 1 to len(SValue)<BR> If mid(SValue, i, 1) = " " Then<BR> CurrentWord = CurrentWord + 1<BR> Else<BR> Word(CurrentWord) = Word(CurrentWord) + mid(SValue, i, 1)<BR> End If<BR>Next<BR><BR>&#039 Now Lets Build The SQL Statement Based On What Search Type <BR>&#039(SType)Was Selected<BR><BR>&#039First Part Of SQL<BR>SQL = "SELECT * FROM baseballbooks WHERE "<BR><BR>&#039For Loop To Concatenate SQL String Together<BR> For i = 1 to WordCounter<BR> If SType = "AllWords" Then<BR> If i &#060;> WordCounter Then<BR> SQL1 = SQL1 & strFieldName & " LIKE &#039%" & Word(i) & "%&#039 AND "<BR> ElseIf i = WordCounter Then<BR> SQL1 = SQL1 & strFieldName & " LIKE &#039%" & Word(i)& "%&#039"<BR> End If<BR> End If<BR> Next<BR> <BR>&#039Finishing Part Of SQL Statement.<BR>if Len(strOrderByField) > 0 then<BR>&#039We need to perform an ORDER BY!<BR>SQL = SQL & SQL1 & " ORDER BY " & strOrderByField<BR>Else<BR>SQL = SQL & SQL1<BR>End If<BR>&#037;&#062;<BR><BR>&#060;%<BR>&#039Connect to our database<BR>Set dbConn = Server.CreateObject("ADODB.Connection")<BR>dbConn. Open "DSN=books"<BR><BR>&#039Create an explicit recordset object...<BR>Set rsRecords = Server.CreateObject("ADODB.Recordset")<BR>rsRecord s.Open SQL, dbConn, 3<BR>&#037;&#062;<BR><BR>&#060;%<BR>If Not rsRecords.EOF And Not rsRecords.BOF Then<BR>&#039We have at least one record, so display it...<BR>&#037;&#062;<BR><BR>&#060;TABLE BORDER=1><BR>&#060;TR><BR>&#060;% For i = 0 to rsRecords.Fields.Count-1 &#037;&#062;<BR>&#060;td valign=top>&#060;b>&#060;%=rsRecords.Fields(i).Nam e&#037;&#062;&#060;/b>&#060;/td><BR>&#060;% Next &#037;&#062;<BR>&#060;/TR><BR>&#060;% Do While Not rsRecords.EOF &#037;&#062;<BR>&#060;TR><BR>&#060;% For i = 0 to rsRecords.Fields.Count-1<BR> thisvalue=rsRecords(i)<BR> If isnull(thisvalue) then<BR> thisvalue="&nbsp;"<BR>end if&#037;&#062;<BR>&#060;TD Valign=top>&#060;%=thisvalue&#037;&#062;&#060;/td><BR>&#060;% Next &#037;&#062;<BR>&#060;/TR><BR>&#060;%rsRecords.MoveNext&#037;&#062;<BR>&# 060;% Loop &#037;&#062;<BR>&#060;/TABLE><BR>&#060;% <BR>Else<BR>&#039The Search was futile, no records returned.<BR>Response.Write("&#060;FONT SIZE=5>&#060;B>Unable to find an entry&#060;/B>")<BR>Response.Write("&#060;/FONT>&#060;/CENTER>")<BR>End If<BR>&#037;&#062;<BR><BR>&#060;/BODY><BR>&#060;/HTML>

  4. #4
    Chris Willey Guest

    Default RE: Need help searching a Access database

    OK, this is pretty straightforward. The script is using a for...next loop to lay out the fields in the database (that begins with the line that reads "&#060;% For i = 0 to rsRecords.Fields.Count-1 &#037;&#062;" -- the second one). So you need to substitute this code block:<BR><BR>&#060;TR><BR>&#060;% For i = 0 to rsRecords.Fields.Count-1<BR>thisvalue=rsRecords(i)<BR>If isnull(thisvalue) then thisvalue=" "<BR>end if&#037;&#062;<BR>&#060;TD Valign=top>&#060;%=thisvalue&#037;&#062;&#060;/td><BR>&#060;% Next &#037;&#062;<BR>&#060;/TR><BR><BR>with something like this:<BR><BR>&#060;TR><BR>&#060;% If rsRecords("fieldname1") &#060;> "" Then &#037;&#062;<BR>&#060;TD Valign=top>&#060;%=rsRecords("fieldname1")&#037;&# 062;&#060;/td><BR>&#060;% End If &#037;&#062;<BR>&#060;/TR><BR>&#060;TR><BR>&#060;% If rsRecords("fieldname2") &#060;> "" Then &#037;&#062;<BR>&#060;TD Valign=top>&#060;%=rsRecords("fieldname2")&#037;&# 062;&#060;/td><BR>&#060;% End If &#037;&#062;<BR>&#060;/TR><BR><BR>Repeat as often as necessary.<BR><BR>As for paging through records, I&#039ll have to submit another posting at a later time on how to do that (about to run out for a while). I&#039ll post sometime this evening.


  5. #5
    Join Date
    Dec 1969
    Posts
    13

    Default RE: Need help searching a Access database

    Thank you, Chris. The coding works perfectly. Made a couple small changes so that it reads as follows:<BR><BR>&#060;%<BR>If Not rsRecords.EOF And Not rsRecords.BOF Then<BR>&#039We have at least one record, so display it...<BR>&#037;&#062;<BR><BR>&#060;TABLE BORDER=1><BR>&#060;tr>&#060;th>Key&#060;/th>&#060;th>Authors&#060;/th>&#060;th>Titles&#060;/th>&#060;th>Have&#060;/th>&#060;/tr><BR><BR>&#060;%<BR>Do While Not rsRecords.EOF<BR>&#037;&#062;<BR><BR>&#060;TR><BR> &#060;%If rsRecords("Key")&#060;>"" Then&#037;&#062;<BR>&#060;td>&#060;font size=2>&#060;%=rsRecords("Key")&#037;&#062;&#060;/font>&#060;/td><BR>&#060;%End If&#037;&#062;<BR> <BR>&#060;%If rsRecords("Authors")&#060;>"" Then&#037;&#062;<BR>&#060;td>&#060;font size=2>&#060;%=rsRecords("Authors")&#037;&#062;&#0 60;/font>&#060;/td><BR>&#060;%End If&#037;&#062;<BR> <BR>&#060;%If rsRecords("Title")&#060;>"" Then&#037;&#062;<BR>&#060;td>&#060;font size=2>&#060;%=rsRecords("Title")&#037;&#062;&#060 ;/font>&#060;/td><BR>&#060;%End If&#037;&#062;<BR> <BR>&#060;%If rsRecords("Have")&#060;>"" Then&#037;&#062;<BR>&#060;td>&#060;font size=2>&#060;%=rsRecords("Have")&#037;&#062;&#060;/font>&#060;/td><BR>&#060;%End If&#037;&#062;<BR>&#060;/tr><BR>&#060;%rsRecords.MoveNext&#037;&#062;<BR>&# 060;%<BR>Loop<BR>&#037;&#062;<BR>&#060;/TABLE>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •