connect database to ASP and get a chance to view it

gilgalbiblewhee · #1 (**permalink**) 07-15-04, 17:08

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.

rokslide · #2 (**permalink**) 07-15-04, 18:38

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??

gilgalbiblewhee · #3 (**permalink**) 07-15-04, 19:22

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?

rokslide · #4 (**permalink**) 07-15-04, 19:42

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

gilgalbiblewhee · #5 (**permalink**) 07-15-04, 19:57

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".

rokslide · #6 (**permalink**) 07-15-04, 20:14

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.....

gilgalbiblewhee · #7 (**permalink**) 07-15-04, 21:02

I see your point. There was one tutorial http://www.stardeveloper.com/article...0051001&page=1 where the writer illustrates

Quote:

An ASP Tutorial to create your own Database driven Search Engine

. I didn't understand it . It which requires 6 asp files:

Quote:

Creating 'addtodb.asp' page

Quote:

Creating 'addtodb_fso.asp' page

Quote:

Creating 'delfromdb.asp' page

Quote:

Creating 'search.htm' page :

Quote:

Creating 'search.asp' page

Quote:

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.

rokslide · #8 (**permalink**) 07-15-04, 21:19

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

gilgalbiblewhee · #9 (**permalink**) 07-16-04, 02:22

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

gilgalbiblewhee · #10 (**permalink**) 07-16-04, 15:55

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:

Quote:

id book spoke book_title chapter verse text_data

where the spaces are the separations (7 fields).

How should the search section be designed?

Seppuku · #11 (**permalink**) 07-17-04, 04:10

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.

gilgalbiblewhee · #12 (**permalink**) 07-17-04, 09:05

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

Quote:

Problem:
An unexpected error occured.

Solution:
Please restart the Publishing Wizard.

gilgalbiblewhee · #13 (**permalink**) 07-18-04, 13:36

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):

gilgalbiblewhee · #14 (**permalink**) 07-18-04, 13:45

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?

Seppuku · #15 (**permalink**) 07-18-04, 17:06

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech