Database now returning errors

Augie Dawg · Nov 6, 2004

I would be greatly indebted to anyone who has an idea as to where I should
be looking for a solution to this problem. I've developed a number of
custom queries using the FP2002's DBRW and an Access 2002 database, and they
have worked flawlessly for the last two years. About a month ago, about 2/3
of these queries started returning errors like "Your page contains a query
with user input parameters that could not be resolved." and
"ADODB.Recordset error '800a0cc1' ". At first I thought it was my hosting
service's servers that were to blame (after all, I hadn't changed anything
to cause the problem), but I just changed providers and the problems
persist. I've done the "compact and repair" on the .mdb file, but the same
errors get returned. I'm at my wit's end on this one. I've looked at the
queries that still work and can find no common denominator between them that
would explain why some work and some don't. Help!

A.D.

Kathleen Anderson [MVP - FP] · Nov 6, 2004

See the second part of this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;817029

Augie Dawg · Nov 7, 2004

I saw that article, but it says it applies to FP 2003. I'm using FP2002.
Also, I looked at the HTML generated, and all s-columnnames and
s-columntypes look just fine. Any other ideas?

Bob Lehmann · Nov 7, 2004

Any other ideas?

Quit using the Data Base Retard Wizard (DBRW) and write your own code.

Bob Lehmann

Clark · Nov 7, 2004

So if you upgrade to fp2003, you are requiring your isp to have asp.net
to get around snags like that?

Thomas A. Rowe · Nov 7, 2004

No, just learn how to hand code your ASP and not rely on the FP database components.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

Augie Dawg · Nov 7, 2004

I'm a little surprised at the responses that urges hand coding vs. use of
higher level tools like the FP database components. While they aren't
perfect, I've found them to be great productivity enhancers. I've spent a
fraction of the time developing the database side of my site than if I had
hand coded it. What is the rationale for promoting hand coding? It seems
that if hand coding is better than using the higher level tool, then by
extension we should all be coding in assembly language rather than the
higher level tools like VB Script.

A.D.

Bob Lehmann · Nov 7, 2004

I've found them to be great productivity enhancers.
How much productivity have you gained in the last day trying to find your
work-around?
Flexibile, more efficient, readable, maintainable code for starters.
Extremely bad analogy. The use of high level tools isn't the problem. But,
the automagical DB thing in FP is so bad it is effectively useless for
anything of substance. And, as you are finding out, it is nearly impossible
to work around the constraints it imposes.

Bob Lehmann

Thomas A. Rowe · Nov 7, 2004

When you learn to hand code, you learn ASP/VBScript.

By learn ASP/VBScript you gain the ability to incorporate other functionality that you might need
down the road, which is just not possible to implement when using the FP database components.

For very basic adding or displaying of data the FP database component are a good starting ground.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

Clark · Nov 8, 2004

Hey Tom, I know you're not gonna let up on my learning asp coding, so I
thought I would share my work-around for that problem. I just teamed up
with a young buck (friend of my grandsons) who is doing all the work to
completely automate my next 2 sites. We're going 50 / 50 on whatever
comes out of it

Now get this -- the deal I made with him was I would do the site design,
he would do the asp coding. Well, once he got started he says to me just
let him do it all, it's faster that way. OK says me, I can live with
that. So now I am the site strategist / requirements inputter /
critiquer. You would love this guy, he does ALL his coding by hand
(crazy but hey it works for him)

No need for me to learn asp. It's a wonderful world out there, you gotta
love it. :=)

Thomas A. Rowe · Nov 8, 2004

That is one way to solve the problem...

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

Augie Dawg · Nov 8, 2004

OK, now that we've covered the hand coding debate, I've still got the
problem of 3/4 of my 100+ ASP pages not working when they were functioning
flawlessly for over two years. I've done some experimenting, and I can make
one of remaining working queries fail simply by adding an AS clause to the
SELECT statement. Specifically, this works great:

SELECT DISTINCT Type, COUNT (Type) FROM LibraryContents WHERE (Type <>
'Supply') AND (Type <> 'Periodical') GROUP BY Type

but this fails:

SELECT DISTINCT Type, COUNT (Type) AS "Item Count" FROM LibraryContents
WHERE (Type <> 'Supply') AND (Type <> 'Periodical') GROUP BY Type

I get the following error from the second query:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name
or ordinal.

E:\WEB\ORCHIDSSCOR\HTDOCS\LIBRARY DATA\../_fpclass/fpdblib.inc, line 48

I'm currently operating on the theory that the files in _fpclass have
somehow been corrupted. Anyone know where I can get a known good copy of
these files? Or am I barking up the wrong tree?

Thanks,

A.D.

Thomas A. Rowe · Nov 9, 2004

Try change your Select statement to read:

SELECT DISTINCT Type, COUNT (Type) AS "ItemCount" FROM LibraryContents WHERE (Type <> 'Supply') AND
(Type <> 'Periodical') GROUP BY Type, ItemCount

The Group By statement must specific state each field being retrieved from the table. Notice that
ItemCount must contain no spaces.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

Augie Dawg · Nov 9, 2004

