Viewing the contents of two tables using ASP and MS Access

Hiya

I'm learning ASP using Access and Dreamweaver. 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")&"
"
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.



reference - moonklash

Re: Viewing the contents of two tables using ASP and MS Access

Stuart Clark wrote:
> Hiya
>
> I'm learning ASP using Access and Dreamweaver. 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")&"
"
> 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?
>
Yes. You've just done what's known as a Cartesian join. You need to read up
on SQL. Access online help can help. Do a Google for SQL tutorials.
Even better is using the Access query builder to create your queries,
switching to SQL View to see what the sql statement should look like.

In this case, you have to specify an inner join between the two tables, and
specify the field or fields that relate the records in the two tables.
Without knowing the structure, I can't get specific. Based on your table
names, I suspect your database is not normalized, so you should also do a
Google for database normalization rules.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Viewing the contents of two tables using ASP and MS Access

Hi,

Thanks for the advise! Unfortunetely I'm unable to make any change to
the database. The only area I have any control over is the web pages.
However I'll try looking on the internet as you suggested. Do you know
any sites that will cover this? Most of my research has only really
covered the very basics and then moved onto other areas?

Cheers,
Stu.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

I forgot to mention the structure:

Basically, this database is used by a software application on a mail
server. Because of the way the software works I can't change the
database design at all.

The software creates one table per user. Each table has exactly the same
fields.

I have to write an ASP website that will display the contents of every
table in the database. Preferably there should be a relatively easy way
to alter the SELECT statement to include new tables when people start
joining the mail service, and easy to remove users when old customers
leave. However, I'm going to play with that later.

I'm fairly new to ASP and so for now I'm just trying to make a simple
page that lists the names of all the people from across two tables.

I realise that ordinarilly you would have all the user information in
one table, but this cannot be altered due to the constraints of the
software program. I also can't create a query in Access as the database
will not be editable. All I can do is have the correct type of Select
statement on my ASP page.

So any advice on how the Select statement should look will be gratefully
received!

Cheers,
Stu.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

http://www.aspfaq.com/show.asp?id=2178

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com


"Stuart Clark" wrote in message
news:O01K2R6rEHA.1036@TK2MSFTNGP10.phx.gbl...
> I forgot to mention the structure:
>
> Basically, this database is used by a software application on a mail
> server. Because of the way the software works I can't change the
> database design at all.
>
> The software creates one table per user. Each table has exactly the same
> fields.
>
> I have to write an ASP website that will display the contents of every
> table in the database. Preferably there should be a relatively easy way
> to alter the SELECT statement to include new tables when people start
> joining the mail service, and easy to remove users when old customers
> leave. However, I'm going to play with that later.
>
> I'm fairly new to ASP and so for now I'm just trying to make a simple
> page that lists the names of all the people from across two tables.
>
> I realise that ordinarilly you would have all the user information in
> one table, but this cannot be altered due to the constraints of the
> software program. I also can't create a query in Access as the database
> will not be editable. All I can do is have the correct type of Select
> statement on my ASP page.
>
> So any advice on how the Select statement should look will be gratefully
> received!
>
> Cheers,
> Stu.
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

This is why this design is frowned upon. There is no way to create a query
to show the data from all these tables, automatically adding new tables
after they are created.

You can create a UNION query that will combine the data from the existing
tables. it would look like this:

select * from table1
union all
select * from table2
etc.

Using the information contained in Mark's link, you can get a list of all
the tables, scroll through them and generate a long union query from that
list.

Bob Barrows

