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
ASPHTMLSQL

Avatar of undefined
Last Comment
Malloy1446

8/22/2022 - Mon
Scott Fell

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 Monty

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;
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".
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Big Monty

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
Malloy1446

ASKER
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 Monty

can you post the changes you made to the link in NewPage?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Malloy1446

ASKER
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 Monty

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>"
Malloy1446

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Big Monty

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?
Malloy1446

ASKER
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 Monty

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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

Malloy1446

ASKER
I get

city=Milwaukee
Big Monty

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")
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Malloy1446

ASKER
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 Monty

what is the source code for default.asp?
Malloy1446

ASKER
Source code attached
default-Source.txt
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Big Monty

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 Monty

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

Malloy1446

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Big Monty

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

ASKER
Same thing... Website cannot display the page."
Big Monty

Did you put a Response.End after the response.write line?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Malloy1446

ASKER
No.
Added a Response.End

Still getting the Website cannot display the page.
Big Monty

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

Malloy1446

ASKER
Still getting the "Website cannot display the page."

New results.txt attached.
results.txt
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Big Monty

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?
Malloy1446

ASKER
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 Fell

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Big Monty

can you restore the code to a point where it was working and then paste that code?
Scott Fell

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 Monty

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott Fell

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.
Malloy1446

ASKER
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
Malloy1446

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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

Is this working now?
Malloy1446

ASKER
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.
Malloy1446

ASKER
SQL statement attached
SQL.txt
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Fell

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
Malloy1446

ASKER
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 Fell

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Malloy1446

ASKER
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 Fell

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.
Malloy1446

ASKER
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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Big Monty

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 Fell

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 Fell

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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 Fell

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 Monty

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Malloy1446

ASKER
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 Monty

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 Monty

And don't forget to post the latest version of your code
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Malloy1446

ASKER
<%

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 Monty

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

ASKER
Still no change "Website cannot display the page."
Your help has saved me hundreds of hours of internet surfing.
fblack61
Big Monty

Do you have errors being sent out to the browser?
Malloy1446

ASKER
No errors listed. Just the "Website cannot display the page"
Scott Fell

Are you able to log into your server and surf on the server? or is this shared hosting?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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 Fell

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.
Malloy1446

ASKER
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!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Malloy1446

ASKER
Oops... error is line 127
Big Monty

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

Malloy1446

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Big Monty

what is the value being passed over for "all"? is it a blank value?
Big Monty

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
Scott Fell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Fell

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Malloy1446

ASKER
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 Fell

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.
Malloy1446

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

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 Monty

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?
Malloy1446

ASKER
Thank you!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy