Pass parameter to nested query

Hello,

I'm very, very new to ASP and am trying to run a travel and absence database
for a small company on the internet. It will show as column headings 01 Tue,
02 Wed, 03 Thu..... etc for one month. Row headings will be the employee
names and the cells will contain the locations where they travel to.

I created 3 nested queries in Access and if I run the 3rd Query (which runs
the second, which runs the first), it works fine and does exactly what I
want. My second query is a CrossTab query.

The first query has a date criteria which works great if I set the criteria
for it to give me current month or current year - I get the results I want
in my grid/table on my ASP page.

The problem I am having is that I want my users to be able to say which
month and which year they want to view in the grid (which will show only one
month at any one time).

The question is - how can I pass a parameter from my ASP page to the first
query in the nest saying something like "02" (for February) and "2005" (the
year).

This is the VBScript I used to run the 3 nested queries

sqlCurrentEvents="SELECT * FROM qryEmpEventFinal"

Query 1 = qryEmpEvent (this is the query where I want to
filter the dates where I need to pass a parameter or variable)
Query 2 = qryEmpEventCrossTab (crosstab query)
Query 3 = qryEmpEventFinal (does a further filter to give final
result)

I've been tearing my hair out over this for weeks.. hope someone can help.
Laura TD

Re: Pass parameter to nested query

Firstly, don't use "Select * ..."

http://aspfaq.com/show.asp?id=2096

Second, look into the "WHERE" clause;

sqlCurrentEvents="SELECT * FROM qryEmpEventFinal WHERE = '" &
strQueryString & "'"

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"laura" wrote in message
news:OeECuDRFFHA.3888@TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm very, very new to ASP and am trying to run a travel and absence
database
> for a small company on the internet. It will show as column headings 01
Tue,
> 02 Wed, 03 Thu..... etc for one month. Row headings will be the employee
> names and the cells will contain the locations where they travel to.
>
> I created 3 nested queries in Access and if I run the 3rd Query (which
runs
> the second, which runs the first), it works fine and does exactly what I
> want. My second query is a CrossTab query.
>
> The first query has a date criteria which works great if I set the
criteria
> for it to give me current month or current year - I get the results I want
> in my grid/table on my ASP page.
>
> The problem I am having is that I want my users to be able to say which
> month and which year they want to view in the grid (which will show only
one
> month at any one time).
>
> The question is - how can I pass a parameter from my ASP page to the first
> query in the nest saying something like "02" (for February) and "2005"
(the
> year).
>
> This is the VBScript I used to run the 3 nested queries
>
> sqlCurrentEvents="SELECT * FROM qryEmpEventFinal"
>
> Query 1 = qryEmpEvent (this is the query where I want to
> filter the dates where I need to pass a parameter or variable)
> Query 2 = qryEmpEventCrossTab (crosstab query)
> Query 3 = qryEmpEventFinal (does a further filter to give final
> result)
>
> I've been tearing my hair out over this for weeks.. hope someone can help.
> Laura TD
>
>
>

Re: Pass parameter to nested query

Hi Steven,

I don't think this is going to help. The three queries were created using
the Query Design Window and when I put the last of the 3 queries into my ASP
page - it works fine. Remember, if I have the criteria already imbedded into
the first query, i.e., current month and current year - it works great - I
get my grid perfectly displayed. The problem I have is how do I pass a month
and year of my choice to the 1st query? I can do it if I am not doing ASP,
i.e., if I have a form that calls the query - it has two text boxes or combo
boxes and the user selects a month and year from a dropdown box.. then the
query would say something like [forms]![frmTravel].[combo1]. That works
within Access... but how can I emulate that from my ASP page. I will be
doing something similar.. my users will choose a month and year from drop
down boxes.

I cannot say what the column headings are or how many of them there will be
(28, 29, 30, 31) because they will change according to how many days there
are in the month and they will have names like 01 Tue, 02 Wed... As I said,
the query works if I call it from my ASP page with the Select statement...

In your example - "WHERE =.... "... would only refer to the
third query in the nest.. i.e., the one that calls the other two..
qryEmpEventFinal.

