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

From: "Ronx" <ronx917@xxxxxxxxxxx>
Date: Sat, 30 May 2009 11:37:23 +0100

Use session variables:

In the code I provided in zip_code.asp, change:

if not rsTemp1.eof then
%>

to

if not rsTemp1.eof then
session("Maxlat") = rsTemp1("MaxLat")
session("Minlat") = rsTemp1("MinLat")
session("Maxlon") = rsTemp1("MaxLon")
session("Minlon") = rsTemp1("MinLon")
%>

On the next page:

MaxLat = session("Maxlat")

etc.

Off topic - the formula you are using for max and min Longitude is accurate only at the equator.
At about 55 miles from the poles the max longitude would be more like: MaxLon = Longitude+Distance
At latitude 45 degrees: MaxLon = Longitude+(Distance/48.99)

See http://www.csgnetwork.com/degreelenllavcalc.html
--
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:4F8B2D8E-09D0-4C73-908E-F47C4F99EF49@xxxxxxxxxxxxxxxx

Thanks Ron. This definitely works to generate the values that I need to pass
to my next query (Maxlat, Minlat, Maxlon and Minlon). I know how to pass the
values to another page using the DRBW, but I don't know how to modify the
code you provided to accomplish that.
--
John

"Ronx" wrote:

I could not get the code to work either.

See if
http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
helps.

--
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:39301C9C-A527-4394-8789-05A0C54382B8@xxxxxxxxxxxxxxxx
> Thanks Ron,
>
> I have given that a try. After publishing here's what the web page
> displays:
>
> Database Results Wizard Error
> Unable to find operator in query string. Query string currently is > 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::')
>
> Here's what the grey code on the web page looks like:
>
> 
>
> -- > John
>
>
> "Ronx" wrote:
>
>> Don't place the code in the custom query. Place it directly in the >> web
>> age - replace the SQL in the grey code starting s-qry=
>>
>> The save the page whilst still in code view.
>> -- >> 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:98D200AE-74E7-497F-B48B-4CC0322E52BF@xxxxxxxxxxxxxxxx
>> > Thanks Ronx.
>> >
>> > FrontPage won't let me save the new custom query in the Database
>> > Wizard.
>> > I
>> > get this database connection error:
>> >
>> > Server error: Unable to retrieve schema information from the query:
>> >
>> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 / >> > 69.712
>> > AS
>> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172 >> > AS
>> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
>> >
>> > against a database using the connection string
>> >
>> > DRIVER={Microsoft Access Driver (*.mdb,
>> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
>> >
>> > The following error message comes from the database driver software; >> > it
>> > may
>> > appear in a different language depending on how the driver is
>> > configured.
>> > -------------------------------------------------------
>> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
>> > expected
>> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
>> >
>> > Source: Microsoft OLE DB Provider for ODBC Drivers
>> > Number: -2147217900 (0x80040e14)
>> >
>> > I notice that the error has replaced ::Distance:: with the numbers >> > 1,
>> > 2, 3
>> > and 4, but in my custom query I have ::Distance:: just as you have >> > in
>> > your
>> > suggested code.
>> > -- >> > John
>> >
>> >
>> > "Ronx" wrote:
>> >
>> >> 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)....
>> >> >> |

Follow-Ups:
- Re: DBRW generating ADODB.Command error '800a0cc1'
  - From: John Garvey

References:
- Re: DBRW generating ADODB.Command error '800a0cc1'
  - From: John Garvey

Prev by Date: Re: DBRW generating ADODB.Command error '800a0cc1'
Next by Date: Re: DBRW generating ADODB.Command error '800a0cc1'
Previous by thread: Re: DBRW generating ADODB.Command error '800a0cc1'
Next by thread: Re: DBRW generating ADODB.Command error '800a0cc1'
Index(es):
- Date
- Thread

Relevant Pages

Re: Access and Front Page 2003 -- Syntax Error
... Did you tried in the custom query changing all the "" ... | they support Access DB's but don't support the Access Web View portion ... Started off by importing an access 2003 database into Front Page ... and got "database results wizard failed. ...
(microsoft.public.frontpage.client)
Database Results Wizard Error: 817029
... need to a parameter to a custom query using sum: ... Database Results Wizard Error ... The DRW is really of limited value if one can't make select statements work ... with 'distinct', 'sum', 'count' and a form parameter. ...
(microsoft.public.frontpage.programming)
re: Criteria in Database Results
... I am encountering an error while trying this custom query. ... The following error message comes from the database driver ... Microsoft OLE DB Provider for ODBC Drivers ...
(microsoft.public.frontpage.programming)
Re: Database isnt updating
... When I do the custom query all I get for a SQL statement is this ... search engine is connected to the Access Database instead. ... > Please rerun the DRW and on page 2, click Custom query and Edit. ...
(microsoft.public.frontpage.client)