Union Queries In ASP?

[MDW]

I've never tried this before, and maybe it doesn't work. I'm building a very simple page for someone else's Web site. It's a sort of schedule, and consists of three tables, each of which has two columns. The columns either represent a person, or the date upon which an event occurs. The tables are tblArrivals, tblDepartures, and tblEvents

All I want to do is march down a couple of dates and see if ANYTHING is happening on that date - if someone is arriving, leaving, or if there is a scheduled activity. Here is my code -

strSQL = "SELECT [Who] & "" Arrives"" AS Event, [ArriveDate] AS TheDate FROM tblArrivals UNION SELECT [Who] & "" Leaves"" AS Event, [DepartDate] As TheDate FROM tblDepartures UNION SELECT ""<b>Event: "" & [What] & ""</b>"" As Event,[EventDate] As TheDate FROM tblEvents WHERE TheDate=#07/25/2004#
Set objRS = objConn.Execute(strSQL

When I try to create the recordset, I get the following error

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

I've verified that the column names are correct. I've tried it both with ailiasing only the first set of columns, and with all the columns. I've tried it without the brackets around the column names. Nothing works

Is this even possible

[Chris Hohmann]

"MDW" <anonymous@discussions.microsoft.com> wrote in message
news:F1C4305F-A7C2-45AD-9938-AF1680504673@microsoft.com...

> I've never tried this before, and maybe it doesn't work. I'm building

a very simple page for someone else's Web site. It's a sort of schedule,
and consists of three tables, each of which has two columns. The columns
either represent a person, or the date upon which an event occurs. The
tables are tblArrivals, tblDepartures, and tblEvents.

>
> All I want to do is march down a couple of dates and see if ANYTHING

is happening on that date - if someone is arriving, leaving, or if there
is a scheduled activity. Here is my code -

>
> strSQL = "SELECT [Who] & "" Arrives"" AS Event, [ArriveDate] AS

TheDate FROM tblArrivals UNION SELECT [Who] & "" Leaves"" AS Event,
[DepartDate] As TheDate FROM tblDepartures UNION SELECT ""<b>Event: "" &
[What] & ""</b>"" As Event,[EventDate] As TheDate FROM tblEvents WHERE
TheDate=#07/25/2004#"

> Set objRS = objConn.Execute(strSQL)
>
> When I try to create the recordset, I get the following error:
>
> Microsoft JET Database Engine error '80040e10'
>
> No value given for one or more required parameters.
>
>
> I've verified that the column names are correct. I've tried it both

with ailiasing only the first set of columns, and with all the columns.
I've tried it without the brackets around the column names. Nothing
works.

>
> Is this even possible?

Try this:
1. Add the following lines to your ASP code right after you set strSQL

Response.Write strSQL
Response.End

2. Cut and paste the results into the Microsoft Access Query Builder.
3. Run the query in Access to determine which expression is being
interpreted as a parameter reference. It's likely a misspelling of some
sort.
4. If you are unable to diagnose the problem yourself, post the results
of the Response.Write here along with sample data and data definition
language, i.e.. CREATE TABLE...

HTH
-Chris Hohmann

[roger]

"MDW" wrote

> I've verified that the column names are correct.

To verify that the column names are correct - put

response.write strSQL

before

Set objRS = objConn.Execute(strSQL)

Open the page, copy the output and paste
into a query in the database.

Run this query. The error message should give you a clue.

It's best to do this whenever you have a problem with a query.


In this case probably...

TheDate=#07/25/2004#

should be...

EventDate=#07/25/2004#

but there may be other problems.


--
roger