Stuart Clark wrote:
> I forgot to mention the structure:
>
> Basically, this database is used by a software application on a mail
> server. Because of the way the software works I can't change the
> database design at all.
>
> The software creates one table per user. Each table has exactly the
> same fields.
>
> I have to write an ASP website that will display the contents of every
> table in the database. Preferably there should be a relatively easy
> way to alter the SELECT statement to include new tables when people
> start joining the mail service, and easy to remove users when old
> customers leave. However, I'm going to play with that later.
>
> I'm fairly new to ASP and so for now I'm just trying to make a simple
> page that lists the names of all the people from across two tables.
>
> I realise that ordinarilly you would have all the user information in
> one table, but this cannot be altered due to the constraints of the
> software program. I also can't create a query in Access as the
> database will not be editable. All I can do is have the correct type
> of Select statement on my ASP page.
>
> So any advice on how the Select statement should look will be
> gratefully received!
>
> Cheers,
> Stu.
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Viewing the contents of two tables using ASP and MS Access

Thanks to everyone for the advise! I now have a fair amount to play
with! I'll let you know how it goes!

Cheers,
Stu.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

Hiya!
Thanks for the advise! I've now got some bits to try. I shall have a
play and let you know how it goes!

Cheers for the help,

Stu.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

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 to ASP pages. The first one accesses this database, retieves 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.

I've had a look at the link you gave me (Thanks!) 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.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

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.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

Stuart Clark wrote:
>
> set adoxConn = CreateObject("ADOX.Catalog")
> set adodbConn = CreateObject("ADODB.Connection")
> adodbConn.open ConnStr
> adoxConn.activeConnection = adodbConn
>
> Dim objSlct

Why are you using the "obj" prefix for a string variable? "obj" is typically
used for object variables.

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

You haven't declared (Dim) or instantiated the table variable yet (that
happens in the loop). Change it to:

objSlct = ""



> for each table in adoxConn.tables
> if table.type="TABLE" then

if len(objSlct) = 0 then
objSlct = "SELECT * FROM " & table.name
else
> objSlct = objSlct & " UNION ALL SELECT * FROM " & table.name
end if
> end if
> next

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Viewing the contents of two tables using ASP and MS Access

Hi there,

Thanks for the advise - that certainly makes a lot of sense! I shall
have a play in a moment and see if that works.

You were right to comment about my inappropriately named variables! My
ASP knowledge is rather poor and my notes are rather scattered about.
That particular name came from where I followed a different example but
forgot to give the variable a more relevant name. I'm going to leave the
names as they are for the moment so that the variable names are
consistant throughout this page.

Before I go and try changing my code, I wouldn't mind some advise on the
next problem I foresee! As I think I may have mentioned above, I'm using
the following method:

PAGE 1 (search.asp) - Creates iist of tables and makes SQL Query
accordingly. Saves this to a cookies
PAGE 2 (results.asp) - Loads the cookie and uses the information from
the cookie as the SELECT statement when creating the recordset.

So far, so good. But now a new problem.... I tried writing the select
statement to a cookie with the following code:

<% Response.Cookies("ImailSearchString") = objSlct %>

Cookies object, ASP 0104 (0x80070057)
Operation not Allowed
/imail/TMP2ir245khox.asp, line 41

I suspect this may simply be because my select statement is so long but
I don't know... any ideas? If so then I suppose the answer will be to
simply make it so that the SELECT statement is created on the second
page. Any suggestions?

Cheers!
Stu.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

Nearly had it!

I take it then that there is a limit to how long a SELECT statement is?
I nearly had it working. I gave up on the two pages communicating with a
cookie idea and put everything back into one page as I originally had
it. Used ADOX to get the table names, wrote them into a SELECT statement
which was stored as a variable, and then created a recordset using the
SELECT statement stored in the variable.

I think it may have worked, but there must be over a hundred tables in
the database, and I think the SELECT statement must have therefore been
too long as I had an error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Query is too complex.
/imail/TMP3o63a5kj6l.asp, line 41


Back to the drawing board I suppose!

Thanks for all the help so far. I've not given up yet ;)

Cheers,
Stu.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Re: Viewing the contents of two tables using ASP and MS Access

