Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2003
    Posts
    13

    Unhappy Unanswered: Searching multiple tables on an ASP website

    Hello,
    I haven't done much with ASP but now I'm giving it a go! However, I'd
    like to ask if anybody knows if what I need to do is possible.

    I have a database with a lot of tables. Each table represents a
    customer. As customers come and go, so tables will be added and
    removed.

    I need to create a search utility that will search all tables for
    (e.g.) a customer name. I'm learning ASP so I'm not that advanced, but
    I'm assuming it will use some kind of SQL statment along the lines of
    SELECT [criteria] FROM [tablename]. Do I need to manually add each
    table name or is there a command that will automatically select all
    tables?

    Many thanks in advance! All advice will be of use. I'm using
    dreamweavr MX and Access 2000.

    Cheers,
    Stu.


    moonklash
    -------------
    Stuart Clark BSc (Hons)
    Temp Property Database Developer EHDC
    http://www.moonklash.com

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Well, I'm sorry to say that, but I fear that before coding any ASP, you will have to redesign your database schema.

    One table per customer is a very bad design.

    You should have only one table CUSTOMERS, with, for example, fields ID, NAME, ADDRESS, PHONE NUMBER, EMAIL... and so on.

    Now, of course, you may need other tables for other types of data, such as PRODUCTS, COUNTRIES, ... I don't know what you want to store exactly in your DB... But don't create a table per customer !!!

    It will be cleaner, much easier to use, and much more efficient.

    If you want some help about schema design, I'd suggest you to go to a more appropriate forum, this one for example : Database Concepts & Design .

    Good luck !

    Regards,

    RBARAER

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    unfortunately I really have to agree with the last reply. the db design as you have suggested it sounds like it really needs to be worked on before you start thinking about doing any real sort of querying no matter what tool you are using.

  4. #4
    Join Date
    Aug 2003
    Posts
    13

    Thumbs down

    Hiya,
    Thanks to all for the advice! Unfortunetely I'm not able to redesign the database as it's made like that for compatability with the mail software. Rather awkward I realise but not much I can do! My first ASP assignment is proving rather tricky.

    I was wondering if it's possible to make a query that would list the contents of all the involved tables. I gather it's called a Union query.... does anyone have any ideas if this could work?

    Alternatively, I assume it isn't possible to have a wildcard after the FROM in the SELECT statement is the form of:

    SELECT abc from *

    I suspect that I'm just going to have to do a very long SELECT statement and manually list every single table. It's certainly not an ideal solution but unfortunetely a redesign is out of the question

    Thanks for all the advice so far!

    Cheers,
    Stu.
    -------------
    Stuart Clark BSc (Hons)
    Temp Property Database Developer EHDC
    http://www.moonklash.com

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello again,

    Well... I must say... I DON'T ENVY YOU AT ALL !

    If schema redesign is not possible at all, then you will have to do with what you have... But understand this is really BAD WORK.

    If all your customers' tables have the same structure, then YES you can use a UNION query to gather all results. And NO there is no SELECT abc FROM *;
    Sorry.

    I really wish you good luck !

    Regards,

    RBARAER

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    the key problem is going to know what the table names are. what sort of database is it? Depending on how you name your tables and what the database is you may be able to use some of the system tables and stored procedures to achieve what you need. Just re-read and I see you are using access... that's not going to help....

    Let me have a play with some tricks that may work and get back to you....

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    If you create this function (you might want to tidy it up as it's jsut rough) in your access database...
    Code:
    Function GetAllTables() As String
    'Returns all  tables
        Dim tmpTables As String
        Set db = CurrentDb
        db.TableDefs.Refresh
        For Each tdf In db.TableDefs
            tmpTables = tmpTables & tdf.Name & ","
        Next
        tmpTables = Left(tmpTables, Len(tmpTables) - 1)
        Set collTables = Nothing
        Set tdf = Nothing
        Set db = Nothing
        GetAllTables = tmpTables
    End Function
    You should then be able to run a query like this
    Code:
    SELECT GetAllTables() AS TableNames;
    Which will return a coma delimited string of all your tables.

    You can then use split to put this into an array, iterate through the array and find the tables you need (assuming a standard naming convention of some sort) and build a union query based on them.

    Disclaimer: I really don't recommend this as a good solution, just as something that might work with the database structure you have to work with.

  8. #8
    Join Date
    Aug 2003
    Posts
    13
    Quote Originally Posted by RBARAER
    Hello again,

    Well... I must say... I DON'T ENVY YOU AT ALL !

    If schema redesign is not possible at all, then you will have to do with what you have... But understand this is really BAD WORK.

    If all your customers' tables have the same structure, then YES you can use a UNION query to gather all results. And NO there is no SELECT abc FROM *;
    Sorry.

    I really wish you good luck !

    Regards,

    RBARAER
    Thanks for the advise!
    I bet I could have got it working as well, but I've now learnt that I can't actually make any queries in the database because of the way it's used by the mail software....!

    Not having much luck really! Arggghhhh!

    Cheers,
    Stu.
    -------------
    Stuart Clark BSc (Hons)
    Temp Property Database Developer EHDC
    http://www.moonklash.com

  9. #9
    Join Date
    Aug 2003
    Posts
    13
    Quote Originally Posted by rokslide
    If you create this function (you might want to tidy it up as it's jsut rough) in your access database...
    Code:
    Function GetAllTables() As String
    'Returns all  tables
        Dim tmpTables As String
        Set db = CurrentDb
        db.TableDefs.Refresh
        For Each tdf In db.TableDefs
            tmpTables = tmpTables & tdf.Name & ","
        Next
        tmpTables = Left(tmpTables, Len(tmpTables) - 1)
        Set collTables = Nothing
        Set tdf = Nothing
        Set db = Nothing
        GetAllTables = tmpTables
    End Function
    You should then be able to run a query like this
    Code:
    SELECT GetAllTables() AS TableNames;
    Which will return a coma delimited string of all your tables.

    You can then use split to put this into an array, iterate through the array and find the tables you need (assuming a standard naming convention of some sort) and build a union query based on them.

    Disclaimer: I really don't recommend this as a good solution, just as something that might work with the database structure you have to work with.

    Hi,

    Thanks for the advise! Wow - that's complicated (to a relative newbie like me) but interesting! I'm still trying to get my head around it!

    Interesting to see the way the tables are stored, hadn't been aware of that. I shall have a play around and see what happens.

    The solution I had been working on was to create a second database in which a list of the table names are stored. I then tried to nest the bit where the recordset is created inside a select statement, so that it would work as follows:

    DB1 - The table with all the user information
    DB2 - A second table which just contains a table with a list of the names of the tables from DB1

    - Create RecordSet based on DB2
    - Gets name of table from DB2
    - Creates RecordSet from DB1 using the tablename found in DB2 table
    - Searches this recordset and outputs any findings
    - Loops to top and gets next table name. Recreates the same recordset for DB1, using the next table name from DB2.

    I tried to implement this as follows:

    IN THE HEAD:
    <!--#include file="Connections/imaildata.asp" -->
    <!--#include file="Connections/imailinfo.asp" -->
    <%
    Dim rsTableName
    Dim rsTableName_numRows

    Set rsTableName = Server.CreateObject("ADODB.Recordset")
    rsTableName.ActiveConnection = MM_imailinfo_STRING
    rsTableName.Source = "SELECT * FROM tblTableName"
    rsTableName.CursorType = 0
    rsTableName.CursorLocation = 2
    rsTableName.LockType = 1
    rsTableName.Open()

    rsTableName_numRows = 0
    %>


    <%
    Dim rsData
    Dim rsData_numRows

    Set rsData = Server.CreateObject("ADODB.Recordset")
    rsData.ActiveConnection = MM_imaildata_STRING
    rsData.Source = "SELECT * FROM domain1_co_uk"
    rsData.CursorType = 0
    rsData.CursorLocation = 2
    rsData.LockType = 1
    rsData.Open()

    rsData_numRows = 0
    %>

    IN THE BODY:
    <%
    set objTableName = rsTableName("TableName")
    while not rsTableName.EOF
    objTableName = rsTableName("TableName")
    Response.write "Searching table " & "<i>" & objTableName &"</i><br>"
    rsData.Source = "SELECT * FROM " & objTableName
    rsTableName.MoveNext
    Wend
    %>



    But get the error:
    Error Type:
    ADODB.Recordset (0x800A0E79)
    Operation is not allowed when the object is open.
    /imail/TMP8r02a59lyh.asp, line 48


    Most of the code is generated by Dreamweaver, and modified by me. I'm a newbie so there are probably a lot of mistakes.....

    Any ideas? I tried adding various rsData.close() etc in the tags in the body to no avail...

    Anyway, I'm guessing that you can't do this so now I'll try some of the other suggestions!

    Cheers,

    Stu.
    -------------
    Stuart Clark BSc (Hons)
    Temp Property Database Developer EHDC
    http://www.moonklash.com

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    this will be the part that is causing you problems...
    Code:
    set objTableName = rsTableName("TableName")
    while not rsTableName.EOF
    objTableName = rsTableName("TableName")
    Response.write "Searching table " & "<i>" & objTableName &"</i><br>"
    rsData.Source = "SELECT * FROM " & objTableName
    rsTableName.MoveNext
    Wend
    what you really need to do it (in psuedo code) is something more like...

    open table name record set
    while not tablenameRS.eof
    add tablename("name") to list of names
    loop
    create sql string with list of table names
    open data recordset using the sql string created
    loop through the data recordset to present the information you require.

  11. #11
    Join Date
    Aug 2003
    Posts
    13
    Thanks! I'm trying something along those lines. I'll let you know how it goes!

    Cheers,
    Stu.
    -------------
    Stuart Clark BSc (Hons)
    Temp Property Database Developer EHDC
    http://www.moonklash.com

  12. #12
    Join Date
    Aug 2003
    Posts
    13
    Hiya

    In order to try and work out how to do this (as I'm a beginner) I've just started simple and I've tried to make the db show the results of just two tables without doing anything clever! I have the following code:

    <!--#include file="Connections/imaildata.asp" -->
    <%
    Dim rsData
    Dim rsData_numRows

    Set rsData = Server.CreateObject("ADODB.Recordset")
    rsData.ActiveConnection = MM_imaildata_STRING
    rsData.Source = "SELECT * FROM domain1_co_uk, domain2_com"
    rsData.CursorType = 0
    rsData.CursorLocation = 2
    rsData.LockType = 1
    rsData.Open()

    rsData_numRows = 0
    %>

    And then in the body I have:

    <%
    while not rsData.EOF
    Response.write rsData("FULLNAME")&"<br>"
    rsData.MoveNext
    Wend
    %>

    However, instead of getting a list of the fullname fields from both tables I get strangely duplicated data from just one table, in the form of:


    edmund blackadder
    edmund blackadder
    percy
    percy
    baldrick
    baldrick
    kevin darling
    kevin darling

    etc

    Any ideas why?

    Cheers then!
    Stu.

  13. #13
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    okie you need to change your sql statement to be something more like...
    Code:
    rsData.Source = "SELECT fullname from domain1_co_uk union select fullname as fullname2 FROM domain2_com"
    this will query each table and return the combined results.

  14. #14
    Join Date
    Aug 2003
    Posts
    13
    That's fantastic! Thanks for your help - I shall go and try that now!

    Cheers,
    Stu.
    -------------
    Stuart Clark BSc (Hons)
    Temp Property Database Developer EHDC
    http://www.moonklash.com

  15. #15
    Join Date
    Aug 2003
    Posts
    13

    Thumbs up Nearly there!

    Hi,

    I nearly have it cracked!

    My current method (which is nearly working) is to have a 2nd database, with a table that has a list of all the table names held in it.

    I have two ASP pages. The first one accesses this database, retrieves a list of names, creates a SELECT/UNION statement which it stores as a cookie. The second page then loads the cookie and uses this statement to search the tables stored in the cookie.

    Someone send me some code that will display all the table names and it's proved useful! I'm trying to apply my current methodology to this by using the following code:


    <%
    dbname = "databasename"

    ' Use this string if using Access:
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source="
    ConnStr = ConnStr & "C:\Inetpub\wwwroot\Imail\data\ImailData.mdb"

    set adoxConn = CreateObject("ADOX.Catalog")
    set adodbConn = CreateObject("ADODB.Connection")
    adodbConn.open ConnStr
    adoxConn.activeConnection = adodbConn

    Dim objSlct
    objSlct = "SELECT * FROM " & table.name

    for each table in adoxConn.tables
    if table.type="TABLE" then
    objSlct = objSlct & " UNION ALL SELECT * FROM " &table.name
    end if
    next
    adodbConn.close: set adodbConn = nothing
    set adoxConn = nothing
    %>


    I then intend to write this select statement to a cookie, which wil be read by the second page and do the same as before. However, before I get that far I receive an ASP error:

    Object required: ''
    /imail/TMPbwwuy5iyse.asp, line 21

    (Line 21 is the line that reads objSlct = "SELECT * FROM " & table.name)

    Any ideas what could be causing this?

    Thanks again for the help everyone has given!

    Cheers,
    Stu.
    -------------
    Stuart Clark BSc (Hons)
    Temp Property Database Developer EHDC
    http://www.moonklash.com

Posting Permissions

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