select from 66 tables using checkboxes - how

gilgalbiblewheel · August 8th, 2004, 11:40 AM

Ok thanks. I'll check later. In the meantime I wanted to say that the WHERE statement had a problem.

SQL = "SELECT * FROM bible WHERE text_data LIKE '%" & Replace(Keyword, "'", "''") & "%'"

I had changed "text_data" to try to include other fields too but it didn't word. Should ther be an AND or OR in between WHERE and LIKE?

happygv · August 8th, 2004, 07:57 PM

Yes, that should be looked into once you get the page displayed as expected. I am aware of the changes that need to be done to your SELECT statement. But I didn't want to confuse you or make you lazy by posting the entire code and this is again easy to learn how things work, when approached step by step.

If you can go ahead and change the SELECT statement, please do. The WHERE clause should have OR conditions checking for every COLUMN (among those 8 checkboxes) that is CHECKED on the page, and if you face any problem with that, feel free to post here. We will get that work for you. You can refer to my previous post that refers to the OTHER PAGE code for help on retrieving and splitting values for usage from checkbox.

WHERE should look like...

Code:

Select.....
WHERE <CHECKEDCOLUMN1> LIKE '%" & Replace(Keyword, "'", "''") & "%'" 
    OR <CHECKEDCOLUMN2> LIKE '%" & Replace(Keyword, "'", "''") & "%'" ... ...

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 8th, 2004, 09:19 PM

I inserted the codes. It didn't work yet. Maybe there are some irrelevant stuff or something missing.

The Radio buttons might be wrong.

Code:

<% @Language=VBScript%>

<%Option Explicit

'************************************************************************************

'* Declaration section
'************************************************************************************
' Mode contstants
Const MODE_DEFAULT     = 1
Const MODE_RESULTS     = 2

Const DB_NAME        = "kjv.mdb"    ' Name of our database file
Const SCRIPT_NAME    = "bible3.asp"    ' Name of this script

Const RECORDS_PER_PAGE     = 20            ' Number of records per page

Dim nMode    ' Current Mode
Dim kjvsearch
kjvsearch=Request.QueryString("kjvsearch")

'************************************************************************************
'* End of Declaration section
'************************************************************************************

'************************************************************************************
'* Main section
'************************************************************************************

' Find out what mode we are in
nMode = CLng(Request.QueryString("Mode"))

' Depending on our mode we will do different things
Select Case nMode

    Case MODE_RESULTS
        ' This is where all the results will show        
        ShowResults

    Case Else     ' This one is for MODE_DEFAULT or invalid modes all the same
        ' By default display the search form
        ShowSearchForm    
End Select

'************************************************************************************
'* End of Main section
'************************************************************************************

'************************************************************************************
'* Functions section
'************************************************************************************

' This function will generate our connection string
' it assumes that Access database is in the same folder as this script
Private Function GetConnectionString()
    GetConnectionString =     "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
                "UID=;PWD=;"
End Function

' Shows HTML page header 
Public Function OutputPageHeader()
    %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft FrontPage 3.0">
<title>Search for the Bible</title>
    <H2>Search the wheel of God</H2>
    <H3><A HREF="bible3.asp">Back Home</A></H3>
</head>

<body>

<fieldset>
<legend>
Biblewheel:
</legend>

    <%
End Function

' Shows HTML page footer 
Public Function OutputPageFooter()
    %>
    </BODY>
    </HTML>
    <%
End Function

' This function will display the search form
Private Function ShowSearchForm()
    OutputPageHeader
    %>
    <!--
        This form will direct user to itself with MODE_RESULTS mode
    -->
<tr>
<FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
Search for: <INPUT TYPE="text" NAME="keyword" VALUE=""> <INPUT TYPE="submit" VALUE=" Search ">
<INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>">
<input type="reset">
</td>
<br>
<INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Book">Book<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Book Spoke">Book Spoke<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Book Title">Book Title<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Chapter">Chapter<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Chapter Spoke">Chapter Spoke<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Verse">Verse<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Verse Spoke">Verse Spoke<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Text">Text<br>
 </tr>

 <!-- 
    Code here .... for Radio Button and Javascript function to disable/enable the following checkboxes.

