Link to home
Start Free TrialLog in
Avatar of Malloy1446
Malloy1446

asked on

Link to a page which was generated by a drop-down list

I have a page (attached:  default.txt) which is a drop down list to select a location.
Based on selection another page is generated with the results (attached: results.txt) based on request.form("city")

I have another page (attached: newPage.txt). On this page I want a link to results.txt with a hard code  where I want to link to the results page by hard coding the city.

Is this possible?
default.txt
Results.txt
NewPage.txt
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

It is possible, but why not keep it all on one page and without a page refresh.   You can use jquery to chain the drop downs. It means throwing all the data to the drop downs at one time and let javascript/jquery do the magic.

http://www.appelsiini.net/projects/chained
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="jquery.chained.min.js"></script>

<select id="mark" name="mark">
  <option value="">--</option>
  <option value="bmw">BMW</option>
  <option value="audi">Audi</option>
</select>
<select id="series" name="series">
  <option value="">--</option>
  <option value="series-3" class="bmw">3 series</option>
  <option value="series-5" class="bmw">5 series</option>
  <option value="series-6" class="bmw">6 series</option>
  <option value="a3" class="audi">A3</option>
  <option value="a4" class="audi">A4</option>
  <option value="a5" class="audi">A5</option>
</select>

Open in new window

in your results page, change the line

StrCity = request.form("city")

to

StrCity = request("city")

and in the newPage file, change your link to:

<a href="ComparisonChart/Directory/Clinical/results.asp?cityity=Milwaukee">Directory Link</a>  &nbsp;&nbsp;
Avatar of Malloy1446
Malloy1446

ASKER

I need the results.txt page to function by using the request.form("city")

The new page needs a link which can somehow be hard coded with a "city".
by changing it to

StrCity = request.("city")

it'll be interpreted as both Request.Form (which is used in your default page because it POSTs the data) as well as Request.QueryString (which will be used by the hard-coded link with the query string variable in NewPage.asp

give it a shot and you'll see it does exactly what you want it to
You were right it did not change the function of the results.txt page.

BUT, when using the link on the NewPage.txt it pulls up ALL results not just the results for Milwaukee.
can you post the changes you made to the link in NewPage?
I have the dim statement:

<%
dim StrCity
StrCity = request("City")
%>
   
and the link I am using is:
<a href="ComparisonChart/Directory/Clinical/results.asp?city=Milwaukee">
when you go to results.asp, do you see "city=Milwaukee" as a query string in the url bar?

if you uncomment the line below, do you see the city in the where clause?

'response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
when you go to results.asp, do you see "city=Milwaukee" as a query string in the url bar?
NO.

The Where clause:

WHERE (FacilityStatus = 'Open') AND (SDDisplay = 'Y') AND City = 'Milwaukee' ORDER BY City, State, FacName, SDLName
so when you click on

<a href="ComparisonChart/Directory/Clinical/results.asp?city=Milwaukee">

city=Milwaukee does not appear in the address bar anywhere? I don't see how that is possible. Is this a public page I can look at?
Milwaukee does not appear in the address bar anywhere. That was my dilemma also. Unfortunately it is not a public page.

Do I need to have all the WHERE qualifiers identified in my NewPage.txt somehow?

WHERE (FacilityStatus = 'Open') AND (SDDisplay = 'Y') AND City = 'Milwaukee'
That is very weird, unless you have url rewriting turned on. That would explain why you don't see them.

Now, the fact that the where clause has the criteria you want  is also bizarre. I think it would be helpful if you could attach the html structure of the page, meaning to load the default.asp, view source, and copy & paste the code here, or attach it as a file. This will allow me to set up a page that is very similar to yours and hopefully see something we missed earlier
On the results page, at the very top, try adding temp code and see what you get.
response.write "<hr>"& request.querystring&"<hr>"&request.form

Open in new window

I get

city=Milwaukee
Alright so it is getting passed over, that's good.

You just need to add the variable to your sql and you should be good to go. I'm on my phone right now and can't really look at your code but make sure the variable you're using in your sql is set by

