Displaying the results of two MS Access tables using ASP

[Stuart Clark]

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:

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

[Eric Schittlipz]

"Stuart Clark" <st**********@Excite.com> wrote in message
news:c7**************************@posting.google.c om...

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:

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

This isn't really the place to ask about ASP. Even if you use an mdb file
to store the data, you are not using Microsoft Access. In fact you may not
even have Access installed. If you do though, you could paste the query
below into the SQL view of a new query:

SELECT * FROM domain1_co_uk, domain2_com

and you will see the same result. ie the problem is not with ASP or Access
but in the SQL statement. I expect you need a join between the two tables
but you have not given any indication of the structure or the results you
expected.

[Stuart Clark]

Thanks for the advise! My apologies if I am posting in the wrong place -
I don't really know where else to try!

So if you don't mind answering this, what would be the correct SQL
statement in this scenareo?

All I want to do is display everything contained in two tables;

domain1_co_uk
domain2_com

Both tables have exactly the same structure, with the same fields/names
etc, just different information.

All I want it to do is list first one table, and then the other.

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

[Andrew Virnuls]

"Stuart Clark" <st**********@excite.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thanks for the advise! My apologies if I am posting in the wrong place -
I don't really know where else to try!

So if you don't mind answering this, what would be the correct SQL
statement in this scenareo?

All I want to do is display everything contained in two tables;

domain1_co_uk
domain2_com

Both tables have exactly the same structure, with the same fields/names
etc, just different information.

All I want it to do is list first one table, and then the other.

It sounds like you need a UNION query, e.g.

SELECT forename, surname FROM domain1_co_uk
UNION ALL
SELECT forename, surname FROM domain2_com;

You can also stick an "ORDER BY surname, forename" before the semi-colon if
you want both lists sorted together.

The SQL doesn't need to be on separate lines - I just split it to make it
easier to read.

Andrew

[Stuart Clark]

That's fantastic! Thanks for the advise! I shall go and try that now!

Thanks again!

Stu.

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

[Mal Reeve]

Search in ACCESS Help for UNION query...

so long as the field names are the same they that will do what you want.
something like

SELECT * from domain1_co_uk
union
SELECT * from domain2_com
where....
order by....etc

HTH
Mal.
"Stuart Clark" <st**********@excite.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

Thanks for the advise! My apologies if I am posting in the wrong place -
I don't really know where else to try!

So if you don't mind answering this, what would be the correct SQL
statement in this scenareo?

All I want to do is display everything contained in two tables;

domain1_co_uk
domain2_com

Both tables have exactly the same structure, with the same fields/names
etc, just different information.

All I want it to do is list first one table, and then the other.

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

[Stuart Clark]

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!