<SCRIPT LANGUAGE=Javascript>

  function disable(OnOff) {
  var f = kjbible;
 
    f.gn.disabled=OnOff;
    f.is.disabled=OnOff;
    f.ro.disabled=OnOff;

    f.ex.disabled=OnOff;
    f.je.disabled=OnOff;
    f.co.disabled=OnOff;

    f.le.disabled=OnOff;
    f.la.disabled=OnOff;
    f.ci.disabled=OnOff;

    f.nu.disabled=OnOff;
    f.ez.disabled=OnOff;
    f.ga.disabled=OnOff;

    f.de.disabled=OnOff;
    f.da.disabled=OnOff;
    f.ep.disabled=OnOff;

    f.js.disabled=OnOff;
    f.ho.disabled=OnOff;
    f.ph.disabled=OnOff;

    f.jg.disabled=OnOff;
    f.jl.disabled=OnOff;
    f.cl.disabled=OnOff;

    f.ru.disabled=OnOff;
    f.am.disabled=OnOff;
    f.th.disabled=OnOff;

    f.sa.disabled=OnOff;
    f.ob.disabled=OnOff;
    f.te.disabled=OnOff;

    f.sm.disabled=OnOff;
    f.jh.disabled=OnOff;
    f.ti.disabled=OnOff;

    f.ki.disabled=OnOff;
    f.mi.disabled=OnOff;
    f.tm.disabled=OnOff;

    f.kn.disabled=OnOff;
    f.na.disabled=OnOff;
    f.tt.disabled=OnOff;

    f.ch.disabled=OnOff;
    f.hb.disabled=OnOff;
    f.pl.disabled=OnOff;

    f.cr.disabled=OnOff;
    f.ze.disabled=OnOff;
    f.he.disabled=OnOff;

    f.ea.disabled=OnOff;
    f.ha.disabled=OnOff;
    f.ja.disabled=OnOff;

    f.ne.disabled=OnOff;
    f.zc.disabled=OnOff;
    f.pe.disabled=OnOff;    

    f.es.disabled=OnOff;
    f.ml.disabled=OnOff;
    f.pt.disabled=OnOff;

    f.jb.disabled=OnOff;
    f.mt.disabled=OnOff;
    f.jn.disabled=OnOff;    

    f.ps.disabled=OnOff;
    f.mk.disabled=OnOff;
    f.jnn.disabled=OnOff;

    f.pr.disabled=OnOff;
    f.lk.disabled=OnOff;
    f.jhn.disabled=OnOff;

    f.ec.disabled=OnOff;
    f.jo.disabled=OnOff;
    f.ju.disabled=OnOff;

    f.so.disabled=OnOff;
    f.ac.disabled=OnOff;
    f.re.disabled=OnOff;

}
</SCRIPT>
    !-->



<p>

<fieldset>
<legend>
Search:
</legend>

<INPUT TYPE="Radio" VALUE='ALL' onClick=disable(true); name="Card" >
The entire King James Bible

<INPUT TYPE="Radio" VALUE='' onClick=disable(false); name="Card" CHECKED>
Select the book(s)
<p>

<p>Where do you want to search?</p> 

<table border="1" width="100%" height="500">
  <tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" CHECKED value="gn"> Genesis</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" CHECKED value="is"> Isaiah</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" CHECKED value="ro"> Romans</td>
  </tr>
  <tr>
    <td width="33%" height="19"> 
<input type="checkbox" name="recordType" value"ex"> Exodus</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" value="je">Jeremiah</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" value="co">1 Corinthians</td>
  </tr>
    <td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="lv">Leviticus</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="la">Lamentations</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ci">2 Corinthians</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="nu">Numbers</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ez">Ezekiel</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ga">Galatians</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="de">Deuteronomy</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="da">Daniel</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ep">Ephesians</td>
  </tr>
    <td width="33%" height="19"> 