City = request("city")
OK... I think I need to start from scratch. I have stripped down my code. I think if I have a Request.form("ChartID") variable it will help with the original problem. The field "ChartID" is a unique field.

I have a glitch in the form I cannot resolve.

The default is generating the drop-down list correctly.
The results page is getting the ChartID, but is selecting a different record.

Milwaukee should be (ChartID 112).
For some reason it is pulling up Wichita (ChartID 176) for the display!
default.txt
results.txt
Results-Source-Code.txt
what is the source code for default.asp?
Source code attached
default-Source.txt
also, in your results_source_code file you posted, you're writing out the recordset value, not the query string value, so where you have

Response.Write objRS("ChartID") & objRS("City")

you probably want to do

Response.Write Request("chartID")

I'm also noticing you don't have any kind of where clause in your sql statement, so that's why it's not filtering by chartID
i believe you want to do something like:

sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID " _
                "where tblGeneral.ChartID = " & Request("chartID")

Open in new window

I modified the code as suggested. Now I am getting "The website cannot display the page"

Eventually I will be using all the fields identified in the SQL statement to display on results.
If you do a response.write of the sql before you execute the sql what do you get?
Same thing... Website cannot display the page."
Did you put a Response.End after the response.write line?
No.
Added a Response.End

Still getting the Website cannot display the page.
i spotted the error, use this for your sql:

sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID " & _
                "where tblGeneral.ChartID = " & Request("chartID")

Open in new window

Still getting the "Website cannot display the page."

New results.txt attached.
results.txt
try this for your code and tell me what it writes out to the screen

<%
on error resume next
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _
		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID " & _
                "where tblGeneral.ChartID = " & Request("chartID")

if err.number <> 0 then Response.Write "Error #1: & err.description


'Diagnostic for sql statement errors
response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	
		Set objRS = Server.CreateObject("ADODB.Recordset")
		objRS.Open sql, objConn 
if err.number <> 0 then response.write "error #2:" & err.description


Response.Write Request("chartID")

Open in new window


i'm going to have to pick it back up in the morning, getting late where I'm at. a few things i need clarification on:

1) you do want to filter in your sql query by chartID, and not city, correct?
2) can you explain what logic you're trying to build here so I make sure i'm on the same page as you?
I am still getting the "Website cannot display the page message"

This is a directory app. Basically select a location then see the specific results for the location.

What I am hoping to do....

On default.txt I want a drop-down list which is generated from the database. The display list will include VISNID, City, State which will correspond to a unique ChartID. The ChartID will not display in the drop down list but will be used on the results.txt page to retrieve the information (SQL statement) for the page. All the fields in the sql statement will eventually be displayed. Hope this makes sense!

Thanks for your help.
Are you getting a 400 error?  meaning can't be found

Are you sure the page is on the server or are you doing any kind of url routing?  I think the issue is the server is not seeing the page.
can you restore the code to a point where it was working and then paste that code?
I have read through in more detail the coding and I think it would be best to start over for this portion.  There are some small items such as using while/wend for your looping which should be do until/loop. And actually spitting that out to an array is an even better choice.  

If you look what I originally suggested, it is  a very simple solution to make a selection of something then base the 2nd selection on the data from the first selection.  

When things start getting overly complex, that is when I like to say that was a good learning experience and move to something new.

It is worth giving it a try.  You can see that you simply create your first drop down as normal, then the 2nd drop down data contains all the possibilities where the class of the 2nd drop down is equal to the value of the first.  

If you select "BMW" in the example, the 2nd selection will only show those values where the class equals "BMW".  You have to use the jquery plug in of course.  But it makes it very easy for the end user.

It is worth 15 minutes to give it  a try http:Q_28564975.html#a40450916
I disagree. The solution you're trying to implement is not overly complicated, there's just one thing that's causing the pledge to bug out. It's a simple filtering dropdown box, not a dependent dropdown. Let's continue with the path we're on, I'm positive we're close
I still think you need to step back and start over.  Some of this is just bandaids.  When you have things like While/Wend instead of Do/Loop is an example of not best practice.  Or even better sending data to an array and closing that connection right away.

