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
Malloy1446Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
Big MontyWeb Ninja at largeCommented:
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;
0
Malloy1446Author 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".
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Big MontyWeb Ninja at largeCommented:
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
0
Malloy1446Author 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.
0
Big MontyWeb Ninja at largeCommented:
can you post the changes you made to the link in NewPage?
0
Malloy1446Author 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">
0
Big MontyWeb Ninja at largeCommented:
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>"
0
Malloy1446Author 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
0
Big MontyWeb Ninja at largeCommented:
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?
0
Malloy1446Author 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'
0
Big MontyWeb Ninja at largeCommented:
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
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
Malloy1446Author Commented:
I get

city=Milwaukee
0
Big MontyWeb Ninja at largeCommented:
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")
0
Malloy1446Author 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
0
Big MontyWeb Ninja at largeCommented:
what is the source code for default.asp?
0
Malloy1446Author Commented:
Source code attached
default-Source.txt
0
Big MontyWeb Ninja at largeCommented:
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
0
Big MontyWeb Ninja at largeCommented:
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

0
Malloy1446Author 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.
0
Big MontyWeb Ninja at largeCommented:
If you do a response.write of the sql before you execute the sql what do you get?
0
Malloy1446Author Commented:
Same thing... Website cannot display the page."
0
Big MontyWeb Ninja at largeCommented:
Did you put a Response.End after the response.write line?
0
Malloy1446Author Commented:
No.
Added a Response.End

Still getting the Website cannot display the page.
0
Big MontyWeb Ninja at largeCommented:
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

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

New results.txt attached.
results.txt
0
Big MontyWeb Ninja at largeCommented:
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?
0
Malloy1446Author 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.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Big MontyWeb Ninja at largeCommented:
can you restore the code to a point where it was working and then paste that code?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
0
Big MontyWeb Ninja at largeCommented:
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
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Malloy1446Author 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
0
Malloy1446Author 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.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Good choice.  The value should always be the unique id you use to relate one table to the other.  

Is this working now?
0
Malloy1446Author 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.
0
Malloy1446Author Commented:
SQL statement attached
SQL.txt
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
0
Malloy1446Author 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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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?
0
Malloy1446Author 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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Malloy1446Author 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

0
Big MontyWeb Ninja at largeCommented:
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' "
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Big MontyWeb Ninja at largeCommented:
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.
0
Malloy1446Author 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.
0
Big MontyWeb Ninja at largeCommented:
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
0
Big MontyWeb Ninja at largeCommented:
And don't forget to post the latest version of your code
0
Malloy1446Author 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

0
Big MontyWeb Ninja at largeCommented:
Try removing the blank row in your sql statement (line 13)
0
Malloy1446Author Commented:
Still no change "Website cannot display the page."
0
Big MontyWeb Ninja at largeCommented:
Do you have errors being sent out to the browser?
0
Malloy1446Author Commented:
No errors listed. Just the "Website cannot display the page"
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Are you able to log into your server and surf on the server? or is this shared hosting?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Malloy1446Author 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!
0
Malloy1446Author Commented:
Oops... error is line 127
0
Big MontyWeb Ninja at largeCommented:
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

0
Malloy1446Author 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
0
Big MontyWeb Ninja at largeCommented:
what is the value being passed over for "all"? is it a blank value?
0
Big MontyWeb Ninja at largeCommented:
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
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Are you talking about "ALL" as in city?
Response.Write ("<option value=''>-- All --</option>")

Open in new window


If that is the case, change the value to a wildcard.  In access that could be a "*" and in sql server it is a "%".  Then the where clause gets changed from = to like

Instead of WHERE City = 'Miami' you can use WHERE City like 'Miami'  With the wild card, WHERE City like '*'   If you just change the WHERE clause for the city to use "LIKE" instead of "=" then on the drop down change to
Response.Write ("<option value='*'>-- All --</option>")

Open in new window

you should be good.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Malloy1446Author 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
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Malloy1446Author 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
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
Big MontyWeb Ninja at largeCommented:
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?
0
Malloy1446Author Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.