We help IT Professionals succeed at work.

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

Malloy1446
Malloy1446 asked
on
216 Views
Last Modified: 2014-11-30
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
Comment
Watch Question

Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

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

Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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;

Author

Commented:
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".
Big MontyWeb Ninja at large
CERTIFIED EXPERT

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

Author

Commented:
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.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
can you post the changes you made to the link in NewPage?

Author

Commented:
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">
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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>"

Author

Commented:
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
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
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'
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

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

Author

Commented:
I get

city=Milwaukee
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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")

Author

Commented:
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
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
what is the source code for default.asp?

Author

Commented:
Source code attached
default-Source.txt
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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
Big MontyWeb Ninja at large
CERTIFIED EXPERT

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

Author

Commented:
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.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
If you do a response.write of the sql before you execute the sql what do you get?

Author

Commented:
Same thing... Website cannot display the page."
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
Did you put a Response.End after the response.write line?

Author

Commented:
No.
Added a Response.End

Still getting the Website cannot display the page.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

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

Author

Commented:
Still getting the "Website cannot display the page."

New results.txt attached.
results.txt
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
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.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
can you restore the code to a point where it was working and then paste that code?
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.

Author

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

Author

Commented:
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.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
Good choice.  The value should always be the unique id you use to relate one table to the other.  

Is this working now?

Author

Commented:
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.

Author

Commented:
SQL statement attached
SQL.txt
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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 & "' "   

 

Code Block

Author

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

Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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?

Author

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

Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.

Author

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

Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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' "
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

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

Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
And don't forget to post the latest version of your code

Author

Commented:
<%

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

Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
Try removing the blank row in your sql statement (line 13)

Author

Commented:
Still no change "Website cannot display the page."
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
Do you have errors being sent out to the browser?

Author

Commented:
No errors listed. Just the "Website cannot display the page"
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
Are you able to log into your server and surf on the server? or is this shared hosting?
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.

Author

Commented:
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!

Author

Commented:
Oops... error is line 127
Big MontyWeb Ninja at large
CERTIFIED EXPERT

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

Author

Commented:
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
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
what is the value being passed over for "all"? is it a blank value?
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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
Developer
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.

Author

Commented:
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
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.

Author

Commented:
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
Scott FellDeveloper
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2013

Commented:
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.
Big MontyWeb Ninja at large
CERTIFIED EXPERT

Commented:
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?

Author

Commented:
Thank you!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.