Record set problem returning a variable field

Ed_R · Oct 5, 2007

I have a query that I'm trying to return the correct value and am
having a problem with it. I believe the problem is the way I'm asking
to return the results of the field. There's a form where you can
enter a departure and arrival point and I want to then go to a table
and find the proper mileage betwen these two points. The field in the
table called location name will return the record where the record
equals the Arival_point_value from the form. Then I only ask to get
the field in that record equal to the departure_point_value. So far
everything works. But then the one line

miles_value = mileObj(" & Departure_point_value & ").Value

is where the problem is. Normally I'd hard code a field name in there
such as shown in the commented out line (Field name is Alexander) but
I don't know what field I want until the person picks that from the
form. So how do I input a variable into: miles_value = mileObj(" &
Departure_point_value & ").Value I tired this but doesn't like the
sytex. What am I doing wrong?? Below is shown the code. Thanks!

Arrival_point_value = Request.Form("Arrival_point")
Departure_point_value = Request.Form("Departure_point")

Set schConn = Server.CreateObject("ADODB.Connection")

schConn.Open Application("cwvo_ConnectionString")
Server.ScriptTimeOut = 600

Set mileObj = schConn.Execute("SELECT " & Departure_point_value & "
FROM Mileage_Chart WHERE Location_name = '" & Arrival_point_value &
"' ")

If mileObj.EOF <> true then

' miles_value = mileObj("Alexander").Value

miles_value = mileObj(" & Departure_point_value & ").Value

Response.write "The mileage from
your departure point: " & Departure_point_value & " to arrival point "
& Arrival_point_value & " is: " & miles_value & " miles "

End IF

Ronx · Oct 5, 2007

miles_value = mileObj(" & Departure_point_value & ").Value

should be

miles_value = mileObj(Departure_point_value).Value

Ed_R · Oct 7, 2007

OK, thanks for tip, that fixed that probelm. I had thought I tried
that, but for whatever reason now it works! However still have one
problem. Below is the code, it works OK as long as value for the
Departure_Point_value doesn't have any spaces in the field.

Exactly what I'm tryting to do is as follows: If you ever notice on
the edge of maps, they often have a mileage table where you look for
one city name in the rows going down the side, then find the other
city in the columns. The intersection of this row and column contains
the mileage between the two. I have a table that does the same thing
and you are just inputting what columns and rows to look at on a form.
So for example if I were looking for mileage with New York City being
the arrival point and Albany being the departure point, the script
works perfect and returns the right value. But if I reverse it and
make the departure point New York City, I get the following error.

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression New York City.

/officials/Mileage_check.asp, line 201

Even if departure point contains a hyphen, it pukes. I'm sure I could
replace all the loactions contains multiple words with underbars to
make it work, but is there a way to make it work without doing that?
I tried to change SELECT " & Departure_point_value & " FROM Mileage to
SELECT '" & Departure_point_value & "' FROM Mileage (addedd single
quote) but then got error "Item cannot be found in the collection
corresponding to the requested name or ordinal."

So is there something I can do to get around spaces

<%

Dim schConn
Dim mileobj

Arrival_point_value = Request.Form("Arrival_point")
Departure_point_value = Request.Form("Departure_point")

Set schConn = Server.CreateObject("ADODB.Connection")

schConn.Open Application("cwvo_ConnectionString")
Server.ScriptTimeOut = 600

Set mileObj = schConn.Execute("SELECT " & Departure_point_value & "
FROM Mileage_Chart WHERE School_name = '" & Arrival_point_value & "'
")

If mileObj.EOF <> true then

miles_value = mileObj(Departure_point_value).Value

Response.write "The mileage from
your departure point: " & Departure_point_value & " to the school: " &
Arrival_point_value & " is: " & miles_value & " miles "

End IF

%>

Stefan B Rusynko · Oct 7, 2007

You don't appear to understand the difference between database field name and values
The syntax is
SELECT FieldName FROM TableName WHERE Criteria
TableName is: Mileage_Chart
Criteria is: School_name = '" & Arrival_point_value & "'"
In your case Departure_point_value is a Criteria and Not a FieldName
- the FieldName must be a Field (not a value) in Mileage_Chart
- the FieldNames can not contain Spaces

The DB Field (named FieldName) can contain values like "New York City"
- if the FieldName is say DepartureCity your query would be

SELECT DepartureCity FROM Mileage_Chart WHERE School_name = '" & Arrival_point_value & "'"

--

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

| OK, thanks for tip, that fixed that probelm. I had thought I tried
| that, but for whatever reason now it works! However still have one
| problem. Below is the code, it works OK as long as value for the
| Departure_Point_value doesn't have any spaces in the field.
|
| Exactly what I'm tryting to do is as follows: If you ever notice on
| the edge of maps, they often have a mileage table where you look for
| one city name in the rows going down the side, then find the other
| city in the columns. The intersection of this row and column contains
| the mileage between the two. I have a table that does the same thing
| and you are just inputting what columns and rows to look at on a form.
| So for example if I were looking for mileage with New York City being
| the arrival point and Albany being the departure point, the script
| works perfect and returns the right value. But if I reverse it and
| make the departure point New York City, I get the following error.
|
|
| Microsoft JET Database Engine error '80040e14'
|
| Syntax error (missing operator) in query expression New York City.
|
| /officials/Mileage_check.asp, line 201
|
|
| Even if departure point contains a hyphen, it pukes. I'm sure I could
| replace all the loactions contains multiple words with underbars to
| make it work, but is there a way to make it work without doing that?
| I tried to change SELECT " & Departure_point_value & " FROM Mileage to
| SELECT '" & Departure_point_value & "' FROM Mileage (addedd single
| quote) but then got error "Item cannot be found in the collection
| corresponding to the requested name or ordinal."
|
| So is there something I can do to get around spaces
|
|
| <%
|
| Dim schConn
| Dim mileobj
|
| Arrival_point_value = Request.Form("Arrival_point")
| Departure_point_value = Request.Form("Departure_point")
|
| Set schConn = Server.CreateObject("ADODB.Connection")
|
| schConn.Open Application("cwvo_ConnectionString")
| Server.ScriptTimeOut = 600
|
| Set mileObj = schConn.Execute("SELECT " & Departure_point_value & "
| FROM Mileage_Chart WHERE School_name = '" & Arrival_point_value & "'
| ")
|
|
| If mileObj.EOF <> true then
|
|
| miles_value = mileObj(Departure_point_value).Value
|
|
| Response.write "The mileage from
| your departure point: " & Departure_point_value & " to the school: " &
| Arrival_point_value & " is: " & miles_value & " miles "
|
|
| End IF
|
|
|
| %>
|
| > miles_value = mileObj(" & Departure_point_value & ").Value
| >
| > should be
| >
| > miles_value = mileObj(Departure_point_value).Value
| >
| > --
| > Ron Symonds - Microsoft MVP (FrontPage)
| > Reply only to group - emails will be deleted unread.
| >
| > http://www.rxs-enterprises.org/fp
| >
|

Ed_R · Oct 8, 2007

No I do understand the difference. However as I described this is
like using a mileage chart on the edge of a map. I don't know what
locations the person is going to choose, so the criteria they pick;
one becomes the record I'm searching for (arrival_poiunt_value), and
the other becomes the name of the field that I'm looking for
(Departure_point_value). So therefore in the example I described,
there is a field called New York City, with the spaces in it. I
imported this table from an Excel spreadsheet and it accepted the
spaces in the field names. Leaving the line as "miles_value =
mileObj(Departure_point_value).Value" does work as long as I get rid
of the spaces.

So I guess the answer is I need to eliminate the spaces from all the
locations as the locations are also field names. Thanks!

You don't appear to understand the difference between database field name and values
The syntax is
SELECT FieldName FROM TableName WHERE Criteria
TableName is: Mileage_Chart
Criteria is: School_name = '" & Arrival_point_value & "'"
In your case Departure_point_value is a Criteria and Not a FieldName
- the FieldName must be a Field (not a value) in Mileage_Chart
- the FieldNames can not contain Spaces

The DB Field (named FieldName) can contain values like "New York City"
- if the FieldName is say DepartureCity your query would be

SELECT DepartureCity FROM Mileage_Chart WHERE School_name = '" & Arrival_point_value & "'"

--

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

| OK, thanks for tip, that fixed that probelm. I had thought I tried
| that, but for whatever reason now it works! However still have one
| problem. Below is the code, it works OK as long as value for the
| Departure_Point_value doesn't have any spaces in the field.
|
| Exactly what I'm tryting to do is as follows: If you ever notice on
| the edge of maps, they often have a mileage table where you look for
| one city name in the rows going down the side, then find the other
| city in the columns. The intersection of this row and column contains
| the mileage between the two. I have a table that does the same thing
| and you are just inputting what columns and rows to look at on a form.
| So for example if I were looking for mileage with New York City being
| the arrival point and Albany being the departure point, the script
| works perfect and returns the right value. But if I reverse it and
| make the departure point New York City, I get the following error.
|
|
| Microsoft JET Database Engine error '80040e14'
|
| Syntax error (missing operator) in query expression New York City.
|
| /officials/Mileage_check.asp, line 201
|
|
| Even if departure point contains a hyphen, it pukes. I'm sure I could
| replace all the loactions contains multiple words with underbars to
| make it work, but is there a way to make it work without doing that?
| I tried to change SELECT " & Departure_point_value & " FROM Mileage to
| SELECT '" & Departure_point_value & "' FROM Mileage (addedd single
| quote) but then got error "Item cannot be found in the collection
| corresponding to the requested name or ordinal."
|
| So is there something I can do to get around spaces
|
|
| <%
|
| Dim schConn
| Dim mileobj
|
| Arrival_point_value = Request.Form("Arrival_point")
| Departure_point_value = Request.Form("Departure_point")
|
| Set schConn = Server.CreateObject("ADODB.Connection")
|
| schConn.Open Application("cwvo_ConnectionString")
| Server.ScriptTimeOut = 600
|
| Set mileObj = schConn.Execute("SELECT " & Departure_point_value & "
| FROM Mileage_Chart WHERE School_name = '" & Arrival_point_value & "'
| ")
|
|
| If mileObj.EOF <> true then
|
|
| miles_value = mileObj(Departure_point_value).Value
|
|
| Response.write "The mileage from
| your departure point: " & Departure_point_value & " to the school: " &
| Arrival_point_value & " is: " & miles_value & " miles "
|
|
| End IF
|
|
|
| %>
|
| > miles_value = mileObj(" & Departure_point_value & ").Value
| >
| > should be
| >
| > miles_value = mileObj(Departure_point_value).Value
| >
| > --
| > Ron Symonds - Microsoft MVP (FrontPage)
| > Reply only to group - emails will be deleted unread.
| >
| >http://www.rxs-enterprises.org/fp
| >
|

Stefan B Rusynko · Oct 8, 2007

Correct - You can not have spaces in Field names in an Access DB

--

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

| No I do understand the difference. However as I described this is
| like using a mileage chart on the edge of a map. I don't know what
| locations the person is going to choose, so the criteria they pick;
| one becomes the record I'm searching for (arrival_poiunt_value), and
| the other becomes the name of the field that I'm looking for
| (Departure_point_value). So therefore in the example I described,
| there is a field called New York City, with the spaces in it. I
| imported this table from an Excel spreadsheet and it accepted the
| spaces in the field names. Leaving the line as "miles_value =
| mileObj(Departure_point_value).Value" does work as long as I get rid
| of the spaces.
|
| So I guess the answer is I need to eliminate the spaces from all the
| locations as the locations are also field names. Thanks!
|
|
|
| > You don't appear to understand the difference between database field name and values
| > The syntax is
| > SELECT FieldName FROM TableName WHERE Criteria
| > TableName is: Mileage_Chart
| > Criteria is: School_name = '" & Arrival_point_value & "'"
| > In your case Departure_point_value is a Criteria and Not a FieldName
| > - the FieldName must be a Field (not a value) in Mileage_Chart
| > - the FieldNames can not contain Spaces
| >
| > The DB Field (named FieldName) can contain values like "New York City"
| > - if the FieldName is say DepartureCity your query would be
| >
| > SELECT DepartureCity FROM Mileage_Chart WHERE School_name = '" & Arrival_point_value & "'"
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > _____________________________________________
| >
| >
| > | OK, thanks for tip, that fixed that probelm. I had thought I tried
| > | that, but for whatever reason now it works! However still have one
| > | problem. Below is the code, it works OK as long as value for the
| > | Departure_Point_value doesn't have any spaces in the field.
| > |
| > | Exactly what I'm tryting to do is as follows: If you ever notice on
| > | the edge of maps, they often have a mileage table where you look for
| > | one city name in the rows going down the side, then find the other
| > | city in the columns. The intersection of this row and column contains
| > | the mileage between the two. I have a table that does the same thing
| > | and you are just inputting what columns and rows to look at on a form.
| > | So for example if I were looking for mileage with New York City being
| > | the arrival point and Albany being the departure point, the script
| > | works perfect and returns the right value. But if I reverse it and
| > | make the departure point New York City, I get the following error.
| > |
| > |
| > | Microsoft JET Database Engine error '80040e14'
| > |
| > | Syntax error (missing operator) in query expression New York City.
| > |
| > | /officials/Mileage_check.asp, line 201
| > |
| > |
| > | Even if departure point contains a hyphen, it pukes. I'm sure I could
| > | replace all the loactions contains multiple words with underbars to
| > | make it work, but is there a way to make it work without doing that?
| > | I tried to change SELECT " & Departure_point_value & " FROM Mileage to
| > | SELECT '" & Departure_point_value & "' FROM Mileage (addedd single
| > | quote) but then got error "Item cannot be found in the collection
| > | corresponding to the requested name or ordinal."
| > |
| > | So is there something I can do to get around spaces
| > |
| > |
| > | <%
| > |
| > | Dim schConn
| > | Dim mileobj
| > |
| > | Arrival_point_value = Request.Form("Arrival_point")
| > | Departure_point_value = Request.Form("Departure_point")
| > |
| > | Set schConn = Server.CreateObject("ADODB.Connection")
| > |
| > | schConn.Open Application("cwvo_ConnectionString")
| > | Server.ScriptTimeOut = 600
| > |
| > | Set mileObj = schConn.Execute("SELECT " & Departure_point_value & "
| > | FROM Mileage_Chart WHERE School_name = '" & Arrival_point_value & "'
| > | ")
| > |
| > |
| > | If mileObj.EOF <> true then
| > |
| > |
| > | miles_value = mileObj(Departure_point_value).Value
| > |
| > |
| > | Response.write "The mileage from
| > | your departure point: " & Departure_point_value & " to the school: " &
| > | Arrival_point_value & " is: " & miles_value & " miles "
| > |
| > |
| > | End IF
| > |
| > |
| > |
| > | %>
| > |
| > | > miles_value = mileObj(" & Departure_point_value & ").Value
| > | >
| > | > should be
| > | >
| > | > miles_value = mileObj(Departure_point_value).Value
| > | >
| > | > --
| > | > Ron Symonds - Microsoft MVP (FrontPage)
| > | > Reply only to group - emails will be deleted unread.
| > | >
| > | >http://www.rxs-enterprises.org/fp
| > | >
| > |
|
|

Record set problem returning a variable field

Ed_R

Ronx

Ed_R

Stefan B Rusynko

Ed_R

Stefan B Rusynko