<input type="checkbox" name="recordType" VALUE="js">Joshua</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ho">Hosea</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ph">Philippians</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="jg">Judges</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="jl">Joel</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="cl">Colossians</td>
  </tr>
    <td width="33%" height="19"> 
<input type="checkbox" name="recordType" VALUE="ru">Ruth</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="am">Amos</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="th">1 Thessalonians</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="sa">1 Samuel</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ob">Obadiah</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="te">2 Thessalonians</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="sa">2 Samuel</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="jh">Jonah</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ti">1 Timothy</td>
  </tr>
    <td width="33%" height="19"> 
<input type="checkbox" name="recordType" VALUE="ki">1 Kings</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="mi">Micah</td>
</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="tm">2 Timothy</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="kn">2 Kings</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="na">Nahum</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="tt">Titus</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="ch">1 Chronicles</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="hb">Habakkuk</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="pl">Philemon</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="ch">2 Chronicles</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ha">Zepheniah</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="pl">Hebrews</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="ea">Ezra</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ha">Haggai</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ja">James</td>
  </tr>
    <td width="33%" height="19"> 
<input type="checkbox" name="recordType" VALUE="ne">Nehemiah</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="zc">Zechariah</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="pe">1 Peter</td>
  </tr>
    <td width="33%" height="19">  
<input type="checkbox" name="recordType" VALUE="es">Esther</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="ml">Malachi</td>
<td width="33%" height="19">
<input type="checkbox" name="recordType" VALUE="pt">2 Peter</td>
  </tr>
    <td width="33%" height="19">  
<input TYPE="checkbox" NAME="recordType" VALUE="jb">Job</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="mt">Matthew</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="jn">1 John</td>
  </tr>
    <td width="33%" height="19">  
<input TYPE="checkbox" NAME="recordType" VALUE="ps">Psalms</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="mk">Mark</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="jnn">2 John</td>
  </tr>
    <td width="33%" height="19">  
<input TYPE="checkbox" NAME="recordType" VALUE="pr">Proverbs</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="lk">Luke</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="jhn">3 John</td>
  </tr>
    <td width="33%" height="19">  
<input TYPE="checkbox" NAME="recordType" VALUE="ec">Ecclesiastes</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="jo">John</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="ju">Jude</td>
  </tr>
    <td width="33%" height="19">  
<input TYPE="checkbox" NAME="recordType" VALUE="so">Song of Solomon</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="ac">Acts</td>
<td width="33%" height="19">
<input TYPE="checkbox" NAME="recordType" VALUE="re">Revelation</td>
  </tr>
</table>
</fieldset>

    <%
    OutputPageFooter
End Function

' This function will display the results of the search
Private Function ShowResults()
    Dim strConn    ' Database connection string
    Dim SqlBible     ' String that will have our SQL statments
    Dim RS        ' Recordset object
    Dim keyword    ' keyword for search
    Dim nRecCount    ' Number of records found
    Dim nPageCount    ' Number of pages of records we have
    Dim nPage    ' Current page number

    ' Let's see what page are we looking at right now
    nPage = CLng(Request.QueryString("Page"))

    ' Let's see what user wants to search for today :)
    keyword = Trim(Request.QueryString("keyword"))

    ' define our SQL statement
    ' we will be looking for all the records in tblItem table 
    ' where ItemName contains our keyword
    ' do not forget to fix tick marks (single quotes) in our keyword


    SqlBible = "SELECT * FROM bible WHERE <CHECKEDCOLUMN1> LIKE '%" & Replace(Keyword, "'", "''") & "%'" OR <CHECKEDCOLUMN2> LIKE '%" & Replace(Keyword, "'", "''") & "%'"


    ' Create our connection string
    strConn = GetConnectionString()


    Set Conn = Server.CreateObject("ADODB.Connection") 
    Conn.ConnectionString = GetConnectionString()

    ' Time to create and open recordset
    Set RS = Server.CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3 ' adUseClient
    RS.Open SqlBible, strConn ' adOpenKeyset CursorType
    Response.write "<Table border='1'><tr>"
    If Not RS.EOF Then
        i = 1
        While Not RS.EOF
            Response.Write "<td>"
            Response.write "<input type='checkbox' name='chkSearchFrom' value='" & RS("BookId") & "'>" & RS("BookName")
            Response.Write "</td>"
            RS.MoveNext
            If (CInt(i) mod 3) = 0 then 
                Response.Write "</tr>"
                If NOT RS.EOF then Response.Write "<tr>"
            End If
            i = CInt(i) + 1
        Wend
    End If
    Response.write "</Table>"
    RS.Close
    Conn.Close
    Set Conn = Nothing
    Set RS = Nothing
