Searching multiple tables on an ASP website

MoonKlash · #1 (**permalink**) 10-04-04, 12:57

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

RBARAER · #2 (**permalink**) 10-04-04, 13:54

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

rokslide · #3 (**permalink**) 10-05-04, 01:29

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.

MoonKlash · #4 (**permalink**) 10-05-04, 05:10

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.

RBARAER · #5 (**permalink**) 10-05-04, 09:05

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

rokslide · #6 (**permalink**) 10-05-04, 20:08

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

rokslide · #7 (**permalink**) 10-05-04, 20:28

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.

MoonKlash · #8 (**permalink**) 10-08-04, 08:28

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.

MoonKlash · #9 (**permalink**) 10-08-04, 09:40

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:


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

rokslide · #10 (**permalink**) 10-10-04, 20:34

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.

MoonKlash · #11 (**permalink**) 10-11-04, 07:35

Thanks! I'm trying something along those lines. I'll let you know how it goes!

Cheers,
Stu.

MoonKlash · #12 (**permalink**) 10-11-04, 08:49

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:


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

rokslide · #13 (**permalink**) 10-11-04, 20:11

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.

MoonKlash · #14 (**permalink**) 10-12-04, 05:14

That's fantastic! Thanks for your help - I shall go and try that now!

Cheers,
Stu.

MoonKlash · #15 (**permalink**) 10-13-04, 10:32

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.