Query not working in FP

[Mr. Know Nothing]

I have a query that is working in Access but it doesn't seem to want to work
in FP. I know I have everything working correctly as the query works in
access, and if I have a simply query (Select * from CallLog) in FP i get the
correct results for that query. The query that i'm trying to get to work is:

SELECT CallLog.* FROM CallLog, Customer where
Customer.CustomerID=CallLog.CustomerID AND Customer.Fname='::Fname::'

Is there anything that anyones can tell me to make this work??

Thanks in advance.

 

[Stefan B Rusynko]

You appear to be trying to use data from 2 tables (CallLog & Customer)
You need to study Joins
See http://www.aspfaq.com/show.asp?id=2533




|I have a query that is working in Access but it doesn't seem to want to work
| in FP. I know I have everything working correctly as the query works in
| access, and if I have a simply query (Select * from CallLog) in FP i get the
| correct results for that query. The query that i'm trying to get to work is:
|
| SELECT CallLog.* FROM CallLog, Customer where
| Customer.CustomerID=CallLog.CustomerID AND Customer.Fname='::Fname::'
|
| Is there anything that anyones can tell me to make this work??
|
| Thanks in advance.

 

[Mr. Know Nothing]

Stefan, I have read up on joins and I have made the following query:

SELECT a.* FROM CallLog a INNER JOIN Customer b ON a.customerID =
b.customerID WHERE b.Fname='marc';

in access the result of the query using joins is the same as my old query:

SELECT CallLog.* FROM CallLog, Customer WHERE
Customer.CustomerID=CallLog.CustomerID AND Customer.Fname='marc';

Now for some reason when i try eighter in FP they do not want to work. I
hvae changed my page a little bit and tested through FP if i had my database
messed up or something but everything seem in place. The only thing i am
changing from the query you are seeing is ... Fname='marc' in FP is
Fname=::Fname::

You can look at the page yourself if it helps. (ujr.ca/test/CallLog.asp)

Thanks

 

[Stefan B Rusynko]

You seem to have it working now at http://ujr.ca/test/CallLog.asp
I get a record when I type in marc
FYI
The code is not visible since it is server side processed




| Stefan, I have read up on joins and I have made the following query:
|
| SELECT a.* FROM CallLog a INNER JOIN Customer b ON a.customerID =
| b.customerID WHERE b.Fname='marc';
|
| in access the result of the query using joins is the same as my old query:
|
| SELECT CallLog.* FROM CallLog, Customer WHERE
| Customer.CustomerID=CallLog.CustomerID AND Customer.Fname='marc';
|
| Now for some reason when i try eighter in FP they do not want to work. I
| hvae changed my page a little bit and tested through FP if i had my database
| messed up or something but everything seem in place. The only thing i am
| changing from the query you are seeing is ... Fname='marc' in FP is
| Fname=::Fname::
|
| You can look at the page yourself if it helps. (ujr.ca/test/CallLog.asp)
|
| Thanks
|
| "Stefan B Rusynko" wrote:
|
| > You appear to be trying to use data from 2 tables (CallLog & Customer)
| > You need to study Joins
| > See http://www.aspfaq.com/show.asp?id=2533
| >
| > --
|

 

[Mr. Know Nothing]

Yes, I know that one is working but the second one is not working.
The first on works as it is a simple query. (select * from Customers WHERE
fname=::fname:

The second one is: (SELECT a.* FROM CallLog a INNER JOIN Customer b ON
a.customerID = b.customerID WHERE b.Fname=::Fname::
and that one doesn't want to work.

Any ideas??

 

[Stefan B Rusynko]

I think you will find this example clearer for what you are trying to do
See http://www.learnasp.com/learn/dbjoin.asp




| Yes, I know that one is working but the second one is not working.
| The first on works as it is a simple query. (select * from Customers WHERE
| fname=::fname:
|
| The second one is: (SELECT a.* FROM CallLog a INNER JOIN Customer b ON
| a.customerID = b.customerID WHERE b.Fname=::Fname::
| and that one doesn't want to work.
|
| Any ideas??
|
|
| "Stefan B Rusynko" wrote:
|
| > You seem to have it working now at http://ujr.ca/test/CallLog.asp
| > I get a record when I type in marc
| > FYI
| > The code is not visible since it is server side processed
| >
| > --
| >
|

 

[Mr. Know Nothing]

Stafan, I have looked at that site and if you notice the query they use for
an example
SELECT Customer.CustomerName, Sales.SalesAmount FROM Customer , Sales
where customer.CustomerID = Sales.CustomerID

is very similar to mine

SELECT CallLog.* FROM CallLog, Customer where
Customer.CustomerID=CallLog.CustomerID AND Customer.Fname='::Fname::'

any reason why my query doesn't want to work in Frontpage?? I have tested
the query in Access and it works fine.

 

[Mr. Know Nothing]

I can make a simple query work but the second i try to make one that is
complex FP doesn't allow me to. when i try to get this query to work i get
the following error.

Query:

SELECT a.*
FROM CallLog a INNER JOIN Customer b
ON a.customerID = b.customerID
WHERE b.Fname=::Fname::;

Error:

Server error: Unable to retrieve schema information from the query:

SELECT a.* FROM CallLog a INNER JOIN Customer b ON a.customerID =
b.customerID WHERE b.Fname='marc';

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/Customers.mdb.

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Type mismatch in expression.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)



Does the error give you any hints in what is going wrong?