DBRW generating ADODB.Command error '800a0cc1'

microsoft.public.frontpage.programming

27 May 2009 10:30 PM

John Garvey

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

28 May 2009 8:45 AM

Stefan B Rusynko

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

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

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

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

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

--

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

Show quoteHide quote

"John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
|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

Are all your drivers up to date? click for free checkup

28 May 2009 1:16 PM

John Garvey

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

Show quoteHide quote

"Stefan B Rusynko" wrote:

> See
> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
> and
> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
>
> Also check all your fields names in the query to make sure they are the same as in your DB
>
> PS
> Your query as written is looking for Distance in the DB because you have coded it like a DB field [Distance]
> - but Distance is not a DB field, it is a form result variable (same as EnterZip)
> - plus Like is meaningless criteria on a number like a 5 digit Zip code
>
> You will need to rewrite your query and logic to remove Distance from the DB values
> - presuming all fields and form results are numeric and EnterZip and Distance are your form fields
> Your Query (all on 1 line) would be something like:
>
> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" & EnterZip &
> " AND latitude<" & Distance/69.172 & " AND latitude>" & Distance/69.172 &
> " AND longitude<" & Distance/69.172 & " AND longitude>" & Distance/69.172
>
> --
>
> _____________________________________________
> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> "Warning - Using the F1 Key will not break anything!" (-;
> _____________________________________________
>
>
> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
> |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
>
>
>

28 May 2009 3:19 PM

Kathleen Anderson

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

Show quoteHide quote

"John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
> Thank you very much for your help on this. I understand what you are
> saying
> about the Distance field. I have tried revising the custom query but am
> still having problems. Since I need to determine a range within the
> maximum
> and minimum longitudes and latitudes to pass to the next form, I set it up
> like this:
>
> SELECT Latitude+Distance/69.172 AS [MaxLat], Latitude-Distance/69.712 AS
> [MinLat], Longitude+Distance/69.172 AS [MaxLon], Longitude-Distance/69.172
> AS
> [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
>
> When I verify the query it says it was verified with the database
> connection. But when I look at the published page I am getting this error
> in
> the database results region:
>
> Database Results Wizard Error
> The operation failed. If this continues, please contact your server
> administrator.
>
> I have 2 text boxes on the form, EnterZip and Distance, and I have the
> form
> posting to the same page searchlocal1zip.asp that the form and the
> database
> results region are on.
>
> What do you think I am still doing wrong?
>
> --
> John
>
>
> "Stefan B Rusynko" wrote:
>
>> See
>> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
>> and
>> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
>>
>> Also check all your fields names in the query to make sure they are the
>> same as in your DB
>>
>> PS
>> Your query as written is looking for Distance in the DB because you have
>> coded it like a DB field [Distance]
>> - but Distance is not a DB field, it is a form result variable (same as
>> EnterZip)
>> - plus Like is meaningless criteria on a number like a 5 digit Zip code
>>
>> You will need to rewrite your query and logic to remove Distance from the
>> DB values
>> - presuming all fields and form results are numeric and EnterZip and
>> Distance are your form fields
>> Your Query (all on 1 line) would be something like:
>>
>> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" &
>> EnterZip &
>> " AND latitude<" & Distance/69.172 & " AND latitude>" & Distance/69.172 &
>> " AND longitude<" & Distance/69.172 & " AND longitude>" & Distance/69.172
>>
>> --
>>
>> _____________________________________________
>> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
>> "Warning - Using the F1 Key will not break anything!" (-;
>> _____________________________________________
>>
>>
>> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
>> |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
>>
>>
>>

28 May 2009 4:04 PM

John Garvey

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

Show quoteHide quote

"Kathleen Anderson" wrote:

> 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" <JohnGar***@discussions.microsoft.com> wrote in message
> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
> > Thank you very much for your help on this. I understand what you are
> > saying
> > about the Distance field. I have tried revising the custom query but am
> > still having problems. Since I need to determine a range within the
> > maximum
> > and minimum longitudes and latitudes to pass to the next form, I set it up
> > like this:
> >
> > SELECT Latitude+Distance/69.172 AS [MaxLat], Latitude-Distance/69.712 AS
> > [MinLat], Longitude+Distance/69.172 AS [MaxLon], Longitude-Distance/69.172
> > AS
> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
> >
> > When I verify the query it says it was verified with the database
> > connection. But when I look at the published page I am getting this error
> > in
> > the database results region:
> >
> > Database Results Wizard Error
> > The operation failed. If this continues, please contact your server
> > administrator.
> >
> > I have 2 text boxes on the form, EnterZip and Distance, and I have the
> > form
> > posting to the same page searchlocal1zip.asp that the form and the
> > database
> > results region are on.
> >
> > What do you think I am still doing wrong?
> >
> > --
> > John
> >
> >
> > "Stefan B Rusynko" wrote:
> >
> >> See
> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
> >> and
> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
> >>
> >> Also check all your fields names in the query to make sure they are the
> >> same as in your DB
> >>
> >> PS
> >> Your query as written is looking for Distance in the DB because you have
> >> coded it like a DB field [Distance]
> >> - but Distance is not a DB field, it is a form result variable (same as
> >> EnterZip)
> >> - plus Like is meaningless criteria on a number like a 5 digit Zip code
> >>
> >> You will need to rewrite your query and logic to remove Distance from the
> >> DB values
> >> - presuming all fields and form results are numeric and EnterZip and
> >> Distance are your form fields
> >> Your Query (all on 1 line) would be something like:
> >>
> >> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" &
> >> EnterZip &
> >> " AND latitude<" & Distance/69.172 & " AND latitude>" & Distance/69.172 &
> >> " AND longitude<" & Distance/69.172 & " AND longitude>" & Distance/69.172
> >>
> >> --
> >>
> >> _____________________________________________
> >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> >> "Warning - Using the F1 Key will not break anything!" (-;
> >> _____________________________________________
> >>
> >>
> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
> >> |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
> >>
> >>
> >>
>
>
>

28 May 2009 5:03 PM

Ronx

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

Change this to:

s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -

::Distance:: / 69.712 AS

[MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude -

::Distance:: / 69.172 AS

[MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"

I have not tested this.

Notice that ::Distance:: refers to the field named Distance, and

::EnterZip:: refers to the field named EnterZip. Distance is assumed to be

numeric, and EnterZip is a string value - you should have code to ensure
these have the correct value types. Your version of the SQL still referred
to Distance as a database field, and EnterZip as a vaiable.
Also note that the sql statement above has been reformatted for the
FrontPage database results wizard - it is not valid SQL.

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

Reply only to group - emails will be deleted unread.

Show quoteHide quote

28 May 2009 5:26 PM

John Garvey

Thanks Ronx.

FrontPage won't let me save the new custom query in the Database Wizard. I
get this database connection error:

Server error: Unable to retrieve schema information from the query:

s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 / 69.712 AS
[MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172 AS
[MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"

against a database using the connection string

DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected
'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217900 (0x80040e14)

I notice that the error has replaced ::Distance:: with the numbers 1, 2, 3
and 4, but in my custom query I have ::Distance:: just as you have in your
suggested code.

--
John

Show quoteHide quote

"Ronx" wrote:

> Open the page in code view, and find the SQL statement in the grey comments:
>
> Change this to:
>
> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
> ::Distance:: / 69.712 AS
> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude -
> ::Distance:: / 69.172 AS
> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
>
> I have not tested this.
>
> Notice that ::Distance:: refers to the field named Distance, and
> ::EnterZip:: refers to the field named EnterZip. Distance is assumed to be
> numeric, and EnterZip is a string value - you should have code to ensure
> these have the correct value types. Your version of the SQL still referred
> to Distance as a database field, and EnterZip as a vaiable.
> Also note that the sql statement above has been reformatted for the
> FrontPage database results wizard - it is not valid SQL.
> --
> Ron Symonds
> Microsoft MVP (Expression)
> http://www.rxs-enterprises.org/fp
>
> Reply only to group - emails will be deleted unread.
>
>
>
> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
> > Thank you very much for your help on this. I understand what you are
> > saying
> > about the Distance field. I have tried revising the custom query but am
> > still having problems. Since I need to determine a range within the
> > maximum
> > and minimum longitudes and latitudes to pass to the next form, I set it up
> > like this:
> >
> > SELECT Latitude+Distance/69.172 AS [MaxLat], Latitude-Distance/69.712 AS
> > [MinLat], Longitude+Distance/69.172 AS [MaxLon], Longitude-Distance/69.172
> > AS
> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
> >
> > When I verify the query it says it was verified with the database
> > connection. But when I look at the published page I am getting this error
> > in
> > the database results region:
> >
> > Database Results Wizard Error
> > The operation failed. If this continues, please contact your server
> > administrator.
> >
> > I have 2 text boxes on the form, EnterZip and Distance, and I have the
> > form
> > posting to the same page searchlocal1zip.asp that the form and the
> > database
> > results region are on.
> >
> > What do you think I am still doing wrong?
> >
> > --
> > John
> >
> >
> > "Stefan B Rusynko" wrote:
> >
> >> See
> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
> >> and
> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
> >>
> >> Also check all your fields names in the query to make sure they are the
> >> same as in your DB
> >>
> >> PS
> >> Your query as written is looking for Distance in the DB because you have
> >> coded it like a DB field [Distance]
> >> - but Distance is not a DB field, it is a form result variable (same as
> >> EnterZip)
> >> - plus Like is meaningless criteria on a number like a 5 digit Zip code
> >>
> >> You will need to rewrite your query and logic to remove Distance from the
> >> DB values
> >> - presuming all fields and form results are numeric and EnterZip and
> >> Distance are your form fields
> >> Your Query (all on 1 line) would be something like:
> >>
> >> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" &
> >> EnterZip &
> >> " AND latitude<" & Distance/69.172 & " AND latitude>" & Distance/69.172 &
> >> " AND longitude<" & Distance/69.172 & " AND longitude>" & Distance/69.172
> >>
> >> --
> >>
> >> _____________________________________________
> >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> >> "Warning - Using the F1 Key will not break anything!" (-;
> >> _____________________________________________
> >>
> >>
> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
> >> |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
> >>
> >>
> >>
>

28 May 2009 5:40 PM

Ronx

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.

Show quoteHide quote

"John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
> Thanks Ronx.
>
> FrontPage won't let me save the new custom query in the Database Wizard.
> I
> get this database connection error:
>
> Server error: Unable to retrieve schema information from the query:
>
> s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 / 69.712 AS
> [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172 AS
> [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
>
> against a database using the connection string
>
> DRIVER={Microsoft Access Driver (*.mdb,
> *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
>
> The following error message comes from the database driver software; it
> may
> appear in a different language depending on how the driver is configured.
> -------------------------------------------------------
> [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected
> 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
>
> Source: Microsoft OLE DB Provider for ODBC Drivers
> Number: -2147217900 (0x80040e14)
>
> I notice that the error has replaced ::Distance:: with the numbers 1, 2, 3
> and 4, but in my custom query I have ::Distance:: just as you have in your
> suggested code.
> --
> John
>
>
> "Ronx" wrote:
>
>> Open the page in code view, and find the SQL statement in the grey
>> comments:
>>
>> Change this to:
>>
>> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
>> ::Distance:: / 69.712 AS
>> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude -
>> ::Distance:: / 69.172 AS
>> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
>>
>> I have not tested this.
>>
>> Notice that ::Distance:: refers to the field named Distance, and
>> ::EnterZip:: refers to the field named EnterZip. Distance is assumed to
>> be
>> numeric, and EnterZip is a string value - you should have code to ensure
>> these have the correct value types. Your version of the SQL still
>> referred
>> to Distance as a database field, and EnterZip as a vaiable.
>> Also note that the sql statement above has been reformatted for the
>> FrontPage database results wizard - it is not valid SQL.
>> --
>> Ron Symonds
>> Microsoft MVP (Expression)
>> http://www.rxs-enterprises.org/fp
>>
>> Reply only to group - emails will be deleted unread.
>>
>>
>>
>> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
>> > Thank you very much for your help on this. I understand what you are
>> > saying
>> > about the Distance field. I have tried revising the custom query but
>> > am
>> > still having problems. Since I need to determine a range within the
>> > maximum
>> > and minimum longitudes and latitudes to pass to the next form, I set it
>> > up
>> > like this:
>> >
>> > SELECT Latitude+Distance/69.172 AS [MaxLat], Latitude-Distance/69.712
>> > AS
>> > [MinLat], Longitude+Distance/69.172 AS [MaxLon],
>> > Longitude-Distance/69.172
>> > AS
>> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
>> >
>> > When I verify the query it says it was verified with the database
>> > connection. But when I look at the published page I am getting this
>> > error
>> > in
>> > the database results region:
>> >
>> > Database Results Wizard Error
>> > The operation failed. If this continues, please contact your server
>> > administrator.
>> >
>> > I have 2 text boxes on the form, EnterZip and Distance, and I have the
>> > form
>> > posting to the same page searchlocal1zip.asp that the form and the
>> > database
>> > results region are on.
>> >
>> > What do you think I am still doing wrong?
>> >
>> > --
>> > John
>> >
>> >
>> > "Stefan B Rusynko" wrote:
>> >
>> >> See
>> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
>> >> and
>> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
>> >>
>> >> Also check all your fields names in the query to make sure they are
>> >> the
>> >> same as in your DB
>> >>
>> >> PS
>> >> Your query as written is looking for Distance in the DB because you
>> >> have
>> >> coded it like a DB field [Distance]
>> >> - but Distance is not a DB field, it is a form result variable (same
>> >> as
>> >> EnterZip)
>> >> - plus Like is meaningless criteria on a number like a 5 digit Zip
>> >> code
>> >>
>> >> You will need to rewrite your query and logic to remove Distance from
>> >> the
>> >> DB values
>> >> - presuming all fields and form results are numeric and EnterZip and
>> >> Distance are your form fields
>> >> Your Query (all on 1 line) would be something like:
>> >>
>> >> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" &
>> >> EnterZip &
>> >> " AND latitude<" & Distance/69.172 & " AND latitude>" &
>> >> Distance/69.172 &
>> >> " AND longitude<" & Distance/69.172 & " AND longitude>" &
>> >> Distance/69.172
>> >>
>> >> --
>> >>
>> >> _____________________________________________
>> >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
>> >> "Warning - Using the F1 Key will not break anything!" (-;
>> >> _____________________________________________
>> >>
>> >>
>> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> >> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
>> >> |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
>> >>
>> >>
>> >>
>>

28 May 2009 6:08 PM

John Garvey

Thanks Ron,

I have given that a try. After publishing here's what the web page displays:

Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT
Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude - ::Distance:: /
69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude
- ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
EnterZip::')

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

<!--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 + ::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::')" 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

Show quoteHide quote

"Ronx" wrote:

> Don't place the code in the custom query. Place it directly in the web
> age - replace the SQL in the grey code starting s-qry=
>
> The save the page whilst still in code view.
> --
> Ron Symonds
> Microsoft MVP (Expression)
> http://www.rxs-enterprises.org/fp
>
> Reply only to group - emails will be deleted unread.
>
>
>
> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
> > Thanks Ronx.
> >
> > FrontPage won't let me save the new custom query in the Database Wizard.
> > I
> > get this database connection error:
> >
> > Server error: Unable to retrieve schema information from the query:
> >
> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 / 69.712 AS
> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172 AS
> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
> >
> > against a database using the connection string
> >
> > DRIVER={Microsoft Access Driver (*.mdb,
> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
> >
> > The following error message comes from the database driver software; it
> > may
> > appear in a different language depending on how the driver is configured.
> > -------------------------------------------------------
> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected
> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
> >
> > Source: Microsoft OLE DB Provider for ODBC Drivers
> > Number: -2147217900 (0x80040e14)
> >
> > I notice that the error has replaced ::Distance:: with the numbers 1, 2, 3
> > and 4, but in my custom query I have ::Distance:: just as you have in your
> > suggested code.
> > --
> > John
> >
> >
> > "Ronx" wrote:
> >
> >> Open the page in code view, and find the SQL statement in the grey
> >> comments:
> >>
> >> Change this to:
> >>
> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
> >> ::Distance:: / 69.712 AS
> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude -
> >> ::Distance:: / 69.172 AS
> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
> >>
> >> I have not tested this.
> >>
> >> Notice that ::Distance:: refers to the field named Distance, and
> >> ::EnterZip:: refers to the field named EnterZip. Distance is assumed to
> >> be
> >> numeric, and EnterZip is a string value - you should have code to ensure
> >> these have the correct value types. Your version of the SQL still
> >> referred
> >> to Distance as a database field, and EnterZip as a vaiable.
> >> Also note that the sql statement above has been reformatted for the
> >> FrontPage database results wizard - it is not valid SQL.
> >> --
> >> Ron Symonds
> >> Microsoft MVP (Expression)
> >> http://www.rxs-enterprises.org/fp
> >>
> >> Reply only to group - emails will be deleted unread.
> >>
> >>
> >>
> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
> >> > Thank you very much for your help on this. I understand what you are
> >> > saying
> >> > about the Distance field. I have tried revising the custom query but
> >> > am
> >> > still having problems. Since I need to determine a range within the
> >> > maximum
> >> > and minimum longitudes and latitudes to pass to the next form, I set it
> >> > up
> >> > like this:
> >> >
> >> > SELECT Latitude+Distance/69.172 AS [MaxLat], Latitude-Distance/69.712
> >> > AS
> >> > [MinLat], Longitude+Distance/69.172 AS [MaxLon],
> >> > Longitude-Distance/69.172
> >> > AS
> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
> >> >
> >> > When I verify the query it says it was verified with the database
> >> > connection. But when I look at the published page I am getting this
> >> > error
> >> > in
> >> > the database results region:
> >> >
> >> > Database Results Wizard Error
> >> > The operation failed. If this continues, please contact your server
> >> > administrator.
> >> >
> >> > I have 2 text boxes on the form, EnterZip and Distance, and I have the
> >> > form
> >> > posting to the same page searchlocal1zip.asp that the form and the
> >> > database
> >> > results region are on.
> >> >
> >> > What do you think I am still doing wrong?
> >> >
> >> > --
> >> > John
> >> >
> >> >
> >> > "Stefan B Rusynko" wrote:
> >> >
> >> >> See
> >> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
> >> >> and
> >> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
> >> >>
> >> >> Also check all your fields names in the query to make sure they are
> >> >> the
> >> >> same as in your DB
> >> >>
> >> >> PS
> >> >> Your query as written is looking for Distance in the DB because you
> >> >> have
> >> >> coded it like a DB field [Distance]
> >> >> - but Distance is not a DB field, it is a form result variable (same
> >> >> as
> >> >> EnterZip)
> >> >> - plus Like is meaningless criteria on a number like a 5 digit Zip
> >> >> code
> >> >>
> >> >> You will need to rewrite your query and logic to remove Distance from
> >> >> the
> >> >> DB values
> >> >> - presuming all fields and form results are numeric and EnterZip and
> >> >> Distance are your form fields
> >> >> Your Query (all on 1 line) would be something like:
> >> >>
> >> >> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" &
> >> >> EnterZip &
> >> >> " AND latitude<" & Distance/69.172 & " AND latitude>" &
> >> >> Distance/69.172 &
> >> >> " AND longitude<" & Distance/69.172 & " AND longitude>" &
> >> >> Distance/69.172
> >> >>
> >> >> --
> >> >>
> >> >> _____________________________________________
> >> >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> >> >> "Warning - Using the F1 Key will not break anything!" (-;
> >> >> _____________________________________________
> >> >>
> >> >>
> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> >> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
> >> >> |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
> >> >>
> >> >>
> >> >>
> >>
>

29 May 2009 12:19 PM

Ronx

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.

Show quoteHide quote

"John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
> Thanks Ron,
>
> I have given that a try. After publishing here's what the web page
> displays:
>
> Database Results Wizard Error
> Unable to find operator in query string. Query string currently is SELECT
> Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude - ::Distance:: /
> 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
> Longitude
> - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
> EnterZip::')
>
> Here's what the grey code on the web page looks like:
>
> 
>
> --
> John
>
>
> "Ronx" wrote:
>
>> Don't place the code in the custom query. Place it directly in the web
>> age - replace the SQL in the grey code starting s-qry=
>>
>> The save the page whilst still in code view.
>> --
>> Ron Symonds
>> Microsoft MVP (Expression)
>> http://www.rxs-enterprises.org/fp
>>
>> Reply only to group - emails will be deleted unread.
>>
>>
>>
>> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
>> > Thanks Ronx.
>> >
>> > FrontPage won't let me save the new custom query in the Database
>> > Wizard.
>> > I
>> > get this database connection error:
>> >
>> > Server error: Unable to retrieve schema information from the query:
>> >
>> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 / 69.712
>> > AS
>> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172 AS
>> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
>> >
>> > against a database using the connection string
>> >
>> > DRIVER={Microsoft Access Driver (*.mdb,
>> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
>> >
>> > The following error message comes from the database driver software; it
>> > may
>> > appear in a different language depending on how the driver is
>> > configured.
>> > -------------------------------------------------------
>> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
>> > expected
>> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
>> >
>> > Source: Microsoft OLE DB Provider for ODBC Drivers
>> > Number: -2147217900 (0x80040e14)
>> >
>> > I notice that the error has replaced ::Distance:: with the numbers 1,
>> > 2, 3
>> > and 4, but in my custom query I have ::Distance:: just as you have in
>> > your
>> > suggested code.
>> > --
>> > John
>> >
>> >
>> > "Ronx" wrote:
>> >
>> >> Open the page in code view, and find the SQL statement in the grey
>> >> comments:
>> >>
>> >> Change this to:
>> >>
>> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
>> >> ::Distance:: / 69.712 AS
>> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude -
>> >> ::Distance:: / 69.172 AS
>> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
>> >>
>> >> I have not tested this.
>> >>
>> >> Notice that ::Distance:: refers to the field named Distance, and
>> >> ::EnterZip:: refers to the field named EnterZip. Distance is assumed
>> >> to
>> >> be
>> >> numeric, and EnterZip is a string value - you should have code to
>> >> ensure
>> >> these have the correct value types. Your version of the SQL still
>> >> referred
>> >> to Distance as a database field, and EnterZip as a vaiable.
>> >> Also note that the sql statement above has been reformatted for the
>> >> FrontPage database results wizard - it is not valid SQL.
>> >> --
>> >> Ron Symonds
>> >> Microsoft MVP (Expression)
>> >> http://www.rxs-enterprises.org/fp
>> >>
>> >> Reply only to group - emails will be deleted unread.
>> >>
>> >>
>> >>
>> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
>> >> > Thank you very much for your help on this. I understand what you
>> >> > are
>> >> > saying
>> >> > about the Distance field. I have tried revising the custom query
>> >> > but
>> >> > am
>> >> > still having problems. Since I need to determine a range within the
>> >> > maximum
>> >> > and minimum longitudes and latitudes to pass to the next form, I set
>> >> > it
>> >> > up
>> >> > like this:
>> >> >
>> >> > SELECT Latitude+Distance/69.172 AS [MaxLat],
>> >> > Latitude-Distance/69.712
>> >> > AS
>> >> > [MinLat], Longitude+Distance/69.172 AS [MaxLon],
>> >> > Longitude-Distance/69.172
>> >> > AS
>> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
>> >> >
>> >> > When I verify the query it says it was verified with the database
>> >> > connection. But when I look at the published page I am getting this
>> >> > error
>> >> > in
>> >> > the database results region:
>> >> >
>> >> > Database Results Wizard Error
>> >> > The operation failed. If this continues, please contact your server
>> >> > administrator.
>> >> >
>> >> > I have 2 text boxes on the form, EnterZip and Distance, and I have
>> >> > the
>> >> > form
>> >> > posting to the same page searchlocal1zip.asp that the form and the
>> >> > database
>> >> > results region are on.
>> >> >
>> >> > What do you think I am still doing wrong?
>> >> >
>> >> > --
>> >> > John
>> >> >
>> >> >
>> >> > "Stefan B Rusynko" wrote:
>> >> >
>> >> >> See
>> >> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
>> >> >> and
>> >> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
>> >> >>
>> >> >> Also check all your fields names in the query to make sure they are
>> >> >> the
>> >> >> same as in your DB
>> >> >>
>> >> >> PS
>> >> >> Your query as written is looking for Distance in the DB because you
>> >> >> have
>> >> >> coded it like a DB field [Distance]
>> >> >> - but Distance is not a DB field, it is a form result variable
>> >> >> (same
>> >> >> as
>> >> >> EnterZip)
>> >> >> - plus Like is meaningless criteria on a number like a 5 digit Zip
>> >> >> code
>> >> >>
>> >> >> You will need to rewrite your query and logic to remove Distance
>> >> >> from
>> >> >> the
>> >> >> DB values
>> >> >> - presuming all fields and form results are numeric and EnterZip
>> >> >> and
>> >> >> Distance are your form fields
>> >> >> Your Query (all on 1 line) would be something like:
>> >> >>
>> >> >> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" &
>> >> >> EnterZip &
>> >> >> " AND latitude<" & Distance/69.172 & " AND latitude>" &
>> >> >> Distance/69.172 &
>> >> >> " AND longitude<" & Distance/69.172 & " AND longitude>" &
>> >> >> Distance/69.172
>> >> >>
>> >> >> --
>> >> >>
>> >> >> _____________________________________________
>> >> >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
>> >> >> "Warning - Using the F1 Key will not break anything!" (-;
>> >> >> _____________________________________________
>> >> >>
>> >> >>
>> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
>> >> >> |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
>> >> >>
>> >> >>
>> >> >>
>> >>
>>

30 May 2009 9:30 AM

John Garvey

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

Show quoteHide quote

"Ronx" wrote:

> I could not get the code to work either.
>
> See if
> http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
> helps.
>
> --
> Ron Symonds
> Microsoft MVP (Expression)
> http://www.rxs-enterprises.org/fp
>
> Reply only to group - emails will be deleted unread.
>
>
>
> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
> > Thanks Ron,
> >
> > I have given that a try. After publishing here's what the web page
> > displays:
> >
> > Database Results Wizard Error
> > Unable to find operator in query string. Query string currently is SELECT
> > Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude - ::Distance:: /
> > 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
> > Longitude
> > - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
> > EnterZip::')
> >
> > Here's what the grey code on the web page looks like:
> >
> > 
> >
> > --
> > John
> >
> >
> > "Ronx" wrote:
> >
> >> Don't place the code in the custom query. Place it directly in the web
> >> age - replace the SQL in the grey code starting s-qry=
> >>
> >> The save the page whilst still in code view.
> >> --
> >> Ron Symonds
> >> Microsoft MVP (Expression)
> >> http://www.rxs-enterprises.org/fp
> >>
> >> Reply only to group - emails will be deleted unread.
> >>
> >>
> >>
> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
> >> > Thanks Ronx.
> >> >
> >> > FrontPage won't let me save the new custom query in the Database
> >> > Wizard.
> >> > I
> >> > get this database connection error:
> >> >
> >> > Server error: Unable to retrieve schema information from the query:
> >> >
> >> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 / 69.712
> >> > AS
> >> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172 AS
> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
> >> >
> >> > against a database using the connection string
> >> >
> >> > DRIVER={Microsoft Access Driver (*.mdb,
> >> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
> >> >
> >> > The following error message comes from the database driver software; it
> >> > may
> >> > appear in a different language depending on how the driver is
> >> > configured.
> >> > -------------------------------------------------------
> >> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
> >> > expected
> >> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
> >> >
> >> > Source: Microsoft OLE DB Provider for ODBC Drivers
> >> > Number: -2147217900 (0x80040e14)
> >> >
> >> > I notice that the error has replaced ::Distance:: with the numbers 1,
> >> > 2, 3
> >> > and 4, but in my custom query I have ::Distance:: just as you have in
> >> > your
> >> > suggested code.
> >> > --
> >> > John
> >> >
> >> >
> >> > "Ronx" wrote:
> >> >
> >> >> Open the page in code view, and find the SQL statement in the grey
> >> >> comments:
> >> >>
> >> >> Change this to:
> >> >>
> >> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
> >> >> ::Distance:: / 69.712 AS
> >> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon], Longitude -
> >> >> ::Distance:: / 69.172 AS
> >> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
> >> >>
> >> >> I have not tested this.
> >> >>
> >> >> Notice that ::Distance:: refers to the field named Distance, and
> >> >> ::EnterZip:: refers to the field named EnterZip. Distance is assumed
> >> >> to
> >> >> be
> >> >> numeric, and EnterZip is a string value - you should have code to
> >> >> ensure
> >> >> these have the correct value types. Your version of the SQL still
> >> >> referred
> >> >> to Distance as a database field, and EnterZip as a vaiable.
> >> >> Also note that the sql statement above has been reformatted for the
> >> >> FrontPage database results wizard - it is not valid SQL.
> >> >> --
> >> >> Ron Symonds
> >> >> Microsoft MVP (Expression)
> >> >> http://www.rxs-enterprises.org/fp
> >> >>
> >> >> Reply only to group - emails will be deleted unread.
> >> >>
> >> >>
> >> >>
> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
> >> >> > Thank you very much for your help on this. I understand what you
> >> >> > are
> >> >> > saying
> >> >> > about the Distance field. I have tried revising the custom query
> >> >> > but
> >> >> > am
> >> >> > still having problems. Since I need to determine a range within the
> >> >> > maximum
> >> >> > and minimum longitudes and latitudes to pass to the next form, I set
> >> >> > it
> >> >> > up
> >> >> > like this:
> >> >> >
> >> >> > SELECT Latitude+Distance/69.172 AS [MaxLat],
> >> >> > Latitude-Distance/69.712
> >> >> > AS
> >> >> > [MinLat], Longitude+Distance/69.172 AS [MaxLon],
> >> >> > Longitude-Distance/69.172
> >> >> > AS
> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
> >> >> >
> >> >> > When I verify the query it says it was verified with the database
> >> >> > connection. But when I look at the published page I am getting this
> >> >> > error
> >> >> > in
> >> >> > the database results region:
> >> >> >
> >> >> > Database Results Wizard Error
> >> >> > The operation failed. If this continues, please contact your server
> >> >> > administrator.
> >> >> >
> >> >> > I have 2 text boxes on the form, EnterZip and Distance, and I have
> >> >> > the
> >> >> > form
> >> >> > posting to the same page searchlocal1zip.asp that the form and the
> >> >> > database
> >> >> > results region are on.
> >> >> >
> >> >> > What do you think I am still doing wrong?
> >> >> >
> >> >> > --
> >> >> > John
> >> >> >
> >> >> >
> >> >> > "Stefan B Rusynko" wrote:
> >> >> >
> >> >> >> See
> >> >> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
> >> >> >> and
> >> >> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
> >> >> >>
> >> >> >> Also check all your fields names in the query to make sure they are
> >> >> >> the
> >> >> >> same as in your DB
> >> >> >>
> >> >> >> PS
> >> >> >> Your query as written is looking for Distance in the DB because you
> >> >> >> have
> >> >> >> coded it like a DB field [Distance]
> >> >> >> - but Distance is not a DB field, it is a form result variable
> >> >> >> (same
> >> >> >> as
> >> >> >> EnterZip)
> >> >> >> - plus Like is meaningless criteria on a number like a 5 digit Zip
> >> >> >> code
> >> >> >>
> >> >> >> You will need to rewrite your query and logic to remove Distance
> >> >> >> from
> >> >> >> the
> >> >> >> DB values
> >> >> >> - presuming all fields and form results are numeric and EnterZip
> >> >> >> and
> >> >> >> Distance are your form fields
> >> >> >> Your Query (all on 1 line) would be something like:
> >> >> >>
> >> >> >> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip=" &
> >> >> >> EnterZip &
> >> >> >> " AND latitude<" & Distance/69.172 & " AND latitude>" &
> >> >> >> Distance/69.172 &
> >> >> >> " AND longitude<" & Distance/69.172 & " AND longitude>" &
> >> >> >> Distance/69.172
> >> >> >>
> >> >> >> --
> >> >> >>
> >> >> >> _____________________________________________
> >> >> >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> >> >> >> "Warning - Using the F1 Key will not break anything!" (-;
> >> >> >> _____________________________________________
> >> >> >>
> >> >> >>
> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
> >> >> >> |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)....
> >> >> >> |

30 May 2009 10:37 AM

Ronx

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.

Show quoteHide quote

"John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
news:4F8B2D8E-09D0-4C73-908E-F47C4F99EF49@microsoft.com...
> Thanks Ron. This definitely works to generate the values that I need to
> pass
> to my next query (Maxlat, Minlat, Maxlon and Minlon). I know how to pass
> the
> values to another page using the DRBW, but I don't know how to modify the
> code you provided to accomplish that.
> --
> John
>
>
> "Ronx" wrote:
>
>> I could not get the code to work either.
>>
>> See if
>> http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
>> helps.
>>
>> --
>> Ron Symonds
>> Microsoft MVP (Expression)
>> http://www.rxs-enterprises.org/fp
>>
>> Reply only to group - emails will be deleted unread.
>>
>>
>>
>> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
>> > Thanks Ron,
>> >
>> > I have given that a try. After publishing here's what the web page
>> > displays:
>> >
>> > Database Results Wizard Error
>> > Unable to find operator in query string. Query string currently is
>> > SELECT
>> > Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude - ::Distance:: /
>> > 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
>> > Longitude
>> > - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
>> > EnterZip::')
>> >
>> > Here's what the grey code on the web page looks like:
>> >
>> > 
>> >
>> > --
>> > John
>> >
>> >
>> > "Ronx" wrote:
>> >
>> >> Don't place the code in the custom query. Place it directly in the
>> >> web
>> >> age - replace the SQL in the grey code starting s-qry=
>> >>
>> >> The save the page whilst still in code view.
>> >> --
>> >> Ron Symonds
>> >> Microsoft MVP (Expression)
>> >> http://www.rxs-enterprises.org/fp
>> >>
>> >> Reply only to group - emails will be deleted unread.
>> >>
>> >>
>> >>
>> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> >> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
>> >> > Thanks Ronx.
>> >> >
>> >> > FrontPage won't let me save the new custom query in the Database
>> >> > Wizard.
>> >> > I
>> >> > get this database connection error:
>> >> >
>> >> > Server error: Unable to retrieve schema information from the query:
>> >> >
>> >> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 /
>> >> > 69.712
>> >> > AS
>> >> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172
>> >> > AS
>> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
>> >> >
>> >> > against a database using the connection string
>> >> >
>> >> > DRIVER={Microsoft Access Driver (*.mdb,
>> >> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
>> >> >
>> >> > The following error message comes from the database driver software;
>> >> > it
>> >> > may
>> >> > appear in a different language depending on how the driver is
>> >> > configured.
>> >> > -------------------------------------------------------
>> >> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
>> >> > expected
>> >> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
>> >> >
>> >> > Source: Microsoft OLE DB Provider for ODBC Drivers
>> >> > Number: -2147217900 (0x80040e14)
>> >> >
>> >> > I notice that the error has replaced ::Distance:: with the numbers
>> >> > 1,
>> >> > 2, 3
>> >> > and 4, but in my custom query I have ::Distance:: just as you have
>> >> > in
>> >> > your
>> >> > suggested code.
>> >> > --
>> >> > John
>> >> >
>> >> >
>> >> > "Ronx" wrote:
>> >> >
>> >> >> Open the page in code view, and find the SQL statement in the grey
>> >> >> comments:
>> >> >>
>> >> >> Change this to:
>> >> >>
>> >> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat],
>> >> >> Latitude -
>> >> >> ::Distance:: / 69.712 AS
>> >> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
>> >> >> Longitude -
>> >> >> ::Distance:: / 69.172 AS
>> >> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
>> >> >>
>> >> >> I have not tested this.
>> >> >>
>> >> >> Notice that ::Distance:: refers to the field named Distance, and
>> >> >> ::EnterZip:: refers to the field named EnterZip. Distance is
>> >> >> assumed
>> >> >> to
>> >> >> be
>> >> >> numeric, and EnterZip is a string value - you should have code to
>> >> >> ensure
>> >> >> these have the correct value types. Your version of the SQL still
>> >> >> referred
>> >> >> to Distance as a database field, and EnterZip as a vaiable.
>> >> >> Also note that the sql statement above has been reformatted for the
>> >> >> FrontPage database results wizard - it is not valid SQL.
>> >> >> --
>> >> >> Ron Symonds
>> >> >> Microsoft MVP (Expression)
>> >> >> http://www.rxs-enterprises.org/fp
>> >> >>
>> >> >> Reply only to group - emails will be deleted unread.
>> >> >>
>> >> >>
>> >> >>
>> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
>> >> >> > Thank you very much for your help on this. I understand what you
>> >> >> > are
>> >> >> > saying
>> >> >> > about the Distance field. I have tried revising the custom query
>> >> >> > but
>> >> >> > am
>> >> >> > still having problems. Since I need to determine a range within
>> >> >> > the
>> >> >> > maximum
>> >> >> > and minimum longitudes and latitudes to pass to the next form, I
>> >> >> > set
>> >> >> > it
>> >> >> > up
>> >> >> > like this:
>> >> >> >
>> >> >> > SELECT Latitude+Distance/69.172 AS [MaxLat],
>> >> >> > Latitude-Distance/69.712
>> >> >> > AS
>> >> >> > [MinLat], Longitude+Distance/69.172 AS [MaxLon],
>> >> >> > Longitude-Distance/69.172
>> >> >> > AS
>> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
>> >> >> >
>> >> >> > When I verify the query it says it was verified with the database
>> >> >> > connection. But when I look at the published page I am getting
>> >> >> > this
>> >> >> > error
>> >> >> > in
>> >> >> > the database results region:
>> >> >> >
>> >> >> > Database Results Wizard Error
>> >> >> > The operation failed. If this continues, please contact your
>> >> >> > server
>> >> >> > administrator.
>> >> >> >
>> >> >> > I have 2 text boxes on the form, EnterZip and Distance, and I
>> >> >> > have
>> >> >> > the
>> >> >> > form
>> >> >> > posting to the same page searchlocal1zip.asp that the form and
>> >> >> > the
>> >> >> > database
>> >> >> > results region are on.
>> >> >> >
>> >> >> > What do you think I am still doing wrong?
>> >> >> >
>> >> >> > --
>> >> >> > John
>> >> >> >
>> >> >> >
>> >> >> > "Stefan B Rusynko" wrote:
>> >> >> >
>> >> >> >> See
>> >> >> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
>> >> >> >> and
>> >> >> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
>> >> >> >>
>> >> >> >> Also check all your fields names in the query to make sure they
>> >> >> >> are
>> >> >> >> the
>> >> >> >> same as in your DB
>> >> >> >>
>> >> >> >> PS
>> >> >> >> Your query as written is looking for Distance in the DB because
>> >> >> >> you
>> >> >> >> have
>> >> >> >> coded it like a DB field [Distance]
>> >> >> >> - but Distance is not a DB field, it is a form result variable
>> >> >> >> (same
>> >> >> >> as
>> >> >> >> EnterZip)
>> >> >> >> - plus Like is meaningless criteria on a number like a 5 digit
>> >> >> >> Zip
>> >> >> >> code
>> >> >> >>
>> >> >> >> You will need to rewrite your query and logic to remove Distance
>> >> >> >> from
>> >> >> >> the
>> >> >> >> DB values
>> >> >> >> - presuming all fields and form results are numeric and EnterZip
>> >> >> >> and
>> >> >> >> Distance are your form fields
>> >> >> >> Your Query (all on 1 line) would be something like:
>> >> >> >>
>> >> >> >> "SELECT latitude, longitude, zip FROM Zipcodes_2006 WHERE Zip="
>> >> >> >> &
>> >> >> >> EnterZip &
>> >> >> >> " AND latitude<" & Distance/69.172 & " AND latitude>" &
>> >> >> >> Distance/69.172 &
>> >> >> >> " AND longitude<" & Distance/69.172 & " AND longitude>" &
>> >> >> >> Distance/69.172
>> >> >> >>
>> >> >> >> --
>> >> >> >>
>> >> >> >> _____________________________________________
>> >> >> >> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
>> >> >> >> "Warning - Using the F1 Key will not break anything!" (-;
>> >> >> >> _____________________________________________
>> >> >> >>
>> >> >> >>
>> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> >> message
>> >> >> >> news:1A40B456-3F53-4C1C-92F8-80B7B820F4AC@microsoft.com...
>> >> >> >> |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)....
>> >> >> >> |

30 May 2009 3:06 PM

John Garvey

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

Show quoteHide quote

"Ronx" wrote:

> 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" <JohnGar***@discussions.microsoft.com> wrote in message
> news:4F8B2D8E-09D0-4C73-908E-F47C4F99EF49@microsoft.com...
> > Thanks Ron. This definitely works to generate the values that I need to
> > pass
> > to my next query (Maxlat, Minlat, Maxlon and Minlon). I know how to pass
> > the
> > values to another page using the DRBW, but I don't know how to modify the
> > code you provided to accomplish that.
> > --
> > John
> >
> >
> > "Ronx" wrote:
> >
> >> I could not get the code to work either.
> >>
> >> See if
> >> http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
> >> helps.
> >>
> >> --
> >> Ron Symonds
> >> Microsoft MVP (Expression)
> >> http://www.rxs-enterprises.org/fp
> >>
> >> Reply only to group - emails will be deleted unread.
> >>
> >>
> >>
> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
> >> > Thanks Ron,
> >> >
> >> > I have given that a try. After publishing here's what the web page
> >> > displays:
> >> >
> >> > Database Results Wizard Error
> >> > Unable to find operator in query string. Query string currently is
> >> > SELECT
> >> > Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude - ::Distance:: /
> >> > 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
> >> > Longitude
> >> > - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip = '::
> >> > EnterZip::')
> >> >
> >> > Here's what the grey code on the web page looks like:
> >> >
> >> > 
> >> >
> >> > --
> >> > John
> >> >
> >> >
> >> > "Ronx" wrote:
> >> >
> >> >> Don't place the code in the custom query. Place it directly in the
> >> >> web
> >> >> age - replace the SQL in the grey code starting s-qry=
> >> >>
> >> >> The save the page whilst still in code view.
> >> >> --
> >> >> Ron Symonds
> >> >> Microsoft MVP (Expression)
> >> >> http://www.rxs-enterprises.org/fp
> >> >>
> >> >> Reply only to group - emails will be deleted unread.
> >> >>
> >> >>
> >> >>
> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> >> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
> >> >> > Thanks Ronx.
> >> >> >
> >> >> > FrontPage won't let me save the new custom query in the Database
> >> >> > Wizard.
> >> >> > I
> >> >> > get this database connection error:
> >> >> >
> >> >> > Server error: Unable to retrieve schema information from the query:
> >> >> >
> >> >> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 /
> >> >> > 69.712
> >> >> > AS
> >> >> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 / 69.172
> >> >> > AS
> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
> >> >> >
> >> >> > against a database using the connection string
> >> >> >
> >> >> > DRIVER={Microsoft Access Driver (*.mdb,
> >> >> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
> >> >> >
> >> >> > The following error message comes from the database driver software;
> >> >> > it
> >> >> > may
> >> >> > appear in a different language depending on how the driver is
> >> >> > configured.
> >> >> > -------------------------------------------------------
> >> >> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
> >> >> > expected
> >> >> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
> >> >> >
> >> >> > Source: Microsoft OLE DB Provider for ODBC Drivers
> >> >> > Number: -2147217900 (0x80040e14)
> >> >> >
> >> >> > I notice that the error has replaced ::Distance:: with the numbers
> >> >> > 1,
> >> >> > 2, 3
> >> >> > and 4, but in my custom query I have ::Distance:: just as you have
> >> >> > in
> >> >> > your
> >> >> > suggested code.
> >> >> > --
> >> >> > John
> >> >> >
> >> >> >
> >> >> > "Ronx" wrote:
> >> >> >
> >> >> >> Open the page in code view, and find the SQL statement in the grey
> >> >> >> comments:
> >> >> >>
> >> >> >> Change this to:
> >> >> >>
> >> >> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat],
> >> >> >> Latitude -
> >> >> >> ::Distance:: / 69.712 AS
> >> >> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
> >> >> >> Longitude -
> >> >> >> ::Distance:: / 69.172 AS
> >> >> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
> >> >> >>
> >> >> >> I have not tested this.
> >> >> >>
> >> >> >> Notice that ::Distance:: refers to the field named Distance, and
> >> >> >> ::EnterZip:: refers to the field named EnterZip. Distance is
> >> >> >> assumed
> >> >> >> to
> >> >> >> be
> >> >> >> numeric, and EnterZip is a string value - you should have code to
> >> >> >> ensure
> >> >> >> these have the correct value types. Your version of the SQL still
> >> >> >> referred
> >> >> >> to Distance as a database field, and EnterZip as a vaiable.
> >> >> >> Also note that the sql statement above has been reformatted for the
> >> >> >> FrontPage database results wizard - it is not valid SQL.
> >> >> >> --
> >> >> >> Ron Symonds
> >> >> >> Microsoft MVP (Expression)
> >> >> >> http://www.rxs-enterprises.org/fp
> >> >> >>
> >> >> >> Reply only to group - emails will be deleted unread.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
> >> >> >> > Thank you very much for your help on this. I understand what you
> >> >> >> > are
> >> >> >> > saying
> >> >> >> > about the Distance field. I have tried revising the custom query
> >> >> >> > but
> >> >> >> > am
> >> >> >> > still having problems. Since I need to determine a range within
> >> >> >> > the
> >> >> >> > maximum
> >> >> >> > and minimum longitudes and latitudes to pass to the next form, I
> >> >> >> > set
> >> >> >> > it
> >> >> >> > up
> >> >> >> > like this:
> >> >> >> >
> >> >> >> > SELECT Latitude+Distance/69.172 AS [MaxLat],
> >> >> >> > Latitude-Distance/69.712
> >> >> >> > AS
> >> >> >> > [MinLat], Longitude+Distance/69.172 AS [MaxLon],
> >> >> >> > Longitude-Distance/69.172
> >> >> >> > AS
> >> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
> >> >> >> >
> >> >> >> > When I verify the query it says it was verified with the database
> >> >> >> > connection. But when I look at the published page I am getting
> >> >> >> > this
> >> >> >> > error
> >> >> >> > in
> >> >> >> > the database results region:
> >> >> >> >
> >> >> >> > Database Results Wizard Error
> >> >> >> > The operation failed. If this continues, please contact your
> >> >> >> > server
> >> >> >> > administrator.
> >> >> >> >
> >> >> >> > I have 2 text boxes on the form, EnterZip and Distance, and I
> >> >> >> > have
> >> >> >> > the
> >> >> >> > form
> >> >> >> > posting to the same page searchlocal1zip.asp that the form and
> >> >> >> > the
> >> >> >> > database
> >> >> >> > results region are on.
> >> >> >> >
> >> >> >> > What do you think I am still doing wrong?
> >> >> >> >
> >> >> >> > --
> >> >> >> > John
> >> >> >> >
> >> >> >> >
> >> >> >> > "Stefan B Rusynko" wrote:
> >> >> >> >
> >> >> >> >> See
> >> >> >> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
> >> >> >> >> and
> >> >> >> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
> >> >> >> >>
> >> >> >> >> Also check all your fields names in the query to make sure they
> >> >> >> >> are
> >> >> >> >> the
> >> >> >> >> same as in your DB
> >> >> >> >>
> >> >> >> >> PS
> >> >> >> >> Your query as written is looking for Distance in the DB because
> >> >> >> >> you
> >> >> >> >> have
> >> >> >> >> coded it like a DB field [Distance]
> >> >> >> >> - but Distance is not a DB field, it is a form result variable
> >> >> >> >> (same
> >> >> >> >> as
> >> >> >> >> EnterZip)
> >> >> >> >> - plus Like is meaningless criteria on a number like a 5 digit
> >> >> >> >> Zip
> >> >> >> >> code
> >> >> >> >>
> >> >> >> >> You will need to rewrite your query and logic to remove Distance
> >> >> >> >> from
> >> >> >> >> the
> >> >> >> >> DB values

30 May 2009 5:47 PM

Ronx

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.

Show quoteHide quote

"John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
news:58CAE8DC-B1AB-45F6-9899-E5073E4A79AE@microsoft.com...
> 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" wrote:
>
>> 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" <JohnGar***@discussions.microsoft.com> wrote in message
>> news:4F8B2D8E-09D0-4C73-908E-F47C4F99EF49@microsoft.com...
>> > Thanks Ron. This definitely works to generate the values that I need
>> > to
>> > pass
>> > to my next query (Maxlat, Minlat, Maxlon and Minlon). I know how to
>> > pass
>> > the
>> > values to another page using the DRBW, but I don't know how to modify
>> > the
>> > code you provided to accomplish that.
>> > --
>> > John
>> >
>> >
>> > "Ronx" wrote:
>> >
>> >> I could not get the code to work either.
>> >>
>> >> See if
>> >> http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
>> >> helps.
>> >>
>> >> --
>> >> Ron Symonds
>> >> Microsoft MVP (Expression)
>> >> http://www.rxs-enterprises.org/fp
>> >>
>> >> Reply only to group - emails will be deleted unread.
>> >>
>> >>
>> >>
>> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
>> >> news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
>> >> > Thanks Ron,
>> >> >
>> >> > I have given that a try. After publishing here's what the web page
>> >> > displays:
>> >> >
>> >> > Database Results Wizard Error
>> >> > Unable to find operator in query string. Query string currently is
>> >> > SELECT
>> >> > Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
>> >> > ::Distance:: /
>> >> > 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
>> >> > Longitude
>> >> > - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip =
>> >> > '::
>> >> > EnterZip::')
>> >> >
>> >> > Here's what the grey code on the web page looks like:
>> >> >
>> >> > 
>> >> >
>> >> > --
>> >> > John
>> >> >
>> >> >
>> >> > "Ronx" wrote:
>> >> >
>> >> >> Don't place the code in the custom query. Place it directly in the
>> >> >> web
>> >> >> age - replace the SQL in the grey code starting s-qry=
>> >> >>
>> >> >> The save the page whilst still in code view.
>> >> >> --
>> >> >> Ron Symonds
>> >> >> Microsoft MVP (Expression)
>> >> >> http://www.rxs-enterprises.org/fp
>> >> >>
>> >> >> Reply only to group - emails will be deleted unread.
>> >> >>
>> >> >>
>> >> >>
>> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
>> >> >> > Thanks Ronx.
>> >> >> >
>> >> >> > FrontPage won't let me save the new custom query in the Database
>> >> >> > Wizard.
>> >> >> > I
>> >> >> > get this database connection error:
>> >> >> >
>> >> >> > Server error: Unable to retrieve schema information from the
>> >> >> > query:
>> >> >> >
>> >> >> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 /
>> >> >> > 69.712
>> >> >> > AS
>> >> >> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 /
>> >> >> > 69.172
>> >> >> > AS
>> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
>> >> >> >
>> >> >> > against a database using the connection string
>> >> >> >
>> >> >> > DRIVER={Microsoft Access Driver (*.mdb,
>> >> >> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
>> >> >> >
>> >> >> > The following error message comes from the database driver
>> >> >> > software;
>> >> >> > it
>> >> >> > may
>> >> >> > appear in a different language depending on how the driver is
>> >> >> > configured.
>> >> >> > -------------------------------------------------------
>> >> >> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
>> >> >> > expected
>> >> >> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
>> >> >> >
>> >> >> > Source: Microsoft OLE DB Provider for ODBC Drivers
>> >> >> > Number: -2147217900 (0x80040e14)
>> >> >> >
>> >> >> > I notice that the error has replaced ::Distance:: with the
>> >> >> > numbers
>> >> >> > 1,
>> >> >> > 2, 3
>> >> >> > and 4, but in my custom query I have ::Distance:: just as you
>> >> >> > have
>> >> >> > in
>> >> >> > your
>> >> >> > suggested code.
>> >> >> > --
>> >> >> > John
>> >> >> >
>> >> >> >
>> >> >> > "Ronx" wrote:
>> >> >> >
>> >> >> >> Open the page in code view, and find the SQL statement in the
>> >> >> >> grey
>> >> >> >> comments:
>> >> >> >>
>> >> >> >> Change this to:
>> >> >> >>
>> >> >> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat],
>> >> >> >> Latitude -
>> >> >> >> ::Distance:: / 69.712 AS
>> >> >> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
>> >> >> >> Longitude -
>> >> >> >> ::Distance:: / 69.172 AS
>> >> >> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
>> >> >> >>
>> >> >> >> I have not tested this.
>> >> >> >>
>> >> >> >> Notice that ::Distance:: refers to the field named Distance, and
>> >> >> >> ::EnterZip:: refers to the field named EnterZip. Distance is
>> >> >> >> assumed
>> >> >> >> to
>> >> >> >> be
>> >> >> >> numeric, and EnterZip is a string value - you should have code
>> >> >> >> to
>> >> >> >> ensure
>> >> >> >> these have the correct value types. Your version of the SQL
>> >> >> >> still
>> >> >> >> referred
>> >> >> >> to Distance as a database field, and EnterZip as a vaiable.
>> >> >> >> Also note that the sql statement above has been reformatted for
>> >> >> >> the
>> >> >> >> FrontPage database results wizard - it is not valid SQL.
>> >> >> >> --
>> >> >> >> Ron Symonds
>> >> >> >> Microsoft MVP (Expression)
>> >> >> >> http://www.rxs-enterprises.org/fp
>> >> >> >>
>> >> >> >> Reply only to group - emails will be deleted unread.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> >> message
>> >> >> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
>> >> >> >> > Thank you very much for your help on this. I understand what
>> >> >> >> > you
>> >> >> >> > are
>> >> >> >> > saying
>> >> >> >> > about the Distance field. I have tried revising the custom
>> >> >> >> > query
>> >> >> >> > but
>> >> >> >> > am
>> >> >> >> > still having problems. Since I need to determine a range
>> >> >> >> > within
>> >> >> >> > the
>> >> >> >> > maximum
>> >> >> >> > and minimum longitudes and latitudes to pass to the next form,
>> >> >> >> > I
>> >> >> >> > set
>> >> >> >> > it
>> >> >> >> > up
>> >> >> >> > like this:
>> >> >> >> >
>> >> >> >> > SELECT Latitude+Distance/69.172 AS [MaxLat],
>> >> >> >> > Latitude-Distance/69.712
>> >> >> >> > AS
>> >> >> >> > [MinLat], Longitude+Distance/69.172 AS [MaxLon],
>> >> >> >> > Longitude-Distance/69.172
>> >> >> >> > AS
>> >> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '& EnterZip &')
>> >> >> >> >
>> >> >> >> > When I verify the query it says it was verified with the
>> >> >> >> > database
>> >> >> >> > connection. But when I look at the published page I am
>> >> >> >> > getting
>> >> >> >> > this
>> >> >> >> > error
>> >> >> >> > in
>> >> >> >> > the database results region:
>> >> >> >> >
>> >> >> >> > Database Results Wizard Error
>> >> >> >> > The operation failed. If this continues, please contact your
>> >> >> >> > server
>> >> >> >> > administrator.
>> >> >> >> >
>> >> >> >> > I have 2 text boxes on the form, EnterZip and Distance, and I
>> >> >> >> > have
>> >> >> >> > the
>> >> >> >> > form
>> >> >> >> > posting to the same page searchlocal1zip.asp that the form and
>> >> >> >> > the
>> >> >> >> > database
>> >> >> >> > results region are on.
>> >> >> >> >
>> >> >> >> > What do you think I am still doing wrong?
>> >> >> >> >
>> >> >> >> > --
>> >> >> >> > John
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Stefan B Rusynko" wrote:
>> >> >> >> >
>> >> >> >> >> See
>> >> >> >> >> http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
>> >> >> >> >> and
>> >> >> >> >> http://databases.aspfaq.com/general/how-do-i-access-min-max-sum-count-values-from-sql-statements.html
>> >> >> >> >>
>> >> >> >> >> Also check all your fields names in the query to make sure
>> >> >> >> >> they
>> >> >> >> >> are
>> >> >> >> >> the
>> >> >> >> >> same as in your DB
>> >> >> >> >>
>> >> >> >> >> PS
>> >> >> >> >> Your query as written is looking for Distance in the DB
>> >> >> >> >> because
>> >> >> >> >> you
>> >> >> >> >> have
>> >> >> >> >> coded it like a DB field [Distance]
>> >> >> >> >> - but Distance is not a DB field, it is a form result
>> >> >> >> >> variable
>> >> >> >> >> (same
>> >> >> >> >> as
>> >> >> >> >> EnterZip)
>> >> >> >> >> - plus Like is meaningless criteria on a number like a 5
>> >> >> >> >> digit
>> >> >> >> >> Zip
>> >> >> >> >> code
>> >> >> >> >>
>> >> >> >> >> You will need to rewrite your query and logic to remove
>> >> >> >> >> Distance
>> >> >> >> >> from
>> >> >> >> >> the
>> >> >> >> >> DB values

31 May 2009 3:50 PM

John Garvey

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

Show quoteHide quote

"Ronx" wrote:

> 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" <JohnGar***@discussions.microsoft.com> wrote in message
> news:58CAE8DC-B1AB-45F6-9899-E5073E4A79AE@microsoft.com...
> > 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" wrote:
> >
> >> 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" <JohnGar***@discussions.microsoft.com> wrote in message
> >> news:4F8B2D8E-09D0-4C73-908E-F47C4F99EF49@microsoft.com...
> >> > Thanks Ron. This definitely works to generate the values that I need
> >> > to
> >> > pass
> >> > to my next query (Maxlat, Minlat, Maxlon and Minlon). I know how to
> >> > pass
> >> > the
> >> > values to another page using the DRBW, but I don't know how to modify
> >> > the
> >> > code you provided to accomplish that.
> >> > --
> >> > John
> >> >
> >> >
> >> > "Ronx" wrote:
> >> >
> >> >> I could not get the code to work either.
> >> >>
> >> >> See if
> >> >> http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
> >> >> helps.
> >> >>
> >> >> --
> >> >> Ron Symonds
> >> >> Microsoft MVP (Expression)
> >> >> http://www.rxs-enterprises.org/fp
> >> >>
> >> >> Reply only to group - emails will be deleted unread.
> >> >>
> >> >>
> >> >>
> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
> >> >> news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
> >> >> > Thanks Ron,
> >> >> >
> >> >> > I have given that a try. After publishing here's what the web page
> >> >> > displays:
> >> >> >
> >> >> > Database Results Wizard Error
> >> >> > Unable to find operator in query string. Query string currently is
> >> >> > SELECT
> >> >> > Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
> >> >> > ::Distance:: /
> >> >> > 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
> >> >> > Longitude
> >> >> > - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip =
> >> >> > '::
> >> >> > EnterZip::')
> >> >> >
> >> >> > Here's what the grey code on the web page looks like:
> >> >> >
> >> >> > 
> >> >> >
> >> >> > --
> >> >> > John
> >> >> >
> >> >> >
> >> >> > "Ronx" wrote:
> >> >> >
> >> >> >> Don't place the code in the custom query. Place it directly in the
> >> >> >> web
> >> >> >> age - replace the SQL in the grey code starting s-qry=
> >> >> >>
> >> >> >> The save the page whilst still in code view.
> >> >> >> --
> >> >> >> Ron Symonds
> >> >> >> Microsoft MVP (Expression)
> >> >> >> http://www.rxs-enterprises.org/fp
> >> >> >>
> >> >> >> Reply only to group - emails will be deleted unread.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
> >> >> >> > Thanks Ronx.
> >> >> >> >
> >> >> >> > FrontPage won't let me save the new custom query in the Database
> >> >> >> > Wizard.
> >> >> >> > I
> >> >> >> > get this database connection error:
> >> >> >> >
> >> >> >> > Server error: Unable to retrieve schema information from the
> >> >> >> > query:
> >> >> >> >
> >> >> >> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2 /
> >> >> >> > 69.712
> >> >> >> > AS
> >> >> >> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 /
> >> >> >> > 69.172
> >> >> >> > AS
> >> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
> >> >> >> >
> >> >> >> > against a database using the connection string
> >> >> >> >
> >> >> >> > DRIVER={Microsoft Access Driver (*.mdb,
> >> >> >> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
> >> >> >> >
> >> >> >> > The following error message comes from the database driver
> >> >> >> > software;
> >> >> >> > it
> >> >> >> > may
> >> >> >> > appear in a different language depending on how the driver is
> >> >> >> > configured.
> >> >> >> > -------------------------------------------------------
> >> >> >> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement;
> >> >> >> > expected
> >> >> >> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
> >> >> >> >
> >> >> >> > Source: Microsoft OLE DB Provider for ODBC Drivers
> >> >> >> > Number: -2147217900 (0x80040e14)
> >> >> >> >
> >> >> >> > I notice that the error has replaced ::Distance:: with the
> >> >> >> > numbers
> >> >> >> > 1,
> >> >> >> > 2, 3
> >> >> >> > and 4, but in my custom query I have ::Distance:: just as you
> >> >> >> > have
> >> >> >> > in
> >> >> >> > your
> >> >> >> > suggested code.
> >> >> >> > --
> >> >> >> > John
> >> >> >> >
> >> >> >> >
> >> >> >> > "Ronx" wrote:
> >> >> >> >
> >> >> >> >> Open the page in code view, and find the SQL statement in the
> >> >> >> >> grey
> >> >> >> >> comments:
> >> >> >> >>
> >> >> >> >> Change this to:
> >> >> >> >>
> >> >> >> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat],
> >> >> >> >> Latitude -
> >> >> >> >> ::Distance:: / 69.712 AS
> >> >> >> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
> >> >> >> >> Longitude -
> >> >> >> >> ::Distance:: / 69.172 AS
> >> >> >> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
> >> >> >> >>
> >> >> >> >> I have not tested this.
> >> >> >> >>
> >> >> >> >> Notice that ::Distance:: refers to the field named Distance, and
> >> >> >> >> ::EnterZip:: refers to the field named EnterZip. Distance is
> >> >> >> >> assumed
> >> >> >> >> to
> >> >> >> >> be
> >> >> >> >> numeric, and EnterZip is a string value - you should have code
> >> >> >> >> to
> >> >> >> >> ensure
> >> >> >> >> these have the correct value types. Your version of the SQL
> >> >> >> >> still
> >> >> >> >> referred
> >> >> >> >> to Distance as a database field, and EnterZip as a vaiable.
> >> >> >> >> Also note that the sql statement above has been reformatted for
> >> >> >> >> the
> >> >> >> >> FrontPage database results wizard - it is not valid SQL.
> >> >> >> >> --
> >> >> >> >> Ron Symonds
> >> >> >> >> Microsoft MVP (Expression)
> >> >> >> >> http://www.rxs-enterprises.org/fp
> >> >> >> >>
> >> >> >> >> Reply only to group - emails will be deleted unread.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
> >> >> >> >> message
> >> >> >> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
> >> >> >> >> > 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

6 Jun 2009 10:45 AM

Ronx

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.

Show quoteHide quote

"John Garvey" <JohnGar***@discussions.microsoft.com> wrote in message
news:F624A868-9398-4FB4-9AC2-901F889A216B@microsoft.com...
> 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" wrote:
>
>> 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" <JohnGar***@discussions.microsoft.com> wrote in message
>> news:58CAE8DC-B1AB-45F6-9899-E5073E4A79AE@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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" <JohnGar***@discussions.microsoft.com> wrote in message
>> >> news:4F8B2D8E-09D0-4C73-908E-F47C4F99EF49@microsoft.com...
>> >> > Thanks Ron. This definitely works to generate the values that I
>> >> > need
>> >> > to
>> >> > pass
>> >> > to my next query (Maxlat, Minlat, Maxlon and Minlon). I know how to
>> >> > pass
>> >> > the
>> >> > values to another page using the DRBW, but I don't know how to
>> >> > modify
>> >> > the
>> >> > code you provided to accomplish that.
>> >> > --
>> >> > John
>> >> >
>> >> >
>> >> > "Ronx" wrote:
>> >> >
>> >> >> I could not get the code to work either.
>> >> >>
>> >> >> See if
>> >> >> http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
>> >> >> helps.
>> >> >>
>> >> >> --
>> >> >> Ron Symonds
>> >> >> Microsoft MVP (Expression)
>> >> >> http://www.rxs-enterprises.org/fp
>> >> >>
>> >> >> Reply only to group - emails will be deleted unread.
>> >> >>
>> >> >>
>> >> >>
>> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
>> >> >> > Thanks Ron,
>> >> >> >
>> >> >> > I have given that a try. After publishing here's what the web
>> >> >> > page
>> >> >> > displays:
>> >> >> >
>> >> >> > Database Results Wizard Error
>> >> >> > Unable to find operator in query string. Query string currently
>> >> >> > is
>> >> >> > SELECT
>> >> >> > Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
>> >> >> > ::Distance:: /
>> >> >> > 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS
>> >> >> > [MaxLon],
>> >> >> > Longitude
>> >> >> > - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip
>> >> >> > =
>> >> >> > '::
>> >> >> > EnterZip::')
>> >> >> >
>> >> >> > Here's what the grey code on the web page looks like:
>> >> >> >
>> >> >> > 
>> >> >> >
>> >> >> > --
>> >> >> > John
>> >> >> >
>> >> >> >
>> >> >> > "Ronx" wrote:
>> >> >> >
>> >> >> >> Don't place the code in the custom query. Place it directly in
>> >> >> >> the
>> >> >> >> web
>> >> >> >> age - replace the SQL in the grey code starting s-qry=
>> >> >> >>
>> >> >> >> The save the page whilst still in code view.
>> >> >> >> --
>> >> >> >> Ron Symonds
>> >> >> >> Microsoft MVP (Expression)
>> >> >> >> http://www.rxs-enterprises.org/fp
>> >> >> >>
>> >> >> >> Reply only to group - emails will be deleted unread.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> >> message
>> >> >> >> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
>> >> >> >> > Thanks Ronx.
>> >> >> >> >
>> >> >> >> > FrontPage won't let me save the new custom query in the
>> >> >> >> > Database
>> >> >> >> > Wizard.
>> >> >> >> > I
>> >> >> >> > get this database connection error:
>> >> >> >> >
>> >> >> >> > Server error: Unable to retrieve schema information from the
>> >> >> >> > query:
>> >> >> >> >
>> >> >> >> > s-sql="SELECT Latitude + 1 / 69.172 AS [MaxLat], Latitude - 2
>> >> >> >> > /
>> >> >> >> > 69.712
>> >> >> >> > AS
>> >> >> >> > [MinLat], Longitude + 3 / 69.172 AS [MaxLon], Longitude - 4 /
>> >> >> >> > 69.172
>> >> >> >> > AS
>> >> >> >> > [MinLon] FROM Zipcodes_2006 WHERE (Zip = '5')"
>> >> >> >> >
>> >> >> >> > against a database using the connection string
>> >> >> >> >
>> >> >> >> > DRIVER={Microsoft Access Driver (*.mdb,
>> >> >> >> > *.accdb)};DBQ=URL=fpdb/neatokeeno.mdb.
>> >> >> >> >
>> >> >> >> > The following error message comes from the database driver
>> >> >> >> > software;
>> >> >> >> > it
>> >> >> >> > may
>> >> >> >> > appear in a different language depending on how the driver is
>> >> >> >> > configured.
>> >> >> >> > -------------------------------------------------------
>> >> >> >> > [Microsoft][ODBC Microsoft Access Driver] Invalid SQL
>> >> >> >> > statement;
>> >> >> >> > expected
>> >> >> >> > 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
>> >> >> >> >
>> >> >> >> > Source: Microsoft OLE DB Provider for ODBC Drivers
>> >> >> >> > Number: -2147217900 (0x80040e14)
>> >> >> >> >
>> >> >> >> > I notice that the error has replaced ::Distance:: with the
>> >> >> >> > numbers
>> >> >> >> > 1,
>> >> >> >> > 2, 3
>> >> >> >> > and 4, but in my custom query I have ::Distance:: just as you
>> >> >> >> > have
>> >> >> >> > in
>> >> >> >> > your
>> >> >> >> > suggested code.
>> >> >> >> > --
>> >> >> >> > John
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Ronx" wrote:
>> >> >> >> >
>> >> >> >> >> Open the page in code view, and find the SQL statement in the
>> >> >> >> >> grey
>> >> >> >> >> comments:
>> >> >> >> >>
>> >> >> >> >> Change this to:
>> >> >> >> >>
>> >> >> >> >> s-sql="SELECT Latitude + ::Distance:: / 69.172 AS [MaxLat],
>> >> >> >> >> Latitude -
>> >> >> >> >> ::Distance:: / 69.712 AS
>> >> >> >> >> [MinLat], Longitude + ::Distance:: / 69.172 AS [MaxLon],
>> >> >> >> >> Longitude -
>> >> >> >> >> ::Distance:: / 69.172 AS
>> >> >> >> >> [MinLon] FROM Zipcodes_2006 WHERE (Zip = ':: EnterZip::')"
>> >> >> >> >>
>> >> >> >> >> I have not tested this.
>> >> >> >> >>
>> >> >> >> >> Notice that ::Distance:: refers to the field named Distance,
>> >> >> >> >> and
>> >> >> >> >> ::EnterZip:: refers to the field named EnterZip. Distance
>> >> >> >> >> is
>> >> >> >> >> assumed
>> >> >> >> >> to
>> >> >> >> >> be
>> >> >> >> >> numeric, and EnterZip is a string value - you should have
>> >> >> >> >> code
>> >> >> >> >> to
>> >> >> >> >> ensure
>> >> >> >> >> these have the correct value types. Your version of the SQL
>> >> >> >> >> still
>> >> >> >> >> referred
>> >> >> >> >> to Distance as a database field, and EnterZip as a vaiable.
>> >> >> >> >> Also note that the sql statement above has been reformatted
>> >> >> >> >> for
>> >> >> >> >> the
>> >> >> >> >> FrontPage database results wizard - it is not valid SQL.
>> >> >> >> >> --
>> >> >> >> >> Ron Symonds
>> >> >> >> >> Microsoft MVP (Expression)
>> >> >> >> >> http://www.rxs-enterprises.org/fp
>> >> >> >> >>
>> >> >> >> >> Reply only to group - emails will be deleted unread.
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
>> >> >> >> >> message
>> >> >> >> >> news:59B8BEC3-C6FA-463A-9567-2D44B4BECFA3@microsoft.com...
>> >> >> >> >> > 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

8 Jun 2009 2:57 PM

John Garvey

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

Show quoteHide quote

"Ronx" wrote:

> 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" <JohnGar***@discussions.microsoft.com> wrote in message
> news:F624A868-9398-4FB4-9AC2-901F889A216B@microsoft.com...
> > 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" wrote:
> >
> >> 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" <JohnGar***@discussions.microsoft.com> wrote in message
> >> news:58CAE8DC-B1AB-45F6-9899-E5073E4A79AE@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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" <JohnGar***@discussions.microsoft.com> wrote in message
> >> >> news:4F8B2D8E-09D0-4C73-908E-F47C4F99EF49@microsoft.com...
> >> >> > Thanks Ron. This definitely works to generate the values that I
> >> >> > need
> >> >> > to
> >> >> > pass
> >> >> > to my next query (Maxlat, Minlat, Maxlon and Minlon). I know how to
> >> >> > pass
> >> >> > the
> >> >> > values to another page using the DRBW, but I don't know how to
> >> >> > modify
> >> >> > the
> >> >> > code you provided to accomplish that.
> >> >> > --
> >> >> > John
> >> >> >
> >> >> >
> >> >> > "Ronx" wrote:
> >> >> >
> >> >> >> I could not get the code to work either.
> >> >> >>
> >> >> >> See if
> >> >> >> http://www.rxs-enterprises.org/tests/pages/zip_codes_read_me.htm
> >> >> >> helps.
> >> >> >>
> >> >> >> --
> >> >> >> Ron Symonds
> >> >> >> Microsoft MVP (Expression)
> >> >> >> http://www.rxs-enterprises.org/fp
> >> >> >>
> >> >> >> Reply only to group - emails will be deleted unread.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:39301C9C-A527-4394-8789-05A0C54382B8@microsoft.com...
> >> >> >> > Thanks Ron,
> >> >> >> >
> >> >> >> > I have given that a try. After publishing here's what the web
> >> >> >> > page
> >> >> >> > displays:
> >> >> >> >
> >> >> >> > Database Results Wizard Error
> >> >> >> > Unable to find operator in query string. Query string currently
> >> >> >> > is
> >> >> >> > SELECT
> >> >> >> > Latitude + ::Distance:: / 69.172 AS [MaxLat], Latitude -
> >> >> >> > ::Distance:: /
> >> >> >> > 69.712 AS [MinLat], Longitude + ::Distance:: / 69.172 AS
> >> >> >> > [MaxLon],
> >> >> >> > Longitude
> >> >> >> > - ::Distance:: / 69.172 AS [MinLon] FROM Zipcodes_2006 WHERE (Zip
> >> >> >> > =
> >> >> >> > '::
> >> >> >> > EnterZip::')
> >> >> >> >
> >> >> >> > Here's what the grey code on the web page looks like:
> >> >> >> >
> >> >> >> > 
> >> >> >> >
> >> >> >> > --
> >> >> >> > John
> >> >> >> >
> >> >> >> >
> >> >> >> > "Ronx" wrote:
> >> >> >> >
> >> >> >> >> Don't place the code in the custom query. Place it directly in
> >> >> >> >> the
> >> >> >> >> web
> >> >> >> >> age - replace the SQL in the grey code starting s-qry=
> >> >> >> >>
> >> >> >> >> The save the page whilst still in code view.
> >> >> >> >> --
> >> >> >> >> Ron Symonds
> >> >> >> >> Microsoft MVP (Expression)
> >> >> >> >> http://www.rxs-enterprises.org/fp
> >> >> >> >>
> >> >> >> >> Reply only to group - emails will be deleted unread.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "John Garvey" <JohnGar***@discussions.microsoft.com> wrote in
> >> >> >> >> message
> >> >> >> >> news:98D200AE-74E7-497F-B48B-4CC0322E52BF@microsoft.com...
> >> >> >> >> > 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