Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 3rd, 2004, 06:04 PM
Friend of Wrox
Points: 1,107, Level: 13
Points: 1,107, Level: 13 Points: 1,107, Level: 13 Points: 1,107, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2004
Location: , , .
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default select from 66 tables using checkboxes - how

Ok. I have a 66 table database. I want the viewer to choose his table to search in. I have an example of the design but need help in the ASP response page:

http://wheelofgod.tripod.com/ASPSearchamos2.htm

the first 6 options are for table columns. Then there is 2 radion buttons which enable/disable the 66 tables.

I was wondering if
quote:
--------------------------------------------------------------------------------
select * from
--------------------------------------------------------------------------------

can come in an "if" statement? If so how would the code be written?


Reply With Quote
  #2 (permalink)  
Old August 3rd, 2004, 08:31 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Mentiri, Muara, Brunei.
Posts: 363
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hello,
  I understand that u want to retrieve the fields which the user checked.

if you give same name to all checkboxes it will create an array.
And i suggest that you give "value" for each checkbox as columnname (Ex:book)

Then you can read it as

'*************At the submitted page
dim strFields
strFields=request("checkboxname")

strQry="select " & strFields & " from tablename"

i think this is what you need.

Reply With Quote
  #3 (permalink)  
Old August 4th, 2004, 12:41 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: colombo, WP, Sri Lanka.
Posts: 481
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via MSN to surendran Send a message via Yahoo to surendran
Default

hi gilgalbiblewheel
try this

strFields=request("checkboxname")

strQry="select * from " & strFields & ""

surendran
(Anything is Possible)
Reply With Quote
  #4 (permalink)  
Old August 4th, 2004, 08:12 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi gilgalbiblewheel,

If I am not wrong, you are trying to search for the KEYWORD (entered in the textbox) against all CHECKED columns among those 6 checkboxes, from all the tables CHECKED among those 66 table Checkboxes.

This is actually taking all the columns that are checked and compare for search keyword in it for every table.

Assume you have CHECKED the BOOK, SPOKE and TEXT from the COLUMNS and CHECKED the tables as Genesis, Isaiah, Romans... then you should contruct an sql statement that would check it this way.

Select <DISPLAY_COLUMNs>
from Genesis
where BOOK like '%' + strSearchKeyWord + '%' OR
SPOKE like '%' + strSearchKeyWord + '%' OR
TEXT like '%' + strSearchKeyWord + '%'

Now replace GENESIS with Isaiah in the next query and then with ROMANS in the next query and finally you should UNION ALL and that should contruct your recordset. Which is round about way of acheiving.

And Surendran's solution would not fit this. coz' as per his code, the sql statement would look like

Quote:
quote:strFields=request("checkboxname")
strQry="select * from " & strFields & ""
select * from Genesis, Isaiah, Romans
this would not solve the purpose, instead result in huge result set of 9 columns(imaging if all 66 table are checked, it would result in 66 * 3 = 198 columns per row) because, all the three tables have the same column names(book, spoke, text) in it that woudl result in repetion and you cannot differentiate which has come from which table. And you are not using object referencing there, instead using a *. Also WHERE clause is missing, so what would you be searching for atlast?

Same as the case of RajaniKrisna's solution, only difference is that he has suggested to use that variable in the COLUMNs list, which would not solve the purpose.

I feel that there is a flaw in designing this database structure. When all those 66 tables are having same number of columns all with same names too(I believe so, as per the UI design I could see there), which are similar to all 66 tables, having the data stored in different tables is a real blunder, that makes things complex for you.

You could have designed a single table to hold all that.
BooksMastertable
BOOKID
BookName

SearchStringMatchTable (name it the rightway, that suits the context)
BOOKID
Book
Spoke
Book Title
Chapter
Verse
Text

So there would be 66 different values for BOOK column each having a reference to different BOOKID, similary all other columns would have its own value pertaining to each BOOKID.

So based on the selection made in the asp page, you could easily contruct you SELECT query that would look like...

Code:
strSearchKeyWord = Request.Form("txtSearch")

strColumns would hold "Book, Spoke, Text" separated with comma,
provided that all those 6 checkboxes are given the same name.
strColumns = Request.Form(chkColumns)

Similarly strBooks would hold BOOKID of Genesis, Isaiah, Romans as per selection made, 
where the checkboxes are already set with value as BOOKID. 
Assume that the value of strBooks finally would have "1,2,3"
strBooks = Request.Form(chkBooks)

strSql = "Select <YourColumnName(s)> from SearchStringMatchTable Where "

arrSearchColumns = split(strColumns,",")
For i = 0 to Ubound(arrSearchColumns)
    strSql = strSql & arrSearchColumns(i) & " like '%" & strSearchKeyWord & "%'"
    If i < Ubound(arrSearchColumns) then 
        strSql = strSql & " OR "
    Else
        strSql = strSql & " AND "
    End If
Next

strSql = strSql & "BookId in (" & strBooks & ")"
Response.write strSql
Assuming your Seach Keyword is "King" then the output of strSql string would look like
Select <YourColumnName(s)> from SearchStringMatchTable
Where Book like '%King%' OR Spoke like '%King%' OR Text like '%King%'
AND BookId in (1,2,3)

Does this make sense?
Hope this helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #5 (permalink)  
Old August 4th, 2004, 11:41 AM
Friend of Wrox
Points: 1,107, Level: 13
Points: 1,107, Level: 13 Points: 1,107, Level: 13 Points: 1,107, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2004
Location: , , .
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

I changed my code yesterday. Instead of having 66 tables I reunited them into one table. But I don't know how to apply the radio buttons and checkboxes of the 66 selections. Here's the sample:

http://wheelofgod.tripod.com/ASPSearchbible.htm
and
http://wheelofgod.tripod.com/bible.asp

Unfortunately the database is too big for me to upload it. The first is the htm page and the second the answer or response page.

As you see the 8 first checkboxes represent 8 fields in the table and work perfectly.

My problem is the radio buttons and the 66 checkboxes. The radio buttons are for yes (search the entire table) or no (select your preferense). I guess each of the 66 will be divided with a range of ID (genesis: 1-546, exodus: 547-876...). But I need more guidance.

Thanks.

Reply With Quote
  #6 (permalink)  
Old August 5th, 2004, 08:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

So now you should be having the BooksMaster Table entries in another table. You got to generate the checkboxes for 66 books based on the value in that table. Something like

Code:
strString = "select BookId, BookName from BooksMaster"
...
...
While not rs.EOF
    <input type="Checkbox" name="Books" value='<%=rs("BookId")%>'><%=rs("BookName")
Wend
And for RadioButtons...

The entire King James Bible
<INPUT TYPE="Radio" VALUE='EntirTable' onClick=disable(true); name="Card">
Your selection of books
<INPUT TYPE="Radio" VALUE='Books' onClick=disable(false); name="Card">

If value is BOOKS then enable all 66 checkboxes, If Value is EntireTable then disable all those 66 checkboxes

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #7 (permalink)  
Old August 5th, 2004, 10:38 AM
Friend of Wrox
Points: 1,107, Level: 13
Points: 1,107, Level: 13 Points: 1,107, Level: 13 Points: 1,107, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2004
Location: , , .
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

I changed the 66 tables and formed one table (I don't think I'll be able to find free webspace in any website).

As far as organization goes how should my asp/database answer page has to be structured? Because I'm a newbie and looking at it makes me confused.

Reply With Quote
  #8 (permalink)  
Old August 7th, 2004, 03:15 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

If the page is not too long to have posted here, 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.

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #9 (permalink)  
Old August 7th, 2004, 03:19 PM
Friend of Wrox
Points: 1,107, Level: 13
Points: 1,107, Level: 13 Points: 1,107, Level: 13 Points: 1,107, Level: 13
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2004
Location: , , .
Posts: 240
Thanks: 0
Thanked 1 Time in 1 Post
Default

Here it is:

Code:
<%
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    = "bible2.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>ADO Recordset Paging Sample</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">
    Item Name: <INPUT TYPE="text" NAME="Keyword" VALUE="Item"> <INPUT TYPE="submit" VALUE=" Search ">
    <INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>">
    </FORM>
    <%
    OutputPageFooter
End Function

' 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

    ' 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
    SQL = "SELECT * FROM bible WHERE text_data LIKE '%" & Replace(Keyword, "'", "''") & "%'"

    ' Create our connection string
    strConn = GetConnectionString()

    ' 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 "&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>"

        ' 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("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
'************************************************************************************
'* End of Functions section
'************************************************************************************
%>
Reply With Quote
  #10 (permalink)  
Old August 8th, 2004, 01:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi there,

This is how your Function ShowSearchForm() would look.

Code:
Private Function ShowSearchForm()
    OutputPageHeader
    %>
    <!--
        This form will direct user to itself with MODE_RESULTS mode
    -->
    <FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
    Search For: <INPUT TYPE="text" NAME="Keyword" VALUE="Item"> <INPUT TYPE="submit" VALUE=" Search ">
    <INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>">
    <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>

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

    Where do you want to search?<br><br>
<%
    Set Conn = Server.CreateObject("ADODB.Connection") 
    Conn.ConnectionString = GetConnectionString()

    Set RS = Server.CreateObject("ADODB.Recordset")
    Not sure about the ColumnNames and TableName at your end. Use the relavant names marked in red below.
    strSQL = "Select BookId, BookName from BooksMaster Order by BookName"
    RS.Open strSQL, Conn
    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
Hope this helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select all checkboxes in a datagrid kawal.singh C# 2 May 7th, 2007 02:09 PM
Select from Two Tables Problem gaurav_jain2403 SQL Server 2000 2 May 18th, 2006 07:18 AM
Select lot tables... vieritlc Classic ASP Databases 3 May 18th, 2004 07:07 AM



All times are GMT -4. The time now is 06:02 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.