Laura


"Steven Burn" wrote in message
news:%23YPnGLRFFHA.3032@TK2MSFTNGP12.phx.gbl...
> Firstly, don't use "Select * ..."
>
> http://aspfaq.com/show.asp?id=2096
>
> Second, look into the "WHERE" clause;
>
> sqlCurrentEvents="SELECT * FROM qryEmpEventFinal WHERE = '" &
> strQueryString & "'"
>
> --
> Regards
>
> Steven Burn
> Ur I.T. Mate Group
> www.it-mate.co.uk
>
> Keeping it FREE!
>
> "laura" wrote in message
> news:OeECuDRFFHA.3888@TK2MSFTNGP12.phx.gbl...
>> Hello,
>>
>> I'm very, very new to ASP and am trying to run a travel and absence
> database
>> for a small company on the internet. It will show as column headings 01
> Tue,
>> 02 Wed, 03 Thu..... etc for one month. Row headings will be the employee
>> names and the cells will contain the locations where they travel to.
>>
>> I created 3 nested queries in Access and if I run the 3rd Query (which
> runs
>> the second, which runs the first), it works fine and does exactly what I
>> want. My second query is a CrossTab query.
>>
>> The first query has a date criteria which works great if I set the
> criteria
>> for it to give me current month or current year - I get the results I
>> want
>> in my grid/table on my ASP page.
>>
>> The problem I am having is that I want my users to be able to say which
>> month and which year they want to view in the grid (which will show only
> one
>> month at any one time).
>>
>> The question is - how can I pass a parameter from my ASP page to the
>> first
>> query in the nest saying something like "02" (for February) and "2005"
> (the
>> year).
>>
>> This is the VBScript I used to run the 3 nested queries
>>
>> sqlCurrentEvents="SELECT * FROM qryEmpEventFinal"
>>
>> Query 1 = qryEmpEvent (this is the query where I want to
>> filter the dates where I need to pass a parameter or variable)
>> Query 2 = qryEmpEventCrossTab (crosstab query)
>> Query 3 = qryEmpEventFinal (does a further filter to give final
>> result)
>>
>> I've been tearing my hair out over this for weeks.. hope someone can
>> help.
>> Laura TD
>>
>>
>>
>
>

Re: Pass parameter to nested query

laura wrote:
> Hello,
>
> I'm very, very new to ASP and am trying to run a travel and absence
> database for a small company on the internet. It will show as column
> headings 01 Tue, 02 Wed, 03 Thu..... etc for one month. Row headings
> will be the employee names and the cells will contain the locations
> where they travel to.
>
> I created 3 nested queries in Access and if I run the 3rd Query
> (which runs the second, which runs the first), it works fine and does
> exactly what I want. My second query is a CrossTab query.
>
> The first query has a date criteria which works great if I set the
> criteria for it to give me current month or current year - I get the
> results I want in my grid/table on my ASP page.
>
> The problem I am having is that I want my users to be able to say
> which month and which year they want to view in the grid (which will
> show only one month at any one time).
>
> The question is - how can I pass a parameter from my ASP page to the
> first query in the nest saying something like "02" (for February) and
> "2005" (the year).
>
> This is the VBScript I used to run the 3 nested queries
>
> sqlCurrentEvents="SELECT * FROM qryEmpEventFinal"
>
> Query 1 = qryEmpEvent (this is the query where I want
> to filter the dates where I need to pass a parameter or variable)
> Query 2 = qryEmpEventCrossTab (crosstab query)
> Query 3 = qryEmpEventFinal (does a further filter to give
> final result)
>
> I've been tearing my hair out over this for weeks.. hope someone can
> help. Laura TD


Let's put some sql into these queries (I don't know your field names, so I
will use fldMonth and fldYear to represent fields in your innermost query),
using parameters in place of your hard-coded values:

qryEmpEvent:
select from first_table where fldmonth = [pMonth]
and fldYear = [pYear]

