Multi-table SQL -2147217904 (0x80040E10) Error

[LukeDouglas]

OK, so I copied my sql query as follows:

SELECT FB_Games.GameNumber, FB_Games.GameDate, Opponent.SchooName FROM
Opponent LEFT JOIN FB_Games ON Opponent.SchoolID=FB_Games.OpponentSchoolID
ORDER BY ::Sortcolumn:: ASC

I set the default for Sortcolumn to GameNumber. This yields the following
FP code:

<table width=&quot;100%&quot; border=&quot;1&quot;>
<thead>
<tr>
<td><b><a
href=&quot;Luke_test.asp?Sortcolumn=GameNumber&quot;>GameNumber</a></b></td>
<td><b><a
href=&quot;Luke_test.asp?Sortcolumn=GameDate&quot;>GameDate</a></b></td>
<td><b><a
href=&quot;Luke_test.asp?Sortcolumn=SchoolName&quot;>SchooName</a></b></td>
</tr>
</thead>
<tbody>
<!--webbot bot=&quot;DatabaseRegionStart&quot; startspan
s-columnnames=&quot;GameNumber,GameDate,SchooName&quot; s-columntypes=&quot;3,135,128&quot;
s-dataconnection=&quot;Sports&quot; b-tableformat=&quot;TRUE&quot; b-menuformat=&quot;FALSE&quot;
s-menuchoice=&quot;GameNumber&quot; s-menuvalue=&quot;GameNumber&quot; b-tableborder=&quot;TRUE&quot;
b-tableexpand=&quot;TRUE&quot;
b-tableheader=&quot;TRUE&quot; b-listlabels=&quot;TRUE&quot; b-listseparator=&quot;TRUE&quot;
i-ListFormat=&quot;0&quot; b-makeform=&quot;FALSE&quot; s-recordsource
s-displaycolumns=&quot;GameNumber,GameDate,SchooName&quot; s-criteria s-order
s-sql=&quot;SELECT FB_Games.GameNumber, FB_Games.GameDate,
Opponent.SchooName FROM Opponent LEFT JOIN FB_Games ON
Opponent.SchoolID=FB_Games.OpponentSchoolID ORDER BY ::SortColumn:: ASC&quot;
b-procedure=&quot;FALSE&quot; clientside SuggestedExt=&quot;asp&quot;
s-DefaultFields=&quot;SortColumn=GameNumber&quot; s-NoRecordsFound=&quot;No records
returned.&quot;
i-MaxRecords=&quot;256&quot; i-GroupSize=&quot;14&quot; BOTID=&quot;0&quot;
u-dblib=&quot;../../../_fpclass/fpdblib.inc&quot;
u-dbrgn1=&quot;../../../_fpclass/fpdbrgn1.inc&quot;
u-dbrgn2=&quot;../../../_fpclass/fpdbrgn2.inc&quot; tag=&quot;TBODY&quot;
local_preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot;
align=&quot;left&quot; width=&quot;100%&quot;><font
color=&quot;#000000&quot;>Database Results regions will not preview
unless this page is fetched from a Web server with a web browser. The
following table row will repeat once for every record returned by the
query.</font></td></tr>&quot;
preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot;
align=&quot;left&quot; width=&quot;100%&quot;><font
color=&quot;#000000&quot;>This is the start of a Database Results region.
The page must be fetched from a web server with a web browser to display
correctly; the current web is stored on your local disk or
network.</font></td></tr>&quot;
b-WasTableFormat=&quot;TRUE&quot; --><!--#include
file=&quot;../../../_fpclass/fpdblib.inc&quot;-->
<%
fp_sQry=&quot;SELECT FB_Games.GameNumber, FB_Games.GameDate, Opponent.SchooName
FROM Opponent LEFT JOIN FB_Games ON
Opponent.SchoolID=FB_Games.OpponentSchoolID ORDER BY ::SortColumn:: ASC&quot;
fp_sDefault=&quot;SortColumn=GameNumber&quot;
fp_sNoRecords=&quot;<tr><td colspan=3 align=left width=&quot;&quot;100%&quot;&quot;>No records
returned.</td></tr>&quot;
fp_sDataConn=&quot;Sports&quot;
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=14
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=&quot;GameNumber&quot;
fp_sMenuValue=&quot;GameNumber&quot;
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

