Thanks for replying John,
My database is a SQL Server 2000 to which I am communicating using an ODBC
System DSN on Windows 2003 Server. I am using FP 2003.
On the SQL Server, the table looks like this:
CREATE TABLE [dbo].[DO_SUB_TOS] (
[DO_NUM] [int] NOT NULL ,
[COMPANY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TO_NUM] [float] NOT NULL ,
[LABOR] [money] NULL ,
[OTHER] [money] NULL ,
[FUNDED_OVERRIDE] [money] NULL ,
[DUE_SUB] AS ([labor] + [other] - [funded_OVERRIDE]) ,
[NOTES] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DLChanged] [binary] (8) NULL
) ON [PRIMARY]
So I created an Access database with linked tables back to the SQL Server.
I tested my query on Access, which preferred having the [' '] around the
alias string, so I kept them in the query.
I created a ODBC System DSN to this Access database.
I created a new web site and used the odbc connection to this Access
database.
I used the following query and it worked:
select Company, sum(labor) as ['labor'], sum(other) as ['other'],
sum(funded_override) as ['funded_override'], sum(due_sub) as ['due_sub']
from dbo_do_sub_tos
where do_num = 113 group by do_num, company order by company
The data shows in the table as follows - I don't know how to cut/paste as I
looks in the table on the asp page, but you get the idea:
Company 'labor' 'other' 'funded_override' 'due_sub'
ABC 14 26 38 2
BCD 0 0 0 0
CDE 0 0 0 0
DEF 0 0 0 0
EFG 0 0 0 0
============================================================================
==
I added a parameter which is passed to the page as
new_page_1.asp?DO_NUM=113. The receipt of the parameter by the page was
verified by <%response.write(request("DO_NUM"))%>
The query looked like:
SELECT Company, sum(labor) AS ['labor'], sum(other) AS ['other'],
sum(funded_override) AS ['funded_override'], sum(due_sub) AS ['due_sub']
FROM dbo_do_sub_tos
WHERE do_num=::DO_NUM:: GROUP BY do_num, company
ORDER BY company
I got the following error:
Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.
One or more form fields were empty. You should provide default values for
all form fields that are used in the query.
==================================================================
I then changed the query to put a default of 0 for each sum as follows:
SELECT Company, 0+sum(labor) AS ['labor'], 0+sum(other) AS ['other'],
0+sum(funded_override) AS ['funded_override'], 0+sum(due_sub) AS ['due_sub']
FROM dbo_do_sub_tos
WHERE do_num=::DO_NUM:: GROUP BY do_num, company
ORDER BY company
I then got this error:
Database Results Wizard Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.
I hope this was helpful. I am looking forward to getting this bug fixed.
Thanks,
Sue
"John Jansen (MSFT)" <johnjan@online.microsoft.com> wrote in message
news:urwHF4lIEHA.3224@TK2MSFTNGP09.phx.gbl...
> Hi Sue,
>
> Can you run your Query in Access and see if it works? I am trying to
> replicate your error using the Northwind database, and I am not seeing it
in
> FrontPage
>
> Here is my SQL statement, authoring against the Products table from
> Northwind:
>
> SELECT Sum(ProductID) AS Products, Sum(UnitPrice) AS Price, ProductName
FROM
> Products WHERE SupplierID=::SuppliersID:: GROUP BY ProductName, SupplierID
> order by ProductName;
>
> I am including a string field, a currency field and a number field, so
> functionally I don't see any difference from your query.
>
> What type field is do_num?
> --
> Thanks!
> John Jansen
> Microsoft Office FrontPage
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "SK" <sue.kapr@removeme.titan.com> wrote in message
> news:%23RnFRBjIEHA.3220@TK2MSFTNGP12.phx.gbl...
> > Using FP 2003 I continue to get the following error from the DRW if I
add
> > need to a parameter to a custom query using sum:
> >
> > Database Results Wizard Error
> > Your page contains a query with user input parameters that could not be
> > resolved.
> > This could happen if your DatabaseRegionStart webbot has an empty or
> > missing
> > s-columnnames or s-columntypes attributes.
> > You may need to read Microsoft Knowledge Base Article 817029.
> >
> > fp_sQry="select Company, sum(labor) as LABOR, sum(other) as OTHER,
> > sum(funded_override) as OVER_RIDE, sum(due_sub) as DUE_SUB from
do_sub_tos
> > where do_num = ::DO_NUM:: group by do_num, company order by company"
> > fp_sDefault="DO_NUM="
> > fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
records
> > returned.</td></tr>"
> > fp_sDataConn="Database1"
> > fp_iMaxRecords=0
> > fp_iCommandType=1
> > fp_iPageSize=0
> > fp_fTableFormat=True
> > fp_fMenuFormat=False
> > fp_sMenuChoice=""
> > fp_sMenuValue=""
> > fp_sColTypes="&Company=200&LABOR=6&OTHER=6&OVER_RIDE=6&DUE_SUB=6&"
> > fp_iDisplayCols=5
> > fp_fCustomQuery=True
> > BOTID=3
> > fp_iRegion=BOTID
> >
> > This MS article is bogus. This should work.
> >
> > I can get the query to work if I take out "where do_num = ::DO_NUM::" to
> > make it:
> >
> > select Company, sum(labor) as LABOR, sum(other) as OTHER,
> > sum(funded_override) as OVER_RIDE, sum(due_sub) as DUE_SUB from
do_sub_tos
> > group by do_num, company order by company
> >
> > but of course the information I want is not displayed.
> >
> > I thought adding DO_NUM to the select columns would help, but it does
not.
> >
> >
===========================================================================
> >
> > I have gotten the error before on a different page, but made it work in
> > the
> > following way by adding a false column and hiding it:
> >
> > fp_sQry="SELECT ' ' as DO_NUM, sum(labor) as SUM_LABOR, sum(TRAVEL) as
> > SUM_TRAVEL, SUM(other) as SUM_OTHER, SUM(FUNDED) as SUM_FUNDED from
> > do_details where DO_NUM = ::DO_NUM::"
> > fp_sDefault="DO_NUM="
> > fp_sNoRecords="No records returned."
> > fp_sDataConn="Database1"
> > fp_iMaxRecords=0
> > fp_iCommandType=1
> > fp_iPageSize=0
> > fp_fTableFormat=False
> > fp_fMenuFormat=False
> > fp_sMenuChoice=""
> > fp_sMenuValue=""
> >
fp_sColTypes="&DO_NUM=200&SUM_LABOR=6&SUM_TRAVEL=6&SUM_OTHER=6&SUM_FUNDED=6&
> > "
> > fp_iDisplayCols=5
> > fp_fCustomQuery=True
> > BOTID=1
> > fp_iRegion=BOTID
> >
> >
============================================================================
> > ====
> >
> > If anyone (Microsoft) knows a fix for this I would really appreciate it.
> > The DRW is really of limited value if one can't make select statements
> > work
> > with 'distinct', 'sum', 'count' and a form parameter.
> >
> > Thanks for any help.
> >
> > Sue
> >
> >
> >
>
>