Can FP Handle Complex Queries

[Mr. Know Nothing]

My problem is that i'm trying to get results from Tables A but going through
Table B. I have tried 2 queries:

SELECT a.* FROM Address a INNER JOIN Stores b ON a.MemberNum = b.TimbrID
WHERE (b.CustomerID= ::T1:;

SELECT Address.* FROM Address, Stores WHERE Address.MemberNum=Stores.TimbrID
AND (Stores.CustomerID=::T1:;

Now these queries, in Access, do work (ie. gives the correct results) but
when I put them in FP it gives me the following error:

Database Results Error
The operation failed. If this continues, please contact your server
administrator.

You can look at the page at ujr.ca/test/CallLog.asp
Can FP even handle queries this complex?

Anything on this would be greatly appreciated.

 

[Stefan B Rusynko]

It has nothing to do w/ FP handling it
- its a matter of validating your ASP code to make sure you can get results from your DB in ASP

Before using any Joins validate you can actually get data from each table individually (for syntax / connection errors) by writing a
simple test query for each Table using the same fields and data types

From your code sample
- (presumes T1 and V are some variables previously declared)
- Validate T1 data value/type using
response.write "T1 = " & VarType(T1) & " and is: " & TypeName(T1) & "<br>"

Then individually test

V = XXX ' assign a valid value for MemberNum & CustomerID to test
SELECT * FROM Address WHERE (MemberNum = ::V:
and
MemberNum = V
SELECT * FROM Stores WHERE (TimbrID= ::MemberNum:
and
SELECT * FROM Stores WHERE (CustomerID= ::T1:

If you have no errors try the inner join (all on 1 line)
- presumes T1 is some variable previously declared

strSQL = "SELECT a.* FROM Address a "
strSQL = = strSQL & "INNER JOIN Stores b ON (a.MemberNum = b.TimbrID) "
strSQL = = strSQL & "WHERE (b.CustomerID = ::" & T1 & " : "

Open your Recordset using the variable: strSQL

Then debug/look at what the code being sent to the DB is using:

response.write strSQL & <"br>"

The primary problem w/ Joins is accounting for null results or data type mismatches




| My problem is that i'm trying to get results from Tables A but going through
| Table B. I have tried 2 queries:
|
| SELECT a.* FROM Address a INNER JOIN Stores b ON a.MemberNum = b.TimbrID
| WHERE (b.CustomerID= ::T1:;
|
| SELECT Address.* FROM Address, Stores WHERE Address.MemberNum=Stores.TimbrID
| AND (Stores.CustomerID=::T1:;
|
| Now these queries, in Access, do work (ie. gives the correct results) but
| when I put them in FP it gives me the following error:
|
| Database Results Error
| The operation failed. If this continues, please contact your server
| administrator.
|
| You can look at the page at ujr.ca/test/CallLog.asp
| Can FP even handle queries this complex?
|
| Anything on this would be greatly appreciated.

 

[Mr. Know Nothing]

Stefan, I have to say that you have a good explination, if only I could
understand what you are saying. Would it be possible for you to explain that
in more simple terms? What do you mean by "validating your ASP code"?

The only thing I understand from your reply is to test each table
individually and that I have done and it gives me the correct results.

Note: I am not touching the code at all i'm doing everything with the wizards.

 

[Tenzin]

While using the FP Database Results Wizard to set up the custom query, before
completing the wizard, in one of the steps when you specify custom query,
there's a button to "Verify Query". Does this give any errors? If yes, then
your search query has an error. If not, then there's probably something not
working right between your version of FP and the server software. I've had no
problems in using custom queries for databases using FP

Tenzin

 

[Mr. Know Nothing]

Tenzin, I have verified the query and there are no errors. I have a question
for you , in the last part of your reply you have "there's probably something
not working right between your version of FP and the server software" how
can this be is i have other database results on the same page that work but
just not the one with this query?

Mr. Know Nothing

 

[Tenzin]

The reason why I say that is because I'm having a similar problem: I can set
up new webpages (with databases) without problem in subsites that were
created a while ago with probably older versions of FrontPage. However, when
I create new subsites now and do the same setup/process, it gives me the same
error message that you get. I've googled around and many seem to point to a
possible problem between FrontPage3, Access database and WindowXP. Not sure
how far that's true.

Any chance that your webpage was set up several months ago with the
databases that work. And that this one that doesn't work was added/modified
to the same page recently (probably after updating to FP2003)?

Tenzin

 

[Tenzin]

Does this database (that doesn't work) work if you replace the query string
with a simple one?

 

[Mr. Know Nothing]

No to the first reply, the webpage and the database have both been build in
the pass 2 months without any upgrades made to either programs.

The second reply, the database does work as i'm using it with other queries
and getting the proper results. It's just not liking the query I have for
this result.

Mr. Know Nothing

 

[Tenzin]

I guess your problem is different from mine. Mine has started working after
switching to SQL database instead of Access.
Good luck to you!