When I run this asp page, I get the following error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
'C:\MyWeb\data\Sports.mdb' is not a valid path. Make sure that the path name
is spelled correctly and that you are connected to the server on which the
file resides.
Number: -2147467259 (0x80004005)
Source: Microsoft OLE DB Provider for ODBC Drivers
----------------------------------------------------------------------------
------------------------------------------------
Now here is the sql as created for a single file that runs fine.

SELECT * FROM FB_Games ORDER BY ::SortColumn:: ASC

I set the default for Sortcolumn to GameNumber. This yields the following
FP code:

<table width=&quot;100%&quot; border=&quot;1&quot;>
<thead>
<tr>
<td><b><a
href=&quot;Luke_test_good.asp?Sortcolumn=GameNumber&quot;>GameNumber</a></b></td>
<td><b><a
href=&quot;Luke_test_good.asp?Sortcolumn=GameDate&quot;>GameDate</a></b></td>
<td><b><a
href=&quot;Luke_test_good.asp?Sortcolumn=OpponentSchoolID&quot;>OpponentSchoolID</a></
b></td>
</tr>
</thead>
<tbody>
<!--webbot bot=&quot;DatabaseRegionStart&quot; startspan

s-columnnames=&quot;GameID,SchoolID,Year,GameNumber,GameDate,OpponentSchoolID,Sch
ool_Score,School_Qtr1,School_Qtr2,School_Qtr3,School_Qtr4,School_OT,Opponent
_Score,Opponent_Qtr1,Opponent_Qtr2,Opponent_Qtr3,Opponent_Qtr4,Opponent_OT,N
umber_OT,School_Qtr1_Time,School_Qtr2_Time,School_Qtr3_Time,School_Qtr4_Time
,Opponent_Qtr1_Time,Opponent_Qtr2_Time,Opponent_Qtr3_Time,Opponent_Qtr4_Time
,School_Qtr1_FD_Pass,School_Qtr2_FD_Pass,School_Qtr3_FD_Pass,School_Qtr4_FD_
Pass,School_Qtr1_FD_Rush,School_Qtr2_FD_Rush,School_Qtr3_FD_Rush,School_Qtr4
_FD_Rush,School_Qtr1_FD_Penalty,School_Qtr2_FD_Penalty,School_Qtr3_FD_Penalt
y,School_Qtr4_FD_Penalty,Opponent_Qtr1_FD_Pass,Opponent_Qtr2_FD_Pass,Opponen
t_Qtr3_FD_Pass,Opponent_Qtr4_FD_Pass,Opponent_Qtr1_FD_Rush,Opponent_Qtr2_FD_
Rush,Opponent_Qtr3_FD_Rush,Opponent_Qtr4_FD_Rush,Opponent_Qtr1_FD_Penalty,Op
ponent_Qtr2_FD_Penalty,Opponent_Qtr3_FD_Penalty,Opponent_Qtr4_FD_Penalty&quot;

