Database Results Wizard Error: 817029

[SK]

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 = :O_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 = :O_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 = :O_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

 

[John Jansen \(MSFT\)]

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?

 

[SK]

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=:O_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=:O_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