"Stuart Clark" wrote in message
news:O70F#MdsEHA.624@TK2MSFTNGP09.phx.gbl...
>
>
> Nearly had it!
>
> I take it then that there is a limit to how long a SELECT statement is?
> I nearly had it working. I gave up on the two pages communicating with a
> cookie idea and put everything back into one page as I originally had
> it. Used ADOX to get the table names, wrote them into a SELECT statement
> which was stored as a variable, and then created a recordset using the
> SELECT statement stored in the variable.
>
> I think it may have worked, but there must be over a hundred tables in
> the database, and I think the SELECT statement must have therefore been
> too long as I had an error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Query is too complex.
> /imail/TMP3o63a5kj6l.asp, line 41
>
>
> Back to the drawing board I suppose!
>
> Thanks for all the help so far. I've not given up yet ;)
>
> Cheers,
> Stu.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Perhaps this link will help:

http://dotnetjunkies.com/Newsgroups/microsoft.public.dotnet. framework.odbcne
t/2004/3/1/4789.aspx

Re: Viewing the contents of two tables using ASP and MS Access

Stuart Clark wrote:
> Nearly had it!
>
> I take it then that there is a limit to how long a SELECT statement
> is?

Sort of. There are limits to certain features in Jet Queries. Let me check
online help ...

Number of enforced relationships 32 per table minus the number of indexes
that are on the table for fields or combinations of fields that are not
involved in relationships

Number of tables in a query 32

[me] I am not really sure if this refers to the number of tables joined in a
single FROM clause, or whether it applies to the number of union selects as
well. I suspect the latter may be the case.

Number of fields in a recordset 255

[me] How many fields are you returning? If more than 255, you are going to
have to forget about using selstart (select *) and provide column lists
instead (you really should be doing this anyways -
http://www.aspfaq.com/show.asp?id=2096)

Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99

Number of characters in an SQL statement approximately 64,000

[me] Is your sql statement longer than 64000 characters???


> I nearly had it working. I gave up on the two pages communicating
> with a cookie idea and put everything back into one page as I
> originally had it. Used ADOX to get the table names, wrote them into
> a SELECT statement which was stored as a variable, and then created a
> recordset using the SELECT statement stored in the variable.
>
> I think it may have worked, but there must be over a hundred tables in

Ridiculous!!!

> the database, and I think the SELECT statement must have therefore
> been too long as I had an error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Query is too complex.
> /imail/TMP3o63a5kj6l.asp, line 41
>
>
> Back to the drawing board I suppose!
>

I f you response.write the sql statement and paste it into the SQL View of
the Access Query Builder, does it run without error? If not, what do you
have to do to it to make it work? That will give you your answer.

If it is the length or number of tables that is causing the problem, then
you are going to have to put a counter in your loop, using it to create as
many union queries as are needed to retrieve the data you need, retrieveing
the multiple recordsets and combining them when you display them.

An alternative you may wish to consider:
If you do not need upt-to-the-minute data to display on this page, you
should create a scheduled job that combines the data from all your tables
into a single table. I would not use ASP for this as the operation is likely
to take longer than a typical ASP operation. Instead, a VB application could
be created to do this. Or an Access VBA procedure could do it. Use the
Windows scheduler to run it at the periodic intervals you define. I would
probably put the combined data into its own database, so reporting
activities can take place without interfering with data modification
activities.

This article
http://www.aspfaq.com/show.asp?id=2143
shows how to schedule a script to run periodically. Again, my preference
would be to use compiled code for this task instead of script, but it can be
done with script.


Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Viewing the contents of two tables using ASP and MS Access

Stop using ODBC/DSN. This might not solve the problem, but it will at
least make your application faster. Please read the following links.

http://www.aspfaq.com/show.asp?id=2126

http://msdn.microsoft.com/library/en-us/ado270/htm/ado_depre cated_components.asp

Stuart Clark wrote:

> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Query is too complex.
> /imail/TMP3o63a5kj6l.asp, line 41

--
Please do not contact me directly or ask me to contact you directly for
assistance.

If your question is worth asking, it's worth posting.

If it’s not worth posting you should have done a search on
http://www.google.com/ http://www.google.com/grphp?hl=en&tab=wg&q= or
http://news.google.com/froogle?hl=en&tab=nf&ned=us&q= before wasting our
time.