Re: Can FP Handle Complex Queries

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

> 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.
>
>
> "Stefan B Rusynko" wrote:
>
> > 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
> >
> > --
> >
> > _____________________________________________
> > 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:ABD0254C-1325-424F-9A82-749C14D83805@xxxxxxxxxxxxxxxx
> > | 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.
> >
> >
> >
.

Quantcast