Or using rs.movefirst which would be prevented from if you sent data to an array instead of a recordset.

Your error you last described sounds like the page is not being seen though.
OK.... I've been looking at the code. I have a version which is working.
The default.txt page is using City as the variable to pass to results.txt
It is working correctly passing the variable and displaying the page correctly.

I need to use ChartID as the variable because there are duplicate entries for city (ie Columbia Missouri and Columbia South Carolina) which will cause a problem when I need to make the link on the NewPage.txt.

I am attaching a working copy of the code for both default.txt and results.txt
default.txt
results.txt
Also, there is an option in the drop down list for ALL. I would still want to include that as a selection  to the user.
Good choice.  The value should always be the unique id you use to relate one table to the other.  

Is this working now?
No. There is something wrong with the SQL statement. I am passing ChartID to the results.txt page. The error I am getting is

Microsoft JET Database Engine error '80004005'
Specified field "ChartID' could refer to more than one table listed in the FROM clause of your SQL statement.
SQL statement attached
SQL.txt
I think it would help if you posted your code using the code block instead of as a link.  That way we can see the line numbers.  Also the error will tell you which line number the error is on.

Looking at this section of your sql, notice the where clause
If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
End If

Open in new window

vs the first part of your sql
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "

Open in new window


You probably need to use

   strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' "   

 

User generated image
My code is attached and generates "Website cannot display the page."

If I delete the code from lines 116 thru 253, I get the following error message:
     Microsoft JET Database Engine error '80040e07'
     Data type mismatch in criteria expression.
     results3.asp, line 114

<%
response.write "<hr>"&request.form&"<hr>"
%>



<!-- #Include Virtual="ConnChart2.inc" -->

<%
dim title
title = "Directory"
%>

<html>

<head>
<meta name="keywords" content="">
<meta name="Subject" content="">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 12.0">
<meta name="ProgId" content="FrontPage.Editor.Document">

<title><%=title%></title>


<link rel=stylesheet type="text/css"
  href="CodeReuse/style.css">

<script language="JavaScript">
	<!--//BEGIN Script
	function new_window(url) {
	link = window.open(url,"Link","toolbar=0,location=0,directories=0,status=0,menubar=0,scrollbars=1,resizable=1,width=600,height=500,left=10,top=10");
	}
	//END Script-->
</script>

<SCRIPT LANGUAGE="JavaScript">
	<!-- Begin
	function formHandler(){
	var URL = document.form.site.options[document.form.site.selectedIndex].value;
	window.location.href = URL;
	// End -->
	}
</SCRIPT>

</head>

<body>


<!-- PAGE CONTENT BEGINS HERE -->




<h1>Directory Search Results</h1>

<%

Dim objRS, strChartID, sql
	StrChartID = request.form("ChartID")
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "



Dim strWhere

'strWhere = strWhere
strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "
'strWhere = strWhere & "AND tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "



If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
'   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
	strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 
End If


'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = "WHERE " & Right(strWhere, Len(strWhere) - 4)
End If

If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY City, State, FacName, SDLName"
	Else If Request.form("sort_by") = "State" then
		sql = sql & strWhere & "ORDER BY State, City, FacName, SDLName"
   	Else 
   		sql = sql & strWhere & "ORDER BY FacName, SDLName"
   	End If
End If	




'Diagnostic for sql statement errors
response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	
Response.Write "testing"

	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn 

	Dim StrChartID
	