If you run this query in Access, Access will not find fields called pMonth
or pYear, and therefore will treat them as parameters, prompting you for
values. Save the query.

qryEmpEventCrossTab
Transform .... FROM qryEmpEvent

If you run this query in Access, the same thing will happen: you will
prompted for parameter values.

qryEmpEventFinal
select ... from qryEmpEventCrossTab where ...

Again, when you test this in Access, you will be prompted.

In ASP:
dim cn, rs
set cn=createobject("adodb.connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= p:\ath\to\database.mdb"
set rs=createobject("adodb.recordset")
cn.qryEmpEventCrossTab "02", "2005", rs
if not rs.eof then
....


Here's more info:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

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: Pass parameter to nested query

Dear Bob,

I am so grateful to you - and I am sure your solution gets me further than I
have been able to get with this. I understood it and implemented it and the
query began to work, my grid began to display on my screen, but only the
headings. I have a feeling the asp cannot keep passing the variables, or
parameters back to the query each time it needs to build a row - or
something like that because that is where it stops and I get the error
message. I just don't know enough about ASP yet to be able to figure this
out. It's so frustrating.

Maybe someone has a better solution or idea to this.

I am trying to build a travel database for about 200 employees. I want them
to be able to log onto the internet and see a grid, defaulting to the
current month and year showing a list of employees as row headings and the
days of the week/month "01 Tue", "02 Wed".. etc as column headings and
inside the grid, naturally, the locations they are travelling to.

I've gone about it in the only way I know how, which might be slightly more
complicated than it needs to be.

My tables:
tblEmployees - Names and Employee No.
tblEvents - Dates, locations, and Employee No. of each journey
taken
tblDays - A database that will be filled with 1 year's worth of
dates, i.e., one field, 365 records (01/01/05.. etc.)

I created the tblDays so I could pick out whatever month the user wanted -
so that I could make a pivot table and alter them into column headings.

I then created 3 queries that are nested. The first query puts together the
tblDays,tblEvent and tblEmployee for the current month to begin with.
The 2nd Query pivots the table so that the days become column headings -
this is fine, but I get one blank record which needs to be eliminated with
the 3rd Query.

It works in Access and also initially on my ASP page when defaulting to
current month.. I can easily pass my parameters in many ways, such as drop
down boxes on a form. I just cannot pass parameters from my ASP page for
different months - that is the problem.

Thanks
Laura



"Bob Barrows [MVP]" wrote in message
news:OWG0LJSFFHA.3492@TK2MSFTNGP12.phx.gbl...
> laura wrote:
>> Hello,
>>
>> I'm very, very new to ASP and am trying to run a travel and absence
>> database for a small company on the internet. It will show as column
>> headings 01 Tue, 02 Wed, 03 Thu..... etc for one month. Row headings
>> will be the employee names and the cells will contain the locations
>> where they travel to.
>>
>> I created 3 nested queries in Access and if I run the 3rd Query
>> (which runs the second, which runs the first), it works fine and does
>> exactly what I want. My second query is a CrossTab query.
>>
>> The first query has a date criteria which works great if I set the
>> criteria for it to give me current month or current year - I get the
>> results I want in my grid/table on my ASP page.
>>
>> The problem I am having is that I want my users to be able to say
>> which month and which year they want to view in the grid (which will
>> show only one month at any one time).
>>
>> The question is - how can I pass a parameter from my ASP page to the
>> first query in the nest saying something like "02" (for February) and
>> "2005" (the year).
>>
>> This is the VBScript I used to run the 3 nested queries
>>
>> sqlCurrentEvents="SELECT * FROM qryEmpEventFinal"
>>
>> Query 1 = qryEmpEvent (this is the query where I want
>> to filter the dates where I need to pass a parameter or variable)
>> Query 2 = qryEmpEventCrossTab (crosstab query)
>> Query 3 = qryEmpEventFinal (does a further filter to give
>> final result)
>>
>> I've been tearing my hair out over this for weeks.. hope someone can
>> help. Laura TD
>
>
> Let's put some sql into these queries (I don't know your field names, so I
> will use fldMonth and fldYear to represent fields in your innermost
> query),
> using parameters in place of your hard-coded values:
>
> qryEmpEvent:
> select from first_table where fldmonth = [pMonth]
> and fldYear = [pYear]
>
> If you run this query in Access, Access will not find fields called pMonth
> or pYear, and therefore will treat them as parameters, prompting you for
> values. Save the query.
>
> qryEmpEventCrossTab
> Transform .... FROM qryEmpEvent
>
> If you run this query in Access, the same thing will happen: you will
> prompted for parameter values.
>
> qryEmpEventFinal
> select ... from qryEmpEventCrossTab where ...
>
> Again, when you test this in Access, you will be prompted.
>
> In ASP:
> dim cn, rs
> set cn=createobject("adodb.connection")
> cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source= p:\ath\to\database.mdb"
> set rs=createobject("adodb.recordset")
> cn.qryEmpEventCrossTab "02", "2005", rs
> if not rs.eof then
> ...
>
>
> Here's more info:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl
>
> http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
>
> http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
>
> 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: Pass parameter to nested query