%>
    </FORM>

    <%
    OutputPageFooter
End Function
'************************************************************************************
'*My former bible.asp page
'************************************************************************************

dim iCounter
keyword=Request.QueryString("keyword")
iCounter = 0

'for the 66 books
If   request.QueryString("recordType")="yes" then

    SqlBible = SqlBible & "recordType LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

If   request.QueryString("book")="yes" then

    SqlBible = SqlBible & "book LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

'number
If   request.QueryString("book_spoke")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

End If

    SqlBible = SqlBible & "book_spoke LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if


'text
If   request.QueryString("book_title")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

End If

    SqlBible = SqlBible & "book_title LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

'number
If   request.QueryString("chapter")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

End If

    SqlBible = SqlBible & "chapter LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

'number
If   request.QueryString("chapter_spoke")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

End If

    SqlBible = SqlBible & "chapter_spoke LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

'number
If   request.QueryString("verse")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

End If

    SqlBible = SqlBible & "verse LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

'number
If   request.QueryString("verse_spoke")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

End If

    SqlBible = SqlBible & "verse_spoke LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

'number
If   request.QueryString("text_data")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

End If

    SqlBible = SqlBible & "text_data LIKE '%" & keyword & "%'"

    iCounter = iCounter + 1

end if

'************************************************************************************
'*end of my former bible.asp page
'************************************************************************************


    ' Start outputing HTML
    OutputPageHeader

    ' Did we find anything?
    If Not RS.Eof Then
        ' Let's deal with our findings

        ' Get records count
        nRecCount = RS.RecordCount

        ' Tell recordset to split records in the pages of our size
        RS.PageSize = RECORDS_PER_PAGE

        ' How many pages we've got
        nPageCount = RS.PageCount

        ' Make sure that the Page parameter passed to us is within the range
        If nPage < 1 Or nPage > nPageCount Then
            ' Ops - bad page number
            ' let's fix it
            nPage = 1            
        End If

        ' Time to tell user what we've got so far
        Response.Write nRecCount & " records found matching """ & keyword & """.<br>"
        Response.Write nPageCount & " pages of results.<br>"
        Response.Write "Current page is " & nPage & ".<p>"

        ' Give user some navigation

        ' first page
        ' we link to this page with Page parameter = 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?keyword=" & keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & 1 & _
                """>First Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Previous Page
        ' we link to this page with Page parameter = Current Page - 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?keyword=" & keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage - 1 & _
                """>Prev. Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Next Page
        ' we link to this page with Page parameter Current Page + 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?keyword=" & keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage + 1 & _
                """>Next Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Last Page
        ' we link to this page with Page parameter = nPageCount
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?keyword=" & keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPageCount & _
                """>Last Page</A>"



'************************************************************************************
'* End of Functions section
'************************************************************************************

        ' Start Results
        Response.Write "<p><b>Results:</b><br>" & String(20,"-")

        ' Position recordset to the page we want to see
        RS.AbsolutePage = nPage

        ' Let's output our records                
        ' Loop through records until it's a next page or End of Records
        Do While Not (RS.Eof OR RS.AbsolutePage <> nPage)

            ' All we do here is just show the records

Response.Write "<br>" & RS("book, book_spoke, book_title, chapter, chapter_spoke, verse, verse_spoke, text_data")

            ' Move on to the next record
            RS.MoveNext
        Loop
    Else
        ' We did not find anything
        Response.Write "Nothing found. Try again.<p><A HREF=""" & SCRIPT_NAME & """>Back</A>"
    End If

    ' Be nice - close the recordset
    RS.Close

    ' Finish this page
    OutputPageFooter
End Function

%>

Quote:

quote:SqlBible = "SELECT * FROM bible WHERE <CHECKEDCOLUMN1> LIKE '%" & Replace(Keyword, "'", "''") & "%'" OR <CHECKEDCOLUMN2> LIKE '%" & Replace(Keyword, "'", "''") & "%'"

Quote:

--------------------------------------------------------------------------------------------------------^

pointing to < after OR.

happygv · August 8th, 2004, 09:35 PM

Hi there,

Not some irrelavant stuff/missing something. MIXED up the function that I posted. You had split that into 2 parts.;)

I did post the entire ShowSearchForm() function for you. But I wonder where you got the other HUGE code from? I hope you have copy/pasted that from the other HTML file. Now I find all HARDCODED values for those 66 check boxes. What if a new book comes up and is added to you table as 67th check box in future. Will you go and change your code to have that 67th checkbox?

Not only that, You can split the code I posted and pasted the later part into the other function.

SqlBible = "SELECT * FROM bible WHERE <CHECKEDCOLUMN1> LIKE '%" & Replace(Keyword, "'", "''") & "%'" OR <CHECKEDCOLUMN2> LIKE '%" & Replace(Keyword, "'", "''") & "%'"

The RED marked are the values bound to be taken from the CHECKBOX that was selected from the search form. Do you have any column in your table by name CHECKEDCOLUMN1 and 2? I don't think so. It looks like I have to do the entire stuff for you, which I don't want to do. I would have posted the entire page for you, but that doesn't help you in learning. So I am still trying to help you code this stuff.

Quote:

quote: can you post your code, along with the database structure of that Table(that you merged together into one) and the BooksMaster Table if you have one. It is hard to suggest how the structure has to be, without looking at what you are missing.

In one of my previous posts, I had asked for your table structure, which you have't posted yet. So I had to give you clues like the SELECT statement I posted in my previous post.

Post me the Table structure of those 2 tables involved.

Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 9th, 2004, 12:18 AM

Yes I'm sorry for that. It's not my intention to make you do all the work. I've been working on this thing for weeks now looking at examples, writing in different forums. I've changed the database from a 66 table to one table (which was the original version but I had thought 66 tables may have been better but then again I'm a newbie at this). There are websites which don't explain everything as you want it either.

I tried sorting things out. Then I inserted some stuff from the previous asp/htm pages into one asp page which includes highlightin code and pageing/numbering code.

Code:

ID    |book    |book_spoke    |recordType    |book_title    |chapter    |chapter_spoke    |verse    |verse_spoke    |text_data
1    |001    |001    |gn    |Genesis    |001    |001    |001    |001    |In the beginning God created the heaven and the earth.

These are the 10 fields. ID field is to be left out.
The 2 radio buttons (this is a problem)to enable/disable the 66 checkboxes.
The recordType field has 66 paired characters identifying the 66 books.

Well I'm going to get back on it tommorrow, in several hours. I want to thank you all for your tips. I'll get to it tommorrow.

happygv · August 9th, 2004, 01:01 AM

Sorry, I am not trying to be rude here. Just wanted to get things right.
Straight away we can't get everything in place. Let us get things sorted out one by one.
So I need some clarifications on the following.

1) I see "001" as value for many of the columns.
All being those 8 CHECKBOX columns that are shown next to search Keyword textbox.
Why is that so? Is it going to be the same always or in most of the cases?
What do those 8 columns refer to? In what way they are significant in this searching operation?

2) Are these book_titles redundant in this table?
If so How many rows are there at an average for a book_title?
As suggested earlier, you can separate the Book_Title into a different table
(as this is a domain doesn't have to exisit within BIBLE) and use its
ID here instead of Book_TITLE as such.

Code:

Select distinct BOOK_TITLE into BookTitles from Bible order by Book_Title

This will create a new table called BookTitles and insert all 66 booktitles into it.
Add another column called BookTitleID(int type) to this table and add numbers from 1 to 66 for the exisiting BookTitles.

Remember... Take a baukup of BIBLE table before doing all these changes,
just in case something went wrong you can restore from there.

Also after doing this, you can add another column to Bible table called BookTitleId which is NULLable initially
and do the following update...

Code:

Update Bible set B.BookTitleId = BT.BookTitleId
from Bible B, BookTitles BT 
where B.Book_Title = BT.Book_Title

Just do a check if everything is added fine there
(Genesis's BookTitleId from BookTitles table should be there in all rows of Bible table,
that has Book_Title as "Genesis" and similarly for other records too).

After this is done, you can remove the Book_Title column from Bible table.
So now, you have BookTitles (as master table that contains all Book titles in it)
and Bible table that contains other details about each BookTitle.

Now - BookTitles is the table from where the 66 Checkboxes are to be displayed.
Any new BookTitle comes in, that is recorded into BookTitles table and
its other details are added to Bible Table, having the BookTitleId as relationship between the two.
Always search for any keyword is done on the Bible table.

Hope this explains, post here for any clarifications.
Cheers!
_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 9th, 2004, 10:24 AM

Ok the collumns:
001 is a text format. I didn't want it to be as number because Excel and Access would automatically change the 001, 002...021,022 into 1,2...21,22. And when I searched "1" it would give 1,10,11,12...

The reason there are different columns of the same number is some columns'numbers go up to 22 and restart from 1 whereas the others continue. They are divided into book#,chapter#,verse#.

Someone else suggested to create a recordType field so and assign one for every checkbox but I wasn't able to.

Is it necessary to create another table? Isn't one enough? I tell you it worked before. But when I wanted to add the pagecount/result count and highlight, things got out hand.

gilgalbiblewheel · August 9th, 2004, 03:38 PM

There's something wrong in this:

Code:

<%
 '*************************************************
***********************************
'* ADO Recordset Paging Sample Script
'* by Konstantin Vasserman
'* June 2000
 '*************************************************
***********************************

Option Explicit

 '*************************************************
***********************************
'* Declaration section
 '*************************************************
***********************************
' Mode contstants
Const MODE_DEFAULT     = 1
Const MODE_RESULTS     = 2

Const DB_NAME        = "kjv.mdb"    ' Name of our database file
Const SCRIPT_NAME    = "bible5.asp"    ' Name of this script

Const RECORDS_PER_PAGE     = 20            ' Number of records per page

Dim nMode    ' Current Mode
 '*************************************************
***********************************
'* End of Declaration section
 '*************************************************
***********************************

 '*************************************************
***********************************
'* Main section
 '*************************************************
***********************************

' Find out what mode we are in
nMode = CLng(Request.QueryString("Mode"))

' Depending on our mode we will do different things
Select Case nMode

    Case MODE_RESULTS
        ' This is where all the results will show        
        ShowResults

    Case Else     ' This one is for MODE_DEFAULT or invalid modes all the same
        ' By default display the search form
        ShowSearchForm    
End Select

 '*************************************************
***********************************
'* End of Main section
 '*************************************************
***********************************

 '*************************************************
***********************************
'* Functions section
 '*************************************************
***********************************

' This function will generate our connection string
' it assumes that Access database is in the same folder as this script
Private Function GetConnectionString()
    GetConnectionString =     "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
                "UID=;PWD=;"
End Function

' Shows HTML page header 
Public Function OutputPageHeader()
    %>
    <HTML>
    <HEAD><TITLE>ADO Recordset Paging Sample</TITLE></HEAD>
    <BODY>
    <H2>Search the bible, the Wheel of God</H2>
    <H3><A HREF="<%=SCRIPT_NAME%>">Back Home</A></H3>
    <%
End Function

' Shows HTML page footer 
Public Function OutputPageFooter()
    %>
    </BODY>
    </HTML>
    <%
End Function

' This function will display the search form
Private Function ShowSearchForm()
    OutputPageHeader
    %>
    <!--
        This form will direct user to itself with MODE_RESULTS mode
    -->
    <FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
    Look for: <INPUT TYPE="text" NAME="Keyword" VALUE=""> <INPUT TYPE="submit" VALUE=" Search "><input type="reset">
<INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>"><p>
If you search number write 001 instead of 1 and 022 instead of 22

</td>
<br>
<INPUT TYPE="CheckBox" NAME="book" VALUE="Book">Book<br>
    <INPUT TYPE="CheckBox" NAME="book_spoke" VALUE="Book Spoke">Book Spoke<br>
    <INPUT TYPE="CheckBox" NAME="book_title" VALUE="Book Title">Book Title<br>
    <INPUT TYPE="CheckBox" NAME="chapter" VALUE="Chapter">Chapter<br>
    <INPUT TYPE="CheckBox" NAME="chapter_spoke" VALUE="Chapter Spoke">Chapter Spoke<br>
    <INPUT TYPE="CheckBox" NAME="verse" VALUE="Verse">Verse<br>
    <INPUT TYPE="CheckBox" NAME="verse_spoke" VALUE="Verse Spoke">Verse Spoke<br>
    <INPUT TYPE="CheckBox" NAME="text_data" VALUE="Text" CHECKED>Text<br>
 </tr>
    </FORM>
    <%
    OutputPageFooter
End Function

TO BE CONTINUED...PART 2 WITH THE ERROR

gilgalbiblewheel · August 9th, 2004, 03:42 PM

PART 2 OF THE CODE WITH THE ERROR

Code:

' This function will display the results of the search
Private Function ShowResults()
    Dim strConn    ' Database connection string
    Dim SQL     ' String that will have our SQL statments
    Dim RS        ' Recordset object
    Dim Keyword    ' Keyword for search
    Dim nRecCount    ' Number of records found
    Dim nPageCount    ' Number of pages of records we have
    Dim nPage    ' Current page number
    Dim iCounter    'for the checkboxes

    ' Let's see what page are we looking at right now
    nPage = CLng(Request.QueryString("Page"))

    ' Let's see what user wants to search for today :)
    Keyword = Trim(Request.QueryString("Keyword"))

    ' define our SQL statment
    ' we will be looking for all the records in tblItem table 
    ' where ItemName contains our Keyword
    ' do not forget to fix tick marks (single quotes) in our Keyword
    SQL = "SELECT * FROM bible WHERE "
    ' Create our connection string
    strConn = GetConnectionString()

If   request.QueryString("book")="yes" then

  Sql = Sql & "book LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("book_spoke")="yes" then

  If iCounter > 0 Then
    Sql = Sql & " AND "
  End If

  Sql = Sql & "book_spoke LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("book_title")="yes" then

  If iCounter > 0 Then
    Sql = Sql & " AND "
  End If

  Sql = Sql & "book_title LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("chapter")="yes" then

  If iCounter > 0 Then
    Sql = Sql & " AND "
  End If

  Sql = Sql & "chapter LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if


If   request.QueryString("chapter_spoke")="yes" then

  If iCounter > 0 Then
    Sql = Sql & " AND "
  End If

  Sql = Sql & "chapter_spoke LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("verse")="yes" then

  If iCounter > 0 Then
    Sql = Sql & " AND "
  End If

  Sql = Sql & "verse LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("verse_spoke")="yes" then

  If iCounter > 0 Then
    Sql = Sql & " AND "
  End If

  Sql = Sql & "verse_spoke LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

If   request.QueryString("text_data")="yes" then

  If iCounter > 0 Then
    Sql = Sql & " AND "
  End If

  Sql = Sql & "text_data LIKE '%" & Keyword & "%'"

  iCounter = iCounter + 1

end if

    ' Time to create and open recordset
    Set RS = Server.CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3 ' adUseClient
    RS.Open SQL, strConn ' adOpenKeyset CursorType

    ' Start outputing HTML
    OutputPageHeader

    ' Did we find anything?
    If Not RS.Eof Then
        ' Let's deal with our findings

        ' Get records count
        nRecCount = RS.RecordCount

        ' Tell recordset to split records in the pages of our size
        RS.PageSize = RECORDS_PER_PAGE

        ' How many pages we've got
        nPageCount = RS.PageCount

        ' Make sure that the Page parameter passed to us is within the range
        If nPage < 1 Or nPage > nPageCount Then
            ' Ops - bad page number
            ' let's fix it
            nPage = 1            
        End If

        ' Time to tell user what we've got so far
        Response.Write nRecCount & " records found matching """ & Keyword & """.<br>"
        Response.Write nPageCount & " pages of results.<br>"
        Response.Write "Current page is " & nPage & ".<p>"

        ' Give user some navigation

        ' first page
        ' we link to this page with Page parameter = 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & 1 & _
                """>First Page</A>"
        Response.Write "    "

        ' Previous Page
        ' we link to this page with Page parameter = Current Page - 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage - 1 & _
                """>Prev. Page</A>"
        Response.Write "    "

        ' Next Page
        ' we link to this page with Page parameter Current Page + 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage + 1 & _
                """>Next Page</A>"
        Response.Write "    "

        ' Last Page
        ' we link to this page with Page parameter = nPageCount
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPageCount & _
                """>Last Page</A>"

        ' Start Results
        Response.Write "<p><b>These are the results:</b><br>" & String(20,"-")

        ' Position recordset to the page we want to see
        RS.AbsolutePage = nPage

        ' Let's output our records                
        ' Loop through records until it's a next page or End of Records


<table BORDER="0" width="100%" cellpadding="3">
  <tr>
    <th bgcolor="#800000">Book </th>
    <th bgcolor="#800000">Book Spoke </th>
    <th bgcolor="#800000">Book Title </th>
    <th bgcolor="#800000">Chapter </th>
    <th bgcolor="#800000">Chapter Spoke</th>
    <th bgcolor="#800000">Verse </th>
    <th bgcolor="#800000">Verse Spoke</th>
    <th bgcolor="#800000">Text </th>
  </tr>

        Do While Not (RS.Eof OR RS.AbsolutePage <> nPage)




  <tr>
        <td><%=rs("book")%>#32
</td>
    <td><%=rs("book_spoke")%>
</td>
        <td><%=rs("book_title")%>
</td>
        <td><%=rs("chapter")%>
</td>
      <td><%=rs("chapter_spoke")%>
</td>
        <td><%=rs("verse")%>
</td>
        <td><%=rs("verse_spoke")%>
</td>
        <td><%=rs("text_data")%>
</td>
  </tr>

</table>


            ' Move on to the next record
            RS.MoveNext
        Loop
    Else
        ' We did not find anything
        Response.Write "Nothing found. Try again.<p><A HREF=""" & SCRIPT_NAME & """>Back</A>"
    End If

    ' Be nice - close the recordset
    RS.Close

    ' Finish this page
    OutputPageFooter
End Function
 '*************************************************
***********************************
'* End of Functions section
 '*************************************************
***********************************
%>

Quote:

quote:Microsoft VBScript compilation error '800a0400'

Quote:

Expected statement

/bible5.asp, line 307

<table BORDER="0" width="100%" cellpadding="3">
^

happygv · August 9th, 2004, 09:09 PM

Please don't post the entire code again and again as the page looks cluttered and one can never understand what is going on here. I see a lot of posts from you on this, before one being responsed/replied. First of all I am really confused to go through your post that contain the lengthy code and find too many such posts. You can still delete your post whichever you posted redundantly here.

I would suggest you to stick to any one solution. If you are going to take suggestions from the whole world at the same time, I would say that you would never complete this off.

So which one do you want me to reply to now?

_________________________
- Vijay G
Strive for Perfection