'Determine if there are results
	If objRS.EOF then
		'If no records
		Response.Write "Please check your search criteria. There are no records listed for the search criteria selected."
	Else 

		objRS.MoveFirst
		
		StrChartID = objRS("ChartID")
		
		Response.Write "<b>"


	Response.Write "ABCDEF" & "<br>"

	If Len(objRS("CorpName"))>2 then
		Response.Write objRS("CorpName") & "<br>"
	End If
	
	If Len(objRS("FacName"))>2 then
		Response.Write objRS("FacName") & ", "
	End If

	Response.Write "VISN " & objRS("VISNID") & "<br>"
	
	If Len(objRS("StreetAddr"))>2 then
		Response.Write objRS("StreetAddr") & "<br>"
	End If
	
	If Len(objRS("City"))>2 then
		Response.Write objRS("City") & ", " & objRS("State") & " " & objRS("Zipcode") & "<p></b>"
	End If




While NOT objRS.EOF

	If StrChartID = objRS("ChartID") THEN
	


'-------------------- STAFF DISLAY --------------------
				
'The following line will only display staff information when SDDisplay is Yes				
'If objRS("SDDisplay") = "Y" then
				
	If Len(objRS("SDFName"))>2 then
		Response.Write "<blockquote><b>" & objRS("SDFName") & " " & objRS("SDLName") & "</b>"
	End If
	
	If Len(objRS("SDPosition"))>2 then
		Response.Write ", " & objRS("SDPosition")
	End If

	Response.Write "<br>"

	If Len(objRS("SDPhone"))>2 then
		Response.Write "Phone: " & objRS("SDPhone")
		
		If Len(objRS("SDExt"))>2 then
			Response.Write "  x" & objRS("SDExt") 
		End If
	End If

	Response.Write "<br>"

	If Len(objRS("SDEmail"))>2 then
		Response.Write ("<a href=""mailto:" & objRS("SDEmail") & """>" & objRS("SDEmail") & "</a>") & "<br>"
	End If
	
	Response.Write "<br></blockquote>"
	

'Advance the recordset
objRS.MoveNext
   
			Else If NOT ObjRS.EOF Then
   				StrChartID = objRS("ChartID")

				Response.Write "<hr width=600 align=left><b>"
				


				If Len(objRS("CorpName"))>2 then
					Response.Write objRS("CorpName") & "<br>"
				End If
	
				If Len(objRS("FacName"))>2 then
					Response.Write objRS("FacName") & ", "
				End If
	
				Response.Write "VISN " & objRS("VISNID") & "<br>"
		
				If Len(objRS("StreetAddr"))>2 then
					Response.Write objRS("StreetAddr") & "<br>"
				End If

				If Len(objRS("City"))>2 then
					Response.Write objRS("City") & ", " & objRS("State") & " " & objRS("Zipcode") & "</b><p>"
				End If 
				


' ----------------------------------------------------------------
				
If objRS("LibraryStatus") = "Closed" then
	Response.Write "<b>Library Status: " & objRS("LibraryStatus") & "</b><br>"
	Response.Write "Your facility does not support a library or library staff.<br>"
	Response.Write "Contact the individual listed below to determine what arrangements have <br>"
	Response.Write "been made for library services such as literature searching and interlibrary loan: <p>"
End If

If objRS("LibraryStatus") = "Active" THEN
	Response.Write "Contact your local library staff for assistance: <p>"
End If
			
If objRS("LibraryStatus") = "Inactive" THEN
	Response.Write "Your facility does not currently have library staff.<br>"
	Response.Write "Contact the individual listed below to determine what arrangements have<br>"
	Response.Write "been made for library services such as literature searching and interlibrary loan: <p>"
End If
			
		
		
			End If
				
	End If

Wend	

objRS.Close
Set objRS = Nothing
End If

Response.Write "</TABLE>" & VbCrLf



%>

            
</body>

</html>

Open in new window

That error sounds like something is wrong with your WHERE clause.  You can test by removing the where clause or manually hard coding.

But I think the error is here
If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
'   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
	strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 
End If

Open in new window


Is the ChartID in the table numeric or a string?  You have it here as string by adding the single quotes.  

Are you expecting ChartID to have single quotes?

On the code you just posted, you are outputting the rendered SQL on line 109.  Can you post the sql statement being executed?
ChartID is a numeric field in the database.

This form (created by someone else) used to have multiple drop down options but they have been eliminated. It now just has the ONE option which I want tied to ChartID.

Do I even need the WHERE dim?

I still want the single record display, but if no location is selected, I want ALL records displayed.

I am attaching the only SQL statement I have.

Dim objRS, ChartID, sql
	ChartID = request.form("ChartID")
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "


Dim strWhere

'strWhere = strWhere
'strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "
strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 


Dim strWhere
strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "
 

If StrChartID <> "" then
'   StrChartID=Replace(StrChartID,"'","''")
   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
End If

Open in new window

What I as asking is what is the rendered SQL statement.  When you run the page you have this code that should output the sql statement being used.  This will tells the story.
'Diagnostic for sql statement errors
response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"

Open in new window


What I am also wondering is in the sql where clause you have on line 85
 StrChartID=Replace(StrChartID,"'","''")

Open in new window

But strChartID is supposed to be a number that you get from line 61
StrChartID = request.form("ChartID")

Open in new window

There shouldn't be any single quotes in a number.  

Run the page and lets see the rendered sql, not the sql in your vbs.
Following is the rendered SQL statement:

Directory Search Results
SQL statement:SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, tblGeneral.Location, tblGeneral.Hours, tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID WHERE tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' ORDER BY City, State, FacName, SDLName
testing


Microsoft JET Database Engine error '80040e14'
Undefined function 'Request' in expression.
results3.asp, line 114

The above was generated from this code:

Dim objRS, ChartID, sql
	ChartID = request.form("ChartID")
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "


            
Dim strWhere

'strWhere = strWhere
'strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "

strWhere = strWhere & "AND tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "



If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
'   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
	strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 
End If


'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = "WHERE " & Right(strWhere, Len(strWhere) - 4)
End If

If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY City, State, FacName, SDLName"
	Else If Request.form("sort_by") = "State" then
		sql = sql & strWhere & "ORDER BY State, City, FacName, SDLName"
   	Else 
   		sql = sql & strWhere & "ORDER BY FacName, SDLName"
   	End If
End If



'Diagnostic for sql statement errors
response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	
Response.Write "testing<p>"

	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn 

Open in new window

Perfect example on what what happens when an expert tries to take over a question, a lot of confusion occurs. This was at the point I was at...

Anyways, change your where clause code to

strWhere = strWhere & "AND tblGeneral.ChartID =" & Request('ChartID') &  " AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "
From your output, you can see that this is not right
WHERE tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y'

Open in new window

Going back to your sql, that needs to be broken out.   However, this is a little flawed execution of how to dynamically generate an sql statement.  

You should be use a variable for the chart id and not stick a request("variable") in there without first making sure it is a number.  But that is the issue as detected by the output.
I would suggest changing this

	StrChartID = request.form("ChartID")
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "



Dim strWhere

'strWhere = strWhere
strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "
'strWhere = strWhere & "AND tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "



If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
'   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
	strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 
End If

Open in new window

To this
	StrChartID = request.form("ChartID")
	if isnumeric(strChartID) then
		StrChartID = request.form("ChartID")
		else
		StrChartID = 0  ' set to zero if bad data. This will select nothing.'
	end if
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "



Dim strWhere

'strWhere = strWhere
strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "
'strWhere = strWhere & "AND tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "



If StrChartID <> "" then
                       
	strWhere = strWhere & "AND tblGeneral.ChartID = " & StrChartID & " 'no single quotes needed for a number  
End If

Open in new window

Also, it was a little confusing to sort out that StrChartID is a number.  It will not affect your code, but probably should be  intChartID to designate it is a number.
Perfect example of an amateur posing as an expert  http:Q_28564975.html#a40456567

That is SQL injection 101 and the start of the errors you are receiving.   Never trust user input.
Personal attacks and name calling and I'm the amateur, interesting....

Usually I tend to resolve the immediate problem, once that's working then I offer suggestions to make the code more stable.  This tends to avoid a lot of confusion.
I wish that would have worked! I get the "Website cannot display the page."

I will include the code again so you can see it.
It would help to have the errors go to the browser. You can do this by going into iis and under the ASP section, make sure the option "send errors to browser" is set to true
And don't forget to post the latest version of your code
<%

Dim objRS, ChartID, sql
	ChartID = request.form("ChartID")
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "


            
Dim strWhere

'strWhere = strWhere
'strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "

'strWhere = strWhere & "AND tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "
strWhere = strWhere & "AND tblGeneral.ChartID =" & Request('ChartID') &  " AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "


If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
'   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
	strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 
End If





'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = "WHERE " & Right(strWhere, Len(strWhere) - 4)
End If

If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY City, State, FacName, SDLName"
	Else If Request.form("sort_by") = "State" then
		sql = sql & strWhere & "ORDER BY State, City, FacName, SDLName"
   	Else 
   		sql = sql & strWhere & "ORDER BY FacName, SDLName"
   	End If
End If






'Diagnostic for sql statement errors
response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	

Open in new window

Try removing the blank row in your sql statement (line 13)
Still no change "Website cannot display the page."
Do you have errors being sent out to the browser?
No errors listed. Just the "Website cannot display the page"
Are you able to log into your server and surf on the server? or is this shared hosting?
I still think the error is in the where clause.

In your output of the rendered sql you had
WHERE tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' ORDER BY City, State, FacName, SDLName
testing


That should be a number and not request.  

Try an experiment and hard code the WHERE clause with a ChartID you know exists. If that works, then we know that is the issue.
In the code you most recently posted, you have an error in your single and double quotes.

Change to
<%

Dim objRS, ChartID, sql
	ChartID = request.form("ChartID")
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "


            
Dim strWhere

'strWhere = strWhere
'strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "

'strWhere = strWhere & "AND tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "
strWhere = strWhere & "AND tblGeneral.ChartID =" & Request("ChartID") &  " AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "


If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
'   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
	strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 
End If





'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = "WHERE " & Right(strWhere, Len(strWhere) - 4)
End If

If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY City, State, FacName, SDLName"
	Else If Request.form("sort_by") = "State" then
		sql = sql & strWhere & "ORDER BY State, City, FacName, SDLName"
   	Else 
   		sql = sql & strWhere & "ORDER BY FacName, SDLName"
   	End If
End If






'Diagnostic for sql statement errors
response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	

Open in new window


You had single quotes in your request('ChartID') and should be request("ChartID") although as I mentioned, this is a bad practice and opens you up to sql injection.    You should at least check that the request is indeed a number before using it in the WHERE clause or anywhere in your sql.
Scott, we are sooo close.

I will post the most current code.
Code is generating results correctly when I select a specific location.
I am also able to link to the results page from another page by hardcoding the ChartID in the URL (ie results.txt?ChartID=112)

The last problem with the WHERE statement is when the user selects ALL. I then get an error:
   
      Microsfot JET Database Engine error '80040e14'
      Syntax error (missing operator in query expression 'tblGeneral.ChartID = AND FacilityStatus = 'Open' AND SDDisplay = 'Y''.
      results.3.asp,line 27

Also is it important I add the following when identify the Dim ChartID:
	
if isnumeric(strChartID) then
		StrChartID = request.form("ChartID")
		else
		StrChartID = 0  ' set to zero if bad data. This will select nothing.'
	end if

Open in new window




<%
response.write "<hr>"&request.form&"<hr>"
%>



<!-- #Include Virtual="Chart2.inc" -->

<%
dim title
title = "Directory"
%>

<html>

<head>
<meta name="keywords" content="">
<meta name="Subject" content="">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 12.0">
<meta name="ProgId" content="FrontPage.Editor.Document">

<title><%=title%></title>


<link rel=stylesheet type="text/css"
  href="CodeReuse/style.css">

<script language="JavaScript">
	<!--//BEGIN Script
	function new_window(url) {
	link = window.open(url,"Link","toolbar=0,location=0,directories=0,status=0,menubar=0,scrollbars=1,resizable=1,width=600,height=500,left=10,top=10");
	}
	//END Script-->
</script>

<SCRIPT LANGUAGE="JavaScript">
	<!-- Begin
	function formHandler(){
	var URL = document.form.site.options[document.form.site.selectedIndex].value;
	window.location.href = URL;
	// End -->
	}
</SCRIPT>

</head>

<body>


<!-- PAGE CONTENT BEGINS HERE -->




<h1>Directory Search Results</h1>

<%

Dim objRS, ChartID, sql
	ChartID = request.form("ChartID")
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "


            
Dim strWhere

'strWhere = strWhere
'strWhere = strWhere & "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "

'strWhere = strWhere & "AND tblGeneral.ChartID = Request('ChartID') AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "
strWhere = strWhere & "AND tblGeneral.ChartID =" & Request("ChartID") &  " AND FacilityStatus = 'Open' AND SDDisplay = 'Y' "


If StrChartID <> "" then
   StrChartID=Replace(StrChartID,"'","''")
'   strWhere = strWhere & "AND ChartID = '" & StrChartID & "' "                       
	strWhere = strWhere & "AND tblGeneral.ChartID = '" & StrChartID & "' " 
End If





'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = "WHERE " & Right(strWhere, Len(strWhere) - 4)
End If



If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY City, State, FacName, SDLName"
	Else If Request.form("sort_by") = "State" then
		sql = sql & strWhere & "ORDER BY State, City, FacName, SDLName"
   	Else 
   		sql = sql & strWhere & "ORDER BY FacName, SDLName"
   	End If
End If






'Diagnostic for sql statement errors
response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	




	
Response.Write "testing<p>"

	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn 


	If Len(objRS("CorpName"))>2 then
		Response.Write objRS("CorpName") & "<br>"
	End If
	
	If Len(objRS("FacName"))>2 then
		Response.Write objRS("FacName") & ", "
	End If

	Response.Write "VISN " & objRS("VISNID") & "<br>"
	
	If Len(objRS("StreetAddr"))>2 then
		Response.Write objRS("StreetAddr") & "<br>"
	End If
	
	If Len(objRS("City"))>2 then
		Response.Write objRS("City") & ", " & objRS("State") & " " & objRS("Zipcode") & "<p></b>"
	End If



%>

            
</body>

</html>

Open in new window


Thank you for the continuing help!
Oops... error is line 127
i've cleaned up the code a little bit and made it work with whatever option is selected:

Dim objRS, ChartID, sql
ChartID = request.form("ChartID")
if not isNumeric( chartID ) then chartID = -1          '-- invalid value, will not include chartID as part of the search criteria
	
sql = "SELECT tblGeneral.ChartID, tblGeneral.StreetAddr, tblGeneral.Zipcode, tblGeneral.City, tblGeneral.State, " & _
		"tblGeneral.FacName, tblGeneral.CorpName, tblGeneral.VISNID, tblGeneral.LibraryStatus, tblGeneral.FacilityStatus, " & _
		"tblGeneral.Location, tblGeneral.Hours, " & _
		"tblStaffDirectory.StaffID, tblStaffDirectory.SDFName, tblStaffDirectory.SDLName, " & _
		"tblStaffDirectory.SDPosition, tblStaffDirectory.SDPhone, tblStaffDirectory.SDExt, " & _
		"tblStaffDirectory.SDEmail, tblStaffDirectory.SDDisplay, " & _
		"tblWeb.OPACLink, tblWeb.SSLink, tblWeb.Intralink " & _

		"FROM (tblGeneral LEFT JOIN tblStaffDirectory ON tblGeneral.ChartID= tblStaffDirectory.ChartID) " & _
		"LEFT JOIN tblWeb ON tblGeneral.ChartID = tblWeb.ChartID "


            
Dim strWhere
strWhere = "AND (FacilityStatus = 'Open') AND (SDDisplay = 'Y') "

if CInt( chartID ) >= 0 then
        strWhere = strWhere & "AND tblGeneral.ChartID =" & Request("ChartID") 
end if

'If one or more conditions were specified, strip off the initial "AND "
If Len(strWhere) > 0 then
   strWhere = "WHERE " & Right(strWhere, Len(strWhere) - 4)
End If



If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY City, State, FacName, SDLName"
	Else If Request.form("sort_by") = "State" then
		sql = sql & strWhere & "ORDER BY State, City, FacName, SDLName"
   	Else 
   		sql = sql & strWhere & "ORDER BY FacName, SDLName"
   	End If
End If

Open in new window

Monty...
Unfortunately that generates the following:


Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression '(FacilityStatus = 'Open') AND (SDDisplay = 'Y') AND tblGeneral.ChartID =197ORDER BY City, State, FacName, SDLName'.

/valnet/_EEQ/resultsMonty.asp, line 115
what is the value being passed over for "all"? is it a blank value?
change your sort code to the following:

If Request.form("sort_by") = "" then
   sql = sql & strWhere & "ORDER BY City, State, FacName, SDLName"
	Else If Request.form("sort_by") = "State" then
		sql = sql & strWhere & " ORDER BY State, City, FacName, SDLName"
   	Else 
   		sql = sql & strWhere & " ORDER BY FacName, SDLName"
   	End If
End If

Open in new window


a space was needed before the ORDER BY clause
ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Once you get all of this working as is.  I would start another question to use parameterized queries to keep you safe. You can do that here if you like but this thread is getting long. It is up to you.
Scott, for now I am just going to get the page to work displaying the other fields for one location.

For future referrals of CHARTID in relate back to the SQL statement, what would the variable for ChartID be?

For example, when I am going to display the tblStaffDirectory fields and need to refer back to the SQL statement, what is the ChartID variable... For example, in the following:

While NOT objRS.EOF
      If StrChartID = objRS("ChartID") THEN
I feel like I am reading a trick question :)  But I am probably not understanding fully what you are asking.

I think what you are asking is if you have a table of data, how do you click on a field or row and display that item?

I'm guessing this kind of thing is what you are referring to.  When you click on the link it will generate the querystring that is used in your page.
<td><a href="mypage.asp?chartid=<%=objRS("ChartID") %>">Link to chart id</a></td>

Open in new window


For best practice, it is a good idea to always use request.form("chartid") or request.querystring("chartid") and not simply request("chartid").  You want to control your input as much as possible.
Sorry. Part of the display includes a table with ChartIDs used multiple time with the records. I need to go thru the table (While NOT objRS.EOF) until I find all the records that match the Chart ID.

I've tried several methods and none work. Should I start a new question?

While NOT objRS.EOF

'      If ChartID = objRS("ChartID") THEN
'      If ChartID = Request("ChartID") THEN
'      If ChartID = Request.Form("ChartID") THEN
'      If Request.form("ChartID") = objRS("ChartID") THEN
'      If objRS("ChartID") = Request.form("ChartID") THEN
      If objRS("ChartID") = StrChartID THEN
Try converting everything to a string and for good measure trimming.

cStr(objRS("ChartID")) = cStr(StrChartID) THEN

or to be more anal

trim(cStr(objRS("ChartID"))) = trim(cStr(StrChartID)) THEN

If you are having issue matching like this, sometimes will test by adding a pipe to see if there are any funky characters or spaces

response.write "|"&trim(cStr(objRS("ChartID")))&"| = |"&  trim(cStr(StrChartID))&"|"

It can help visualize what you are dealing with. But if you are converting to a string and trimming, it should work.  If not something else is at play.
I think opening a new question would be best, the original question, and subsequent others, have been answered, and this is getting hard to keep track of.

As for your latest question, aren't you filtering out by ChartID in the sql? Why do you then need to check for a ChartID?
Thank you!
Just a thought, Scott.  Regarding this comment (accepted as a solution), it would be simplest to completely ignore the comparison against the city field.

So his WHERE clause wouldn't refer to the city at all.