s-columntypes=&quot;202,3,3,3,135,3,3,3,3,3,202,3,3,3,3,3,3,3,3,135,135,135,135,1
35,135,135,135,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3&quot;
s-dataconnection=&quot;Sports&quot; b-tableformat=&quot;TRUE&quot; b-menuformat=&quot;FALSE&quot;
s-menuchoice s-menuvalue b-tableborder=&quot;TRUE&quot; b-tableexpand=&quot;TRUE&quot;
b-tableheader=&quot;TRUE&quot; b-listlabels=&quot;TRUE&quot; b-listseparator=&quot;TRUE&quot;
i-ListFormat=&quot;0&quot; b-makeform=&quot;FALSE&quot; s-recordsource
s-displaycolumns=&quot;GameNumber,GameDate,OpponentSchoolID&quot; s-criteria
s-order
s-sql=&quot;SELECT * FROM FB_Games ORDER BY ::SortColumn:: ASC&quot;
b-procedure=&quot;FALSE&quot; clientside SuggestedExt=&quot;asp&quot;
s-DefaultFields=&quot;SortColumn=GameNumber&quot;
s-NoRecordsFound=&quot;No records returned.&quot; i-MaxRecords=&quot;256&quot;
i-GroupSize=&quot;14&quot;
BOTID=&quot;0&quot; u-dblib=&quot;../../../_fpclass/fpdblib.inc&quot;
u-dbrgn1=&quot;../../../_fpclass/fpdbrgn1.inc&quot;
u-dbrgn2=&quot;../../../_fpclass/fpdbrgn2.inc&quot; tag=&quot;TBODY&quot;
local_preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot;
align=&quot;left&quot; width=&quot;100%&quot;><font
color=&quot;#000000&quot;>Database Results regions will not preview
unless this page is fetched from a Web server with a web browser. The
following table row will repeat once for every record returned by the
query.</font></td></tr>&quot;
preview=&quot;<tr><td colspan=64 bgcolor=&quot;#FFFF00&quot;
align=&quot;left&quot; width=&quot;100%&quot;><font
color=&quot;#000000&quot;>This is the start of a Database Results region.
The page must be fetched from a web server with a web browser to display
correctly; the current web is stored on your local disk or
network.</font></td></tr>&quot; --><!--#include
file=&quot;../../../_fpclass/fpdblib.inc&quot;-->
<%
fp_sQry=&quot;SELECT * FROM FB_Games ORDER BY ::SortColumn:: ASC&quot;
fp_sDefault=&quot;SortColumn=GameNumber&quot;
fp_sNoRecords=&quot;<tr><td colspan=3 align=left width=&quot;&quot;100%&quot;&quot;>No records
returned.</td></tr>&quot;
fp_sDataConn=&quot;Sports&quot;
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=14
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=&quot;&quot;
fp_sMenuValue=&quot;&quot;
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=&quot;../../../_fpclass/fpdbrgn1.inc&quot;-->
<!--webbot bot=&quot;DatabaseRegionStart&quot; i-CheckSum=&quot;50009&quot; endspan -->

The second one, a single table, works fine. I can click on the labels in
each column and it sorts for that column. Notice that the ::Sortcolumn::
value does retrieve the querystring value for the field to sort by. I'm
sure this would work on the first one but alas, something is amiss since the
asp code is attempting to find the data on my local drive instead of my web
hosting service.

I'm not so concerned by the sorting by column, although that is a nice
feature. I'm just trying to get the records to list as they should. If you
can determine what the problem is, you would have made, not just my day, but
my entire year. I have been working on this for a couple of week with no
resolution. Theoretically, the first one on two tables should work, but it
doesn't. Once I get this working, only then will I introduce the next level
of complexity and that is limiting which records will be displayed but I'm
just trying to get to first base. So far, no hits, just strikeouts.

I will admit that I am probably trying to do something that Frontpage was
never designed to do but even in some Frontpage manuals, I do see
multi-table output. Are these manuals just pulling my leg?

Also, do you know how I can set a variable, such as Sortcolumn, from a
cookie and FP recognize the value? This has been unsuccessful also. If it's
not in a form field or in a querystring, it looks like you're out of luck.

I will say this, if MS was really interested in how FP should work, they
would come see me. I may not be a genius but I do have 'some' common sense.

--
Luke Douglas
Headland, Alabama
t3@ala.net
&quot;Just another 'unemployed' old-timer stuck in character mode&quot;