DBRW generating ADODB.Command error '800a0cc1'

John Garvey · May 27, 2009

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.

Stefan B Rusynko · May 28, 2009

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

John Garvey · May 28, 2009

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 said:
See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

Kathleen Anderson · May 28, 2009

The FP2003 code has changed slightly. To see the true error, open the hidden
folder /_fpclass/ and edit the fpdbrgn1.inc file

At about line 19, change :

fp_DEBUG = False

to :

fp_DEBUG = True

Save the change, and then preview your page in the browser again - you
should get a better error message.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
Expression Web Wiki: http://expression-web-wiki.com/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

John Garvey said:
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 said:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

Click to expand...

John Garvey · May 28, 2009

Thank you. I am now getting this detail about the error:

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

My guess the Distance variable is causing the problem.

--
John

Kathleen Anderson said:
The FP2003 code has changed slightly. To see the true error, open the hidden
folder /_fpclass/ and edit the fpdbrgn1.inc file

At about line 19, change :

fp_DEBUG = False

to :

fp_DEBUG = True

Save the change, and then preview your page in the browser again - you
should get a better error message.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
Expression Web Resources: http://www.spiderwebwoman.com/xweb/
Expression Web Wiki: http://expression-web-wiki.com/
FrontPage Resources: http://www.spiderwebwoman.com/resources/
Please reply to the newsgroup for the benefit of others

John Garvey said:

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 said:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

Click to expand...

Click to expand...

Ronx · May 28, 2009

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

Change this to:

s-sql="SELECT Latitude + :

istance:: / 69.172 AS [MaxLat], Latitude -
:

istance:: / 69.712 AS
[MinLat], Longitude + :

istance:: / 69.172 AS [MaxLon], Longitude -
:

istance:: / 69.172 AS
[MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"

I have not tested this.

Notice that :

istance:: 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 said:
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 said:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

Click to expand...

John Garvey · May 28, 2009

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 :

istance:: with the numbers 1, 2, 3
and 4, but in my custom query I have :

istance:: just as you have in your
suggested code.
--
John

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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 said:

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 said:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

Click to expand...

Click to expand...

Ronx · May 28, 2009

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 said:
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 :istance:: with the numbers 1, 2, 3
and 4, but in my custom query I have :istance:: just as you have in your
suggested code.
--
John

Ronx said:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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 said:

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

:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

Click to expand...

Click to expand...

John Garvey · May 28, 2009

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 + :

istance:: / 69.172 AS [MaxLat], Latitude - :

istance:: /
69.712 AS [MinLat], Longitude + :

istance:: / 69.172 AS [MaxLon], Longitude
- :

istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
EnterZip::')

Here's what the grey code on the web page looks like:

<!--webbot bot="DatabaseRegionStart"
s-columnnames="MaxLat,MinLat,MaxLon,MinLon" s-columntypes="5,5,5,5"
s-dataconnection="neatokeeno" b-tableformat="TRUE" b-menuformat="FALSE"
s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE"
b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
i-listformat="0" b-makeform="FALSE" s-recordsource
s-displaycolumns="MaxLat,MinLat,MaxLon,MinLon" s-criteria s-order
s-sql="SELECT Latitude + :

istance:: / 69.172 AS [MaxLat], Latitude -
:

istance:: / 69.712 AS [MinLat], Longitude + :

istance:: / 69.172 AS
[MaxLon], Longitude - :

istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006
WHERE (Zip = ':: EnterZip::')" b-procedure="FALSE" clientside
suggestedext="asp" s-defaultfields s-norecordsfound="No records returned."
i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc"
u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
preview="<tr><td colspan=64 bgcolor="#FF0000" width="100%">This is the start of a Database Results region. The page must
be fetched from a web server with a web browser to display correctly; the
current web is stored on your local disk or network. The custom query
contains errors.</td></tr>" startspan -->

--
John

Ronx said:
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 said:

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 :istance:: with the numbers 1, 2, 3
and 4, but in my custom query I have :istance:: just as you have in your
suggested code.
--
John

Ronx said:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

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

:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

|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

Click to expand...

Click to expand...

Ronx · May 29, 2009

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 said:
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 + :istance:: / 69.172 AS [MaxLat], Latitude - :istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS [MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

Ronx said:

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 said:

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 :istance:: with the numbers 1,
2, 3
and 4, but in my custom query I have :istance:: just as you have in
your
suggested code.
--
John

:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

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

:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

message
|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

Click to expand...

Click to expand...

John Garvey · May 30, 2009

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 said:
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 said:

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 + :istance:: / 69.172 AS [MaxLat], Latitude - :istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS [MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

Ronx said:

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.

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 :istance:: with the numbers 1,
2, 3
and 4, but in my custom query I have :istance:: just as you have in
your
suggested code.
--
John

:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

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

:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

message
|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)....
|

Click to expand...

Click to expand...

Ronx · May 30, 2009

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 said:
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 said:

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 said:

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 + :istance:: / 69.172 AS [MaxLat], Latitude - :istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS [MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

:

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.

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 :istance:: with the numbers
1,
2, 3
and 4, but in my custom query I have :istance:: just as you have
in
your
suggested code.
--
John

:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

message
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

:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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!" (-;
_____________________________________________

message
|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)....
|

Click to expand...

Click to expand...

John Garvey · May 30, 2009

I'm still getting stuck on a couple of things. In your Database1 you had the
Zip_Code field formatted as a number. This is a text field in my table,
resulting in a data mismatch error. I need this to be a text field because I
want zip codes with leading zeroes to display as such. I've been struggling
with how to modify your code accordingly, but I can't get it. It only works
if i change my zipcode field to number format, but that's really not what I
need.

Re the next page, can I use the DBRW there, and somehow use Maxlon, Minlon,
etc that are passed from the previous page? Where exactly do I place MaxLat
= session("Maxlat"), etc on the second page?

Thanks for your point about the latitude and longitude formula. I'm aware
of that; just using the simple formula as a place holder while I get the
pages working. I'll tackle the formula next.
--
John

Ronx said:
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 said:

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 said:

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.

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 + :istance:: / 69.172 AS [MaxLat], Latitude - :istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS [MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

:

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.

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 :istance:: with the numbers
1,
2, 3
and 4, but in my custom query I have :istance:: just as you have
in
your
suggested code.
--
John

:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

message
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

:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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

Click to expand...

Click to expand...

Ronx · May 30, 2009

I have modified the pages and database:
http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm

--
Ron Symonds
Microsoft MVP (Expression)
http://www.rxs-enterprises.org/fp

Reply only to group - emails will be deleted unread.

John Garvey said:
I'm still getting stuck on a couple of things. In your Database1 you had
the
Zip_Code field formatted as a number. This is a text field in my table,
resulting in a data mismatch error. I need this to be a text field
because I
want zip codes with leading zeroes to display as such. I've been
struggling
with how to modify your code accordingly, but I can't get it. It only
works
if i change my zipcode field to number format, but that's really not what
I
need.

Re the next page, can I use the DBRW there, and somehow use Maxlon,
Minlon,
etc that are passed from the previous page? Where exactly do I place
MaxLat
= session("Maxlat"), etc on the second page?

Thanks for your point about the latitude and longitude formula. I'm aware
of that; just using the simple formula as a place holder while I get the
pages working. I'll tackle the formula next.
--
John

Ronx said:

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 said:

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

:

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.

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 + :istance:: / 69.172 AS [MaxLat], Latitude -
:istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS [MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip =
'::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

:

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.

message
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 :istance:: with the
numbers
1,
2, 3
and 4, but in my custom query I have :istance:: just as you
have
in
your
suggested code.
--
John

:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

message
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

:

See
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
and
http://databases.aspfaq.com/general...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

Click to expand...

Click to expand...

John Garvey · May 31, 2009

Thanks Ron. First page is working like a charm.

I'm still not clear on how I can use the passed values in the query on the
next page? I've tried using a custom query in the DBW using this code, but
I am getting a data mismatch error.

SELECT Zipcodes.Zip, Zipcodes.State, Zipcodes.City, Zipcodes.County,
tblLocal.LocalID, tblLocal.LocalCat, tblLocal.LocalName,
tblLocal.LocalAddress, tblLocal.LocalCity, tblLocal.LocalState,
tblLocal.LocalZip, tblLocal.LocalMinAge, tblLocal.LocalDisctDesc,
tblLocal.LocalPhone, Zipcodes.Latitude, Zipcodes.Longitude
FROM Zipcodes INNER JOIN tblLocal ON Zipcodes.Zip = tblLocal.LocalZip
WHERE (((Zipcodes.Latitude) Between ' & MinLat & ' And ' & MaxLat & ') AND
((Zipcodes.Longitude) Between ' & MinLon & ' And ' & MaxLon & '));

BTW I am about to leave for a trade show for about a week so if you are able
to reply don't think I am ignoring you. Many, many thanks for all your help.

--
John

Ronx said:
I have modified the pages and database:
http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm

--
Ron Symonds
Microsoft MVP (Expression)
http://www.rxs-enterprises.org/fp

Reply only to group - emails will be deleted unread.

John Garvey said:

I'm still getting stuck on a couple of things. In your Database1 you had
the
Zip_Code field formatted as a number. This is a text field in my table,
resulting in a data mismatch error. I need this to be a text field
because I
want zip codes with leading zeroes to display as such. I've been
struggling
with how to modify your code accordingly, but I can't get it. It only
works
if i change my zipcode field to number format, but that's really not what
I
need.

Re the next page, can I use the DBRW there, and somehow use Maxlon,
Minlon,
etc that are passed from the previous page? Where exactly do I place
MaxLat
= session("Maxlat"), etc on the second page?

Thanks for your point about the latitude and longitude formula. I'm aware
of that; just using the simple formula as a place holder while I get the
pages working. I'll tackle the formula next.
--
John

Ronx said:

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.

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

:

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.

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 + :istance:: / 69.172 AS [MaxLat], Latitude -
:istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS [MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip =
'::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

:

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.

message
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 :istance:: with the
numbers
1,
2, 3
and 4, but in my custom query I have :istance:: just as you
have
in
your
suggested code.
--
John

:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

message
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

Click to expand...

Click to expand...

Ronx · Jun 6, 2009

Use the following query in the DBR:

SELECT Zipcodes.Zip, Zipcodes.State, Zipcodes.City, Zipcodes.County,
tblLocal.LocalID, tblLocal.LocalCat, tblLocal.LocalName,
tblLocal.LocalAddress, tblLocal.LocalCity, tblLocal.LocalState,
tblLocal.LocalZip, tblLocal.LocalMinAge, tblLocal.LocalDisctDesc,
tblLocal.LocalPhone, Zipcodes.Latitude, Zipcodes.Longitude
FROM Zipcodes INNER JOIN tblLocal ON Zipcodes.Zip = tblLocal.LocalZip
WHERE ((Zipcodes.Latitude >= ::MinLat:

AND (Zipcodes.Latitude <=
::MaxLat:

AND
(Zipcodes.Longitude >= ::MinLon:

AND (Zipcodes.Longitude <= ::MaxLon:

);

The first page should send the values to the second page using the
querystring method.

I have not tested your query - but a simplified version at
http://www.rxs-enterprises.org/tests/pages/zip_code.asp and the DBR page
link on that page produces the right answers.
--
Ron Symonds
Microsoft MVP (Expression)
http://www.rxs-enterprises.org/fp

Reply only to group - emails will be deleted unread.

John Garvey said:
Thanks Ron. First page is working like a charm.

I'm still not clear on how I can use the passed values in the query on the
next page? I've tried using a custom query in the DBW using this code,
but
I am getting a data mismatch error.

SELECT Zipcodes.Zip, Zipcodes.State, Zipcodes.City, Zipcodes.County,
tblLocal.LocalID, tblLocal.LocalCat, tblLocal.LocalName,
tblLocal.LocalAddress, tblLocal.LocalCity, tblLocal.LocalState,
tblLocal.LocalZip, tblLocal.LocalMinAge, tblLocal.LocalDisctDesc,
tblLocal.LocalPhone, Zipcodes.Latitude, Zipcodes.Longitude
FROM Zipcodes INNER JOIN tblLocal ON Zipcodes.Zip = tblLocal.LocalZip
WHERE (((Zipcodes.Latitude) Between ' & MinLat & ' And ' & MaxLat & ') AND
((Zipcodes.Longitude) Between ' & MinLon & ' And ' & MaxLon & '));

BTW I am about to leave for a trade show for about a week so if you are
able
to reply don't think I am ignoring you. Many, many thanks for all your
help.

--
John

Ronx said:

I have modified the pages and database:
http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm

--
Ron Symonds
Microsoft MVP (Expression)
http://www.rxs-enterprises.org/fp

Reply only to group - emails will be deleted unread.

John Garvey said:

I'm still getting stuck on a couple of things. In your Database1 you
had
the
Zip_Code field formatted as a number. This is a text field in my
table,
resulting in a data mismatch error. I need this to be a text field
because I
want zip codes with leading zeroes to display as such. I've been
struggling
with how to modify your code accordingly, but I can't get it. It only
works
if i change my zipcode field to number format, but that's really not
what
I
need.

Re the next page, can I use the DBRW there, and somehow use Maxlon,
Minlon,
etc that are passed from the previous page? Where exactly do I place
MaxLat
= session("Maxlat"), etc on the second page?

Thanks for your point about the latitude and longitude formula. I'm
aware
of that; just using the simple formula as a place holder while I get
the
pages working. I'll tackle the formula next.
--
John

:

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.

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

:

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.

message
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 + :istance:: / 69.172 AS [MaxLat], Latitude -
:istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS
[MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip
=
'::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

:

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.

message
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 :istance:: with the
numbers
1,
2, 3
and 4, but in my custom query I have :istance:: just as you
have
in
your
suggested code.
--
John

:

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

Change this to:

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

I have not tested this.

Notice that :istance:: 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.

message
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

Click to expand...

Click to expand...

John Garvey · Jun 8, 2009

Thanks Ron. I have been out of town for a week at a trade show so will need
a few days to dig out before I can look at this. I certainly appreciate the
help very much.
--
John

Ronx said:
Use the following query in the DBR:

SELECT Zipcodes.Zip, Zipcodes.State, Zipcodes.City, Zipcodes.County,
tblLocal.LocalID, tblLocal.LocalCat, tblLocal.LocalName,
tblLocal.LocalAddress, tblLocal.LocalCity, tblLocal.LocalState,
tblLocal.LocalZip, tblLocal.LocalMinAge, tblLocal.LocalDisctDesc,
tblLocal.LocalPhone, Zipcodes.Latitude, Zipcodes.Longitude
FROM Zipcodes INNER JOIN tblLocal ON Zipcodes.Zip = tblLocal.LocalZip
WHERE ((Zipcodes.Latitude >= ::MinLat: AND (Zipcodes.Latitude <=
::MaxLat: AND
(Zipcodes.Longitude >= ::MinLon: AND (Zipcodes.Longitude <= ::MaxLon:);

The first page should send the values to the second page using the
querystring method.

I have not tested your query - but a simplified version at
http://www.rxs-enterprises.org/tests/pages/zip_code.asp and the DBR page
link on that page produces the right answers.
--
Ron Symonds
Microsoft MVP (Expression)
http://www.rxs-enterprises.org/fp

Reply only to group - emails will be deleted unread.

John Garvey said:

Thanks Ron. First page is working like a charm.

I'm still not clear on how I can use the passed values in the query on the
next page? I've tried using a custom query in the DBW using this code,
but
I am getting a data mismatch error.

SELECT Zipcodes.Zip, Zipcodes.State, Zipcodes.City, Zipcodes.County,
tblLocal.LocalID, tblLocal.LocalCat, tblLocal.LocalName,
tblLocal.LocalAddress, tblLocal.LocalCity, tblLocal.LocalState,
tblLocal.LocalZip, tblLocal.LocalMinAge, tblLocal.LocalDisctDesc,
tblLocal.LocalPhone, Zipcodes.Latitude, Zipcodes.Longitude
FROM Zipcodes INNER JOIN tblLocal ON Zipcodes.Zip = tblLocal.LocalZip
WHERE (((Zipcodes.Latitude) Between ' & MinLat & ' And ' & MaxLat & ') AND
((Zipcodes.Longitude) Between ' & MinLon & ' And ' & MaxLon & '));

BTW I am about to leave for a trade show for about a week so if you are
able
to reply don't think I am ignoring you. Many, many thanks for all your
help.

--
John

Ronx said:

I have modified the pages and database:
http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm

--
Ron Symonds
Microsoft MVP (Expression)
http://www.rxs-enterprises.org/fp

Reply only to group - emails will be deleted unread.

I'm still getting stuck on a couple of things. In your Database1 you
had
the
Zip_Code field formatted as a number. This is a text field in my
table,
resulting in a data mismatch error. I need this to be a text field
because I
want zip codes with leading zeroes to display as such. I've been
struggling
with how to modify your code accordingly, but I can't get it. It only
works
if i change my zipcode field to number format, but that's really not
what
I
need.

Re the next page, can I use the DBRW there, and somehow use Maxlon,
Minlon,
etc that are passed from the previous page? Where exactly do I place
MaxLat
= session("Maxlat"), etc on the second page?

Thanks for your point about the latitude and longitude formula. I'm
aware
of that; just using the simple formula as a place holder while I get
the
pages working. I'll tackle the formula next.
--
John

:

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.

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

:

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.

message
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 + :istance:: / 69.172 AS [MaxLat], Latitude -
:istance:: /
69.712 AS [MinLat], Longitude + :istance:: / 69.172 AS
[MaxLon],
Longitude
- :istance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip
=
'::
EnterZip::')

Here's what the grey code on the web page looks like:



--
John

:

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.

message
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

Click to expand...

Click to expand...

Parameter Query Error - Expression is too complex to be evaluated	5	Nov 26, 2007
distance between zip codes	0	Jun 9, 2005
SQL Query to Order by distance - my brain is fried	1	Mar 3, 2008
DBRW link to frontpage form	23	Jan 19, 2009
Function in Query is "too complex"	3	Sep 7, 2006
Use DBRW field in form field	5	Apr 2, 2005
subform rowsource pass through to table value function w/ paramete	0	Feb 23, 2008
Cure my array headache!	7	Jul 26, 2005

DBRW generating ADODB.Command error '800a0cc1'

John Garvey

Stefan B Rusynko

John Garvey

Kathleen Anderson

John Garvey

Ronx

John Garvey

Ronx

John Garvey

Ronx

John Garvey

Ronx

John Garvey

Ronx

John Garvey

Ronx

John Garvey

Ask a Question

Similar Threads