laura wrote:
> Dear Bob,
>
> I am so grateful to you - and I am sure your solution gets me further
> than I have been able to get with this. I understood it and implemented it
> and the query began to work, my grid began to display on my screen, but
> only
> the headings.

You're telling me everything except what I need to know. A database is a
database. A table is a table. A query is a query. Why you've built them is
irrelevant. The only relevant statement in all the text that I snipped is
"the query works in Access when I test it".

So, it's clear to me that you are having a problem with the code used to
write the results from the query to the response. Show us the snippet of
code used to read the data from the recordset and write it to the response.
If you have hundreds of lines of code, of course, you need to break out the
few lines that are relevant to your issue ( don't expect us to plow
through a bunch of html simply to find your looping code and
response.writes)

Better yet: You may find it helpful to back away. Create a new page that
simply opens a recordset on your query using hard-coded parameter values and
writes the data contained in it to the response: no html. Something like
this quick code to write a table containing your data to the screen
(hard-code your parameter values for now. Worry about retrieving them from a
form later):

<%
set cn=createobject("adodb.connection")
cn.open ""
set rs=server.createobject("adodb.recordset")
cn.NameOfQuery "02", 2005, rs
if not rs.eof then 'eof is true if the recordset is empty
sHTML=rs.GetString(2,,"","")
rs.close:set rs = nothing
cn.close: set cn=nothing
end if
if len(sHTML) > 0 then
response.write "

Re: Pass parameter to nested query

Dear Bob,

If you're still keeping an eye on this thread, I thought I would write to
say I am sorry I didn't get back with the snippet of code, but I got
involved in some other work and had to put this 'problem' aside for a while.
I took your advice and yesterday started to cut down the ASP page so that I
would be able to give you the code that was causing me the problem and in
doing so, I may have come up with a solution. I think the problem was in a
statement that said

sqlCurrentEvents = ("SELECT * FROM (TRANSFORM.... etc. ")

The query was originally created in Access using the Query Design window..
as series of 3 nested queries and it worked if I called it from my ASP page.
The problem was that the deepest of the nested queries had a filter for
dates (current month and current year) which worked fine also, but I needed
to be able to allow users to enter their own choice of month and year and
the only way I think I could do that was to put the full sql statement into
my ASP page and pass a variable to it as I didn't know how to pass a
parameter through to the query from the ASP page.

The query worked if I nested just two of the queries, in other words, the
final SELECT (at the beginning of the whole statement) was the one that
caused it to not work and I needed to add it as I had to eliminate a blank
record after the other two queries ran. I kept getting an error message
saying that there was an error in the FROM clause. I decided to filter for
the blank record after the query was built, within my grid - in other words,
when building table rows I stipulated that

sqlCurrentEvents("fullname") > " " and it suddenly all worked.

Here is the sql statement that fell down with the SELECT * FROM statement. I
could not enumerate (is that the right word) the fields for the column
headings as I did not know if there would be 28 for Feb, or 30, 31 for other
months, so I had to use the "*".