Well, at least I got a different error message! Your version of the query
(cut and pasted for accuracy) returns the following:

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine

I was surprised at your assertion that "ItemCount" can't have spaces, as I
have successfully put spaces in the alias part of the AS clause without
problems for years. Did something change in the Jet engine? BTW, the query
fails with the same error message if I take the quotes away from ItemCount
as well.

For the record, here's the code generated by the DRW; it looks good to me:

<table border="1">
<thead>
<tr>
<td><b>Type</b></td>
<td><b>ItemCount</b></td>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT DISTINCT Type, COUNT (Type) AS ""ItemCount"" FROM
LibraryContents WHERE (Type <> 'Supply') AND (Type <> 'Periodical') GROUP
BY Type, ItemCount "
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="LibraryData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="Type"
fp_sMenuValue="Type"
fp_iDisplayCols=2
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"Type")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ItemCount")%></td>
</tr>

</tbody>
</table>

Thomas A. Rowe · Nov 9, 2004

I think the issue with using AS with the DRW generated code, is that the code is expecting to
retrieve a actual field from the table, whereas if you were to write this completely by hand, it
would work as expected.

I avoid using spaces in any field names, etc. The Jet engine has changed in what words are
considered reserved, and when it is enforced.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

Kathleen Anderson [MVP - FP] · Nov 9, 2004

Augie said:
I'm currently operating on the theory that the files in _fpclass have
somehow been corrupted. Anyone know where I can get a known good
copy of these files? Or am I barking up the wrong tree?

http://www.frontpagewebmaster.com/m-175524/tm.htm

Bob Lehmann · Nov 9, 2004

A couple of things....

In normal coding, quotes inside the SQl don't work. So, unless the FP
DRWhatever thing requires the quotes, remove them from around "Item Count".
Since you have an illegal alias (space=bad), you will to either remove the
space or put square brackets around it [Item Count].

Bob Lehmann

Augie Dawg · Nov 10, 2004

Thank you for the link...about 1/3 of my problems went away when I uploaded
the good version of those files! I had two other problems to diagnose and
fix after that, both related to presumed recent changes in the way the Jet
engine parses requests. I had to change all 241 occurrances of SELECT ...
AS "Member Name" to SELECT.....AS [Member Name]. That got another 1/3 of
the pages working. The last change was to enclose all occurances of aliases
that were identical to Jet SQL reserved words with brackets, also not
necessary under previous versions of Jet. Four hours later, it looks like
it's working! Thanks to all who responded with helpful hints!

A.D.

Augie Dawg · Nov 10, 2004

Thanks so much for your help! Previous versions of the Jet engine allowed
the quotes, but you're right, changing them to square brackets did the
trick.

A.D.

Bob Lehmann said:
A couple of things....

In normal coding, quotes inside the SQl don't work. So, unless the FP
DRWhatever thing requires the quotes, remove them from around "Item
Count".
Since you have an illegal alias (space=bad), you will to either remove the
space or put square brackets around it [Item Count].

Bob Lehmann

Augie Dawg said:

OK, now that we've covered the hand coding debate, I've still got the
problem of 3/4 of my 100+ ASP pages not working when they were
functioning
flawlessly for over two years. I've done some experimenting, and I can make
one of remaining working queries fail simply by adding an AS clause to
the
SELECT statement. Specifically, this works great:

SELECT DISTINCT Type, COUNT (Type) FROM LibraryContents WHERE (Type <>
'Supply') AND (Type <> 'Periodical') GROUP BY Type

but this fails:

SELECT DISTINCT Type, COUNT (Type) AS "Item Count" FROM LibraryContents
WHERE (Type <> 'Supply') AND (Type <> 'Periodical') GROUP BY Type

I get the following error from the second query:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested
name
or ordinal.

E:\WEB\ORCHIDSSCOR\HTDOCS\LIBRARY DATA\../_fpclass/fpdblib.inc, line 48

I'm currently operating on the theory that the files in _fpclass have
somehow been corrupted. Anyone know where I can get a known good copy of
these files? Or am I barking up the wrong tree?

Thanks,

A.D.

Click to expand...

DBRW Source Change	7	Nov 26, 2007
Error in HOW TO: query	1	Aug 21, 2004
Access Query problem	1	May 21, 2020
Help on DBRW custom query with 2 INNER JOIN statements	1	Aug 20, 2007
Database Connection problem	6	Jan 24, 2008
Access Overflow error	3	Aug 31, 2016
DBRW Issue - Syntax Error	11	Oct 24, 2006
database error	13	Mar 14, 2009

Database now returning errors

Augie Dawg

Kathleen Anderson [MVP - FP]

Augie Dawg

Bob Lehmann

Clark

Thomas A. Rowe

Augie Dawg

Bob Lehmann

Thomas A. Rowe

Clark

Thomas A. Rowe

Augie Dawg

Thomas A. Rowe

Augie Dawg

Thomas A. Rowe

Kathleen Anderson [MVP - FP]

Bob Lehmann

Augie Dawg

Augie Dawg

Ask a Question

Similar Threads