Pass query variable with different field names

[Mark]

I am using FP2003 and have created an ASP page that
displays and selects records from a sql database using a
dropdown list. (thanks spiderwoman) I retrieve the
client_key from the 1st query (clients table) and pass it
to a 2nd ASP page via POST. I then want to select records
from a different table (hardware) using the client_key
value and display them, however the field names joining
the tables are different;

clients.client_key = hardware.ref_key

I have the 2nd page working in that it displays all of
the records in hardware, but I only want to display the
records that the ref_key = client_key from the first ASP
page.

 

[MSFT]

I hope this gets through. This is my first post using an add-in I just downloaded.

You just need to set up the SQL to expect the QueryString. Launch the Database Results wizard and click to step 3 (I think) and click on More Options. Then go into the Criteria and Add a new item, giving it the Value that is contained in the URL being passed to the page.

I hope that makes sense and that this gets through to you.

-John



nntp://msnews.microsoft.com/microsoft.public.frontpage.client/<[email protected]>

I am using FP2003 and have created an ASP page that
displays and selects records from a sql database using a
dropdown list. (thanks spiderwoman) I retrieve the
client_key from the 1st query (clients table) and pass it
to a 2nd ASP page via POST. I then want to select records
from a different table (hardware) using the client_key
value and display them, however the field names joining
the tables are different;

clients.client_key = hardware.ref_key

I have the 2nd page working in that it displays all of
the records in hardware, but I only want to display the
records that the ref_key = client_key from the first ASP
page.


[microsoft.public.frontpage.client]

 

[Kathleen Anderson [MVP - FP]]

Mark:
Try this:

SELECT * FROM hardware WHERE (hardware.ref_key = ::client_key:

 

[Mark]

I still get too many records. Though I noticed the
records displayed do not have a value in ref_key, if that
is a clue.

 

[Kathleen Anderson [MVP - FP]]

In the picklist page, what field did you create the hyperlink with?

 

[Jon]

this sounds like a join to me. Something like
select *
from clients c inner join hardware h
on c.client_key = h.ref_key
where c.client_key = ::client_key::

Not entirely clear though - I think we need more info on the database schema

Jon
Microsoft MVP - FP

 

[Kathleen Anderson [MVP - FP]]

I tried this last night with the fpwind.mdb database using the Categories
and Products tables and it worked, although in that case, the field name
(CategoryID) is the same in both tables.

The SQL on the display page is: SELECT * FROM Products WHERE (CategoryID =
::CategoryID:


--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

 

[Mark]

I do not understand your question, re: hyperlinks..

I created a few different queries and the results are
different for each.

SELECT * from NetItem INNER JOIN Clients ON
NetItem.Ref_Client = Clients.Client_Key WHERE
NetItem.Ref_Client = '::Client_Key::'

No Records returned
-----
SELECT * from NetItem INNER JOIN Clients ON
NetItem.Ref_Client = Clients.Client_Key

Returns NetItem records that have a numeric value in
Ref_Client, no records without a numeric value are
displayed.
-----
SELECT * from NetItem WHERE NetItem.Ref_Client
= '::Client_Key::'

Returns NetItem records that have have a blank value in
Ref_Client, no records with a numeric value are displayed.

 

[Mark]

Problem solved... I created a working test using NWind
and went from there. Thanks for the hints, it helped.
In hindsight I realized I did not say that I was
displaying the lastname on the first query page and
wanted to pass the associated client_key to the second
page as input to the select criteria from the other table.

Page1 - Display-> LastName; Submit-> Client_Key on the
4th window of the DRW..

Page2 - SELECT * from NetItem WHERE NetItem.Ref_Client
= '::Client_Key::'