Anyway, this is really by way of explanation and I am sorry to have taken
your time, but I think I have cracked it now. Here is the code, first the
one that works, and second the one that does not.

This works:-

sqlCurrentEvents = ("TRANSFORM First(Event) AS FirstOfEvent " _
& "SELECT fullname " _
& "FROM (SELECT tblAllDates.theDay, tblEvent.EventDate, tblEvent.Event,
[sname] & ' ' & [fname] AS fullname " _
& "FROM tblAllDates LEFT JOIN (tblEvent LEFT JOIN tblEmployee ON
tblEvent.EmployeeID = tblEmployee.EmployeeID) ON tblAllDates.theDay =
tblEvent.EventDate " _
& "WHERE (((Month([theDay]))= " & theMonth & ") AND ((Year([theDay]))= "
& theYear & ")) " _
& "ORDER BY tblAllDates.theDay) " _
& "GROUP BY fullname " _
& "ORDER BY Format([theDay],'dd') & ' ' & Format([theDay],'ddd') " _
& "PIVOT Format([theDay],'dd') & ' ' & Format([theDay],'ddd')")


This does not work

sqlCurrentEvents = ("SELECT * FROM (TRANSFORM First(Event) AS
FirstOfEvent " _
& "SELECT fullname " _
& "FROM (SELECT tblAllDates.theDay, tblEvent.EventDate, tblEvent.Event,
[sname] & ' ' & [fname] AS fullname " _
& "FROM tblAllDates LEFT JOIN (tblEvent LEFT JOIN tblEmployee ON
tblEvent.EmployeeID = tblEmployee.EmployeeID) ON tblAllDates.theDay =
tblEvent.EventDate " _
& "WHERE (((Month([theDay]))= " & theMonth & ") AND ((Year([theDay]))= "
& theYear & ")) " _
& "ORDER BY tblAllDates.theDay) " _
& "GROUP BY fullname " _
& "ORDER BY Format([theDay],'dd') & ' ' & Format([theDay],'ddd') " _
& "PIVOT Format([theDay],'dd') & ' ' & Format([theDay],'ddd') " _
& "WHERE (((fullname)>''))")

it won't even work if I remove the WHERE clause - problem is probably in the
SELECT statement.

Anyway, thanks for your help - I think that breaking the code down did help
me to find a solution.

Laura TD



"Bob Barrows [MVP]" wrote in message
news:%23XKjUk1FFHA.1396@tk2msftngp13.phx.gbl...
> laura wrote:
>> Dear Bob,
>>
>> I am so grateful to you - and I am sure your solution gets me further
>> than I have been able to get with this. I understood it and implemented
>> it
>> and the query began to work, my grid began to display on my screen, but
>> only
>> the headings.
>
> You're telling me everything except what I need to know. A database is a
> database. A table is a table. A query is a query. Why you've built them is
> irrelevant. The only relevant statement in all the text that I snipped is
> "the query works in Access when I test it".
>
> So, it's clear to me that you are having a problem with the code used to
> write the results from the query to the response. Show us the snippet of
> code used to read the data from the recordset and write it to the
> response. If you have hundreds of lines of code, of course, you need to
> break out the few lines that are relevant to your issue ( don't expect
> us to plow through a bunch of html simply to find your looping code and
> response.writes)
>
> Better yet: You may find it helpful to back away. Create a new page that
> simply opens a recordset on your query using hard-coded parameter values
> and writes the data contained in it to the response: no html. Something
> like this quick code to write a table containing your data to the screen
> (hard-code your parameter values for now. Worry about retrieving them from
> a form later):
>
> <%
> set cn=createobject("adodb.connection")
> cn.open ""
> set rs=server.createobject("adodb.recordset")
> cn.NameOfQuery "02", 2005, rs
> if not rs.eof then 'eof is true if the recordset is empty
> sHTML=rs.GetString(2,,"","")
> rs.close:set rs = nothing
> cn.close: set cn=nothing
> end if
> if len(sHTML) > 0 then
> response.write "