Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: connect database to ASP and get a chance to view it

    Hi. I'm presently reading http://www.w3schools.com as suggested.

    My question is how to build a search engine for a database. I know it's not definable in a couple of paragraphs. And people who reply shouldn't do other people's jobs.

    I've been looking in "SQL Tutorial", "ADO" and the "ASP Tutorial".

    I use MS Access 2000, PWS for WIN98. I'm a newbie.

    WHAT ARE the codes that I should be aware to build a search engine for a bible database? for making word searches?

    DO THE codes have to be pasted in separate ASP files or alltogether?

    I notice for example there are databases described to cause people to input information.

    Mine is simply searching.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    A search engine for a database.... wow.. okie...

    Basically all you need to be concerned about is executing select queries so that you can find ans return data. This is really the least of your worries, select queries are extremely simple (eg. select versetext from verse where versetext like "%Moses%" - searches all the verses in the bible for Moses).

    The real problem is your database structure and how you want to set up your searching so that the performance will meet your needs.

    You suggested a bible database so we will look at that. The bible is broken up into books, chapters and verses which sets things up quite nicely for your initial database structure. Once the inital structure is complete you could do some testing and see if you need to change things to improve performance. A change that could be implemented would be creating a table of keywords and search on those.

    Does thast help at all??

  3. #3
    Join Date
    Jul 2004
    Posts
    494

    Bible database issue

    Thanks for your reply. I have a King James Bible database. That's all I will be using compared to other bible websites. but I will be focusing on the different types of searches. But for now what do I need for a simple search to a database?

    I have MS Access 2000. The Bible database was originally on MS Access 97. Instead of one table, I split in 66 tables, one for each book. And I don't know if it's required (probably so) but I've made 6 queries as well. 66 tables, because the original database was in 97. Converting it to 2000 causes data to go missing.

    So tables are 1 verse/ record. Fields are ID, Book name, Book number, Chapter, Verse, and Text data. 6 Fields.

    How will I be able to test it? I've uploaded the database to my site but I haven't cme across any tutorial that deals specifically on this. It's all taught in bits and pieces.

    I'm looking for answers http://www.w3schools.com has lists of things but I don't know how to put it together. WHat do I need so that I can extract the word search (is it called string query?) and diplay it on a page?

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Right so basically you want how to do a database query through a web page 101.... example, that won't work but will give you an idea... this code is set to submit back to itself and should be saved as SearchForm.asp

    Code:
    <%
    Dim myWord
    myWord = Request.Form("Word") 
    %>
    <form name="Search" action="SearchForm.asp" method="POST">
      <input type="text" name="Word" id="Word" value="">
      <input type="submit" value="Search">
    </form>
    <%
    if myWord <> "" then
      Dim Conn, mySql, VerseRS
      Set Conn=Server.CreateObject("ADODB.Connection")
      Conn.Open "myConnectionString"
      mySql = "Select * from myTable where Text like ='*" & myWord & "*'"
      Set VerseRS = Conn.Execute mySql
      Do while not VerseRS.eof
        Response.write VerseRS.Text & "<br><br>"
      Loop
      Set VerseRS = Nothing
      Set Conn = Nothing
    end if
    %>
    So that is basically it.... but you are going to have problems if you want to search more then one book at a time.....

    HTH

  5. #5
    Join Date
    Jul 2004
    Posts
    494

    Thanks. So I wonder....

    Thanks for the code. So what need to do is paste it on a txt file and save it as .asp . That I understand but how can I display it? There must be something missing. Because when I click on your code (since i posted on my website), it shows The "page cannot be displayed".

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    The code is an example. It will not actually work. There is no way for me to write your code with the information given. What it allow you to do is understand how the stuff you are reading at w3schools all gets put together.

    Specific things you will need to change include myConnectionString (so that it points to your database), mySql (so that it references your tables).

    Also, I assume you have set up a web server on your computer that can execute asp.....

  7. #7
    Join Date
    Jul 2004
    Posts
    494

    Ok I understand

    I see your point. There was one tutorial http://www.stardeveloper.com/article...0051001&page=1 where the writer illustrates
    An ASP Tutorial to create your own Database driven Search Engine
    . I didn't understand it . It which requires 6 asp files:
    Creating 'addtodb.asp' page
    Creating 'addtodb_fso.asp' page
    Creating 'delfromdb.asp' page
    Creating 'search.htm' page :
    Creating 'search.asp' page
    Creating 'editme.asp' Include File
    Are these all for one web page? Does this apply to me? (The reason I ask a stupid question like this is because the article starts with adding and deleting files, which I thought it didn't apply to me).I have PWS but at times I don't understand how to view it.

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    looking at what you have give the two search files would be the only ones you need. You could probably skip through the tutorial to that part. I suspect the first bits are to show how to set up at data base.

    With the code I gave you it is all one page. How you set it up in the long range (eg. how many pages you use) is really up to you to decide once you understand more about how things work.

    PWS will set your machine up as a webserver (for a single site).

    To view a page on the website you have to make sure it is in the right directory (default is C:\inetpub\www\) and then you can use the url of http://localhost/pagename.asp to view/execute the page (pagename refers to whatever you saved the page as).

    HTH

  9. #9
    Join Date
    Jul 2004
    Posts
    494

    things coming together thanks

    thanks. I was having difficulty figuring out the purpose of PWS but it's working.

  10. #10
    Join Date
    Jul 2004
    Posts
    494

    rokslide another question here are records sample for "search"

    I was looking everywhere but didn't find (maybe I wasn't looking the right places) tutorial on SEARCH.

    Let's say I have a table called "acts". Here are the fields:

    id book spoke book_title chapter verse text_data
    where the spaces are the separations (7 fields).

    How should the search section be designed?

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    It depends.. what do you want to allow your users to search by? Book, chapter, verse, all?

    Using meta-code, your search might function like this:
    Code:
    Page1 (biblesearch.asp)
      Form (Search) - action: searchresults.asp, method: post
        Input - Textbox (query)
        Select (criteria)
          Book
          Chapter
          Verse
          ...
        Submit
    
    Page2 (searchresults.asp)
      Read form values into variables (query = Request.Form("query"))
      Open a connection to your DB using ADO, and a connection string to Access
      Execute some SQL based upon your query and criteria (SELECT * FROM acts WHERE " & criteria & " LIKE '%" & query & "'"
      Check the result set for values, if rows exist, write them to the browser
      Close connection to DB
    To do the connection to the DB, look up ADO Connection, ADO Command, and ADO Recordset. The Command object is optional, but most of my apps use it. Your ASP creates a Connection object, then opens it, optionally connects it to a Command object, then executes some SQL or a Stored Procedure through the Connection object to return a RecordSet object which has the results of your query.
    That which does not kill me postpones the inevitable.

  12. #12
    Join Date
    Jul 2004
    Posts
    494

    why is my PWS not working?

    I would like to test it but my PWS was freezing or incapable of publishing these last couple of days.

    Problem:
    An unexpected error occured.

    Solution:
    Please restart the Publishing Wizard.


  13. #13
    Join Date
    Jul 2004
    Posts
    494

    search form + 6 checkboxes retreiving db records

    The goal for this html is to retreive 7 checkbox-ed (7 fields) to display their records. If I have a choice between "post" and "get" methods I prefer "get".

    HTML Code:
    <tr>
    <form action="DaBibleEngine.asp" method="get">
    Search for:
    <input type="text" name="mySearch">
    <input type="submit" value="Submit"><input type="reset">
    </td>
    <br>
    <input type="checkbox" name="book" VALUE="yes">
    Book
    <br>
    <input type="checkbox" name="spoke" VALUE="yes">
    Spoke
    <br>
    <input type="checkbox" name="book_title" VALUE="yes">
    Book Title
    <br>
    <input type="checkbox" name="chapter" VALUE="yes">
    Chapter
    <br>
    <input type="checkbox" name="verse" VALUE="yes">
    Verse
    <br>
    <input type="checkbox" name="text_data" VALUE="yes">
    Text
     </tr>
    </form>
    Next is the response page (ASP):

  14. #14
    Join Date
    Jul 2004
    Posts
    494

    response page (for HTML, ASP, Database)+question

    HTML Code:
     <%@ LANGUAGE="VBSCRIPT" %>
    <html>
    <head>
    <TITLE>DaBibleEngine.asp</TITLE>
    </head>
    
    <body>
    <%
    
    Dim SqlBible
    
    
    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Open("bible")
    
    SqlBible = "SELECT * FROM acts"
    
    <%
    dim mySearch
    mySearch=Request.QueryString("mySearch")
    If   request.QueryString("book")="yes" then
    	SqlBible = SqlBible & " WHERE book LIKE '%" & Request.QueryString("mySearch") & "%'"
         response.Write("Results " & mySearch & "!<br />")
    end if
    If   request.QueryString("spoke")="yes" then
    	SqlBible = SqlBible & " WHERE spoke LIKE '%" & _
         response.Write("Results " & mySearch & "!<br />")
    end if
    If   request.QueryString("book_title")="yes" then
    	SqlBible = SqlBible & " WHERE book_title LIKE '%" & _
         response.Write("Results " & mySearch & "!<br />")
    end if
    If   request.QueryString("chapter")="yes" then
    	SqlBible = SqlBible & " WHERE verse LIKE '%" & _
         response.Write("Results " & mySearch & "!<br />")
    end if
    If   request.QueryString("verse")="yes" then
    	SqlBible = SqlBible & " WHERE verse LIKE '%" & _
         response.Write("Results " & mySearch & "!<br />")
    end if
    If   request.QueryString("text_data")="yes" then
    	SqlBible = SqlBible & " WHERE text_data LIKE '%" & Request.Form("mySearch") & "%'"
         response.Write("Results " & mySearch & "!<br />")
    Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
    rsGlobalWeb.Open SqlBible, dbGlobalWeb, 3
    end if%>
    
    <%
    If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>
    
    <h2 align="center">We did not find a match!</h2>
    <%Else%>
    
    
    <%If Not rsGlobalWeb.BOF Then%>
    
    <h2>These are the results:</h2>
    
    <table BORDER="0" width="100%" cellpadding="3">
      <tr>
        <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book </font></th>
        <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Spoke </font></th>
        <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book Title </font></th>
        <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Chapter </font></th>
        <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Text </font></th>
      </tr>
    <%
    	Do While Not rsGlobalWeb.EOF
    	%>
      <tr>
        	<td><%=rsGlobalWeb("book")%>&#32 
    </td>
    	<td><%=rsGlobalWeb("spoke")%>
    </td>
        	<td><%=rsGlobalWeb("book_title")%>
    </td>
        	<td><%=rsGlobalWeb("chapter")%>
    </td>
        	<td><%=rsGlobalWeb("verse")%>
    </td>
        	<td><%=rsGlobalWeb("text_data")%>
    </td>
      </tr>
    <% rsGlobalWeb.MoveNext
    	Loop
    	%>
    </table>
    <%End If%>
    <%End If%>
    <%
    rsGlobalWeb.Close
    dbGlobalWeb.Close
    %>
    </body>
    </html>
    I don't understand where go wrong. The question is how to select a multiple selection (6 representing the 6 fields in database included for searching, id left out). For clarification the database is "bible" but the table is one out of 66 tables in db called "acts".

    The fields are:
    id book spoke book_title chapter verse text_data

    Anything else needed to clarify?

  15. #15
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    If I have a choice between "post" and "get" methods I prefer "get".
    Why? What's wrong with POSTing?

    You're having trouble because your code that builds the SQL repeats the WHERE clause...

    Code:
    SqlBible = "SELECT * FROM acts"
    
    <%
    dim mySearch
    mySearch=Request.QueryString("mySearch")
    If   request.QueryString("book")="yes" then
    	SqlBible = SqlBible & " WHERE book LIKE '%" & Request.QueryString("mySearch") & "%'"
         response.Write("Results " & mySearch & "!<br />")
    end if
    If   request.QueryString("spoke")="yes" then
    	SqlBible = SqlBible & " WHERE spoke LIKE '%" & _
         response.Write("Results " & mySearch & "!<br />")
    end if
    Notice how you have your WHERE clause twice? And where is "mySearch" in the second If statement? It looks like you've got a Response.Write in a variable asignment. First, when you're building the statement, you can only have one WHERE clause, second, you need some "OR" statements between each criteria check. So what you should do is have a counter. When the counter is greater than 0, you'd have to add an OR statement.

    Code:
    SqlBible = "SELECT * FROM acts WHERE"
    
    <%
    dim mySearch, iCounter
    mySearch=Request.QueryString("mySearch")
    iCounter = 0
    
    If   request.QueryString("book")="yes" then
      SqlBible = SqlBible & "book LIKE '%" & mySearch & "%'"
      iCounter = iCounter + 1
    end if
    If   request.QueryString("spoke")="yes" then
      If iCounter > 0 Then
        SqlBible = SqlBible & " OR "
      End If
      SqlBible = SqlBible & "spokebLIKE '%" & mySearch & "%'"
      iCounter = iCounter + 1
    end if
    Wash, rinse, and repeat each IF (exluding the first) for each column you want to include.
    That which does not kill me postpones the inevitable.

Posting Permissions

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