Query not working in FP

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
Tue Jun 07 04:06:29 CDT 2005

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

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


"Mr. Know Nothing" <MrKnowNothing@discussions.microsoft.com> wrote in message
news:BAE0A816-1AEB-49F6-BCEA-4AB6790C3A4A@microsoft.com...
|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.

MrKnowNothing
Tue Jun 07 09:45:15 CDT 2005

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
>
> --

Stefan
Tue Jun 07 09:58:53 CDT 2005

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

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


"Mr. Know Nothing" <MrKnowNothing@discussions.microsoft.com> wrote in message
news:BCFCC223-5938-42DC-8F04-CBD117C527A8@microsoft.com...
| 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
| >
| > --
|

MrKnowNothing
Tue Jun 07 10:51:07 CDT 2005

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
>
> --
>

Stefan
Wed Jun 08 05:46:55 CDT 2005

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

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


"Mr. Know Nothing" <MrKnowNothing@discussions.microsoft.com> wrote in message
news:FB9E54A4-D975-476C-BF4A-2BA3DC7DF180@microsoft.com...
| 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
| >
| > --
| >
|

MrKnowNothing
Wed Jun 08 14:01:04 CDT 2005

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.


"Stefan B Rusynko" wrote:

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

MrKnowNothing
Wed Jun 08 14:42:04 CDT 2005

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?