Re: DBRW generating ADODB.Command error '800a0cc1'

Open the page in code view, and find the SQL statement in the grey comments:

Change this to:

s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude - ::Distance:: / 69.712 AS
[MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude - ::Distance:: / 69.172 AS
[MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"

I have not tested this.

Notice that ::Distance:: refers to the field named Distance, and ::EnterZip:: refers to the field named EnterZip. Distance is assumed to be numeric, and EnterZip is a string value - you should have code to ensure these have the correct value types. Your version of the SQL still referred to Distance as a database field, and EnterZip as a vaiable.
Also note that the sql statement above has been reformatted for the FrontPage database results wizard - it is not valid SQL.
--
Ron Symonds
Microsoft MVP (Expression)
http://www.rxs-enterprises.org/fp

Reply only to group - emails will be deleted unread.



"John Garvey" <JohnGarvey@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@xxxxxxxxxxxxxxxx
Thank you very much for your help on this. I understand what you are saying
about the Distance field. I have tried revising the custom query but am
still having problems. Since I need to determine a range within the maximum
and minimum longitudes and latitudes to pass to the next form, I set it up
like this:

SELECT Latitude+Distance/69.172 AS [MaxLat], Latitude-Distance/69.712 AS
[MinLat], Longitude+Distance/69.172 AS [MaxLon], Longitude-Distance/69.172 AS
[MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')

When I verify the query it says it was verified with the database
connection. But when I look at the published page I am getting this error in
the database results region:

Database Results Wizard Error
The operation failed. If this continues, please contact your server
administrator.

I have 2 text boxes on the form, EnterZip and Distance, and I have the form
posting to the same page searchlocal1zip.asp that the form and the database
results region are on.

What do you think I am still doing wrong?

--
John


"Stefan B Rusynko" wrote:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html

Also check all your fields names in the query to make sure they are the same as in your DB

PS
Your query as written is looking for Distance in the DB because you have coded it like a DB field [Distance]
- but Distance is not a DB field, it is a form result variable (same as EnterZip)
- plus Like is meaningless criteria on a number like a 5 digit Zip code

You will need to rewrite your query and logic to remove Distance from the DB values
- presuming all fields and form results are numeric and EnterZip and Distance are your form fields
Your Query (all on 1 line) would be something like:

"SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" & EnterZip &
" AND latitude<" & Distance/69.172 & " AND latitude>" & Distance/69.172 &
" AND longitude<" & Distance/69.172 & " AND longitude>" & Distance/69.172

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


"John Garvey" <JohnGarvey@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@xxxxxxxxxxxxxxxx
|I am trying to create a page on my site that will display a list of locations
| from my Access DB that are within a specified radius of a Zip Code selected
| by the user. My queries work fine in Access, but I am having trouble using
| them in the DBRW. I am getting this error:
|
| ADODB.Command error '800a0cc1'
| Item cannot be found in the collection corresponding to the requested name
| or ordinal.
|
| /neatokeeno/_fpclass/fpdbrgn1.inc, line 408
|
|
| I'd sure appreciate any help getting back on track.
|
| I would like to have users enter into a form their Zip Code and the number
| of miles in the radius they want to search, and have all corresponding
| records display in the results.
|
| I have a DB table ZipCodes_2006 that contains all US zip codes and all their
| corresponding latitudes and longitues. My first query (ZipLookup1)
| calculates the min and max of latitudes and longitudes (MinLat, MaxLat,
| MinLon, Max Lon) based on the variable [Distance] from the Zip Code
| ([EnterZip]) that the user enters.
|
| I want to have the results sent to the next form that would query the DB for
| matching records that have Zip Codes that fall in that range. For now I'd be
| happy just to get the results of this first query to display on the first
| page (searchlocal.asp)....
|
| The data source (ZipLookup1 (VIEW)) is a saved query on the DB. Here's my
| code for that query:
|
| SELECT [latitude]+[Distance]/69.172 AS MaxLat, [latitude]-[Distance]/69.172
| AS MinLat, [longitude]+[Distance]/69.172 AS MaxLon,
| [longitude]-[Distance]/69.172 AS MinLon
| FROM Zipcodes_2006
| WHERE (((Zipcodes_2006.Zip) Like [EnterZip]));
|
| I have a search form on the page with input fields [Distance] and
| [EnterZip].
|
| If i can get this right, I was planning to change the form so instead of
| posting to the same page it would post the latitude and longitude ranges to a
| follow-up form on another page.
|
| I'm not sure what to look for here. Thanks in advance.
|
| -- | John



.

Relevant Pages

  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... Source: Microsoft JET Database Engine ... My guess the Distance variable is causing the problem. ... When I verify the query it says it was verified with the database ... I have 2 text boxes on the form, EnterZip and Distance, and I have the ...
    (microsoft.public.frontpage.programming)
  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... When I verify the query it says it was verified with the database ... I have 2 text boxes on the form, EnterZip and Distance, and I have the ...
    (microsoft.public.frontpage.programming)
  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... FrontPage won't let me save the new custom query in the Database Wizard. ... to Distance as a database field, and EnterZip as a vaiable. ...
    (microsoft.public.frontpage.programming)
  • Re: DBRW generating ADODB.Command error 800a0cc1
    ... and minimum longitudes and latitudes to pass to the next form, ... When I verify the query it says it was verified with the database ... I have 2 text boxes on the form, EnterZip and Distance, and I have the form ...
    (microsoft.public.frontpage.programming)
  • Re: MySQL Query Optimization
    ... > query can take 15 seconds or more for broader searches. ... What you really need is a precomputed table of distances between zip codes. ... any location in one block is within distance of any location in the other). ... The distance table would consist of pairs of three-digit zones that meet ...
    (comp.lang.php)
Loading