Solved

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

Posted on 2014-11-18
76
58 Views
Last Modified: 2014-11-30
I have a page (attached:  default.txt) which is a drop down list to select a location.
Based on selection another page is generated with the results (attached: results.txt) based on request.form("city")

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

Is this possible?
default.txt
Results.txt
NewPage.txt
0
Comment
Question by:Malloy1446
  • 30
  • 26
  • 20
76 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40450916
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40450917
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
 

Author Comment

by:Malloy1446
ID: 40450930
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40450947
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
 

Author Comment

by:Malloy1446
ID: 40450969
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40450974
can you post the changes you made to the link in NewPage?
0
 

Author Comment

by:Malloy1446
ID: 40450984
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40451015
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
 

Author Comment

by:Malloy1446
ID: 40451037
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40451048
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
 

Author Comment

by:Malloy1446
ID: 40451070
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40451435
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40451543
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
 

Author Comment

by:Malloy1446
ID: 40453926
I get

city=Milwaukee
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40453968
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
 

Author Comment

by:Malloy1446
ID: 40456220
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40456560
what is the source code for default.asp?
0
 

Author Comment

by:Malloy1446
ID: 40456563
Source code attached
default-Source.txt
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40456565
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40456567
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
 

Author Comment

by:Malloy1446
ID: 40456573
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40456580
If you do a response.write of the sql before you execute the sql what do you get?
0
 

Author Comment

by:Malloy1446
ID: 40456585
Same thing... Website cannot display the page."
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40456589
Did you put a Response.End after the response.write line?
0
 

Author Comment

by:Malloy1446
ID: 40456592
No.
Added a Response.End

Still getting the Website cannot display the page.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40456605
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
 

Author Comment

by:Malloy1446
ID: 40456611
Still getting the "Website cannot display the page."

New results.txt attached.
results.txt
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40456615
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
 

Author Comment

by:Malloy1446
ID: 40456632
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40456679
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40457537
can you restore the code to a point where it was working and then paste that code?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40457760
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40457809
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40458063
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
 

Author Comment

by:Malloy1446
ID: 40458847
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
 

Author Comment

by:Malloy1446
ID: 40458880
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40458901
Good choice.  The value should always be the unique id you use to relate one table to the other.  

Is this working now?
0
 

Author Comment

by:Malloy1446
ID: 40458920
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Malloy1446
ID: 40458921
SQL statement attached
SQL.txt
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40458959
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
 

Author Comment

by:Malloy1446
ID: 40460174
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40460573
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
 

Author Comment

by:Malloy1446
ID: 40460851
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40460903
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
 

Author Comment

by:Malloy1446
ID: 40461021
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40461040
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40461107
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40461114
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40461115
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40461135
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40461195
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
 

Author Comment

by:Malloy1446
ID: 40461236
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40461264
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40461267
And don't forget to post the latest version of your code
0
 

Author Comment

by:Malloy1446
ID: 40461283
<%

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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40461287
Try removing the blank row in your sql statement (line 13)
0
 

Author Comment

by:Malloy1446
ID: 40461323
Still no change "Website cannot display the page."
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40461328
Do you have errors being sent out to the browser?
0
 

Author Comment

by:Malloy1446
ID: 40461365
No errors listed. Just the "Website cannot display the page"
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40461428
Are you able to log into your server and surf on the server? or is this shared hosting?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40461434
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40461447
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
 

Author Comment

by:Malloy1446
ID: 40462630
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
 

Author Comment

by:Malloy1446
ID: 40462649
Oops... error is line 127
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40462678
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
 

Author Comment

by:Malloy1446
ID: 40462702
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40462709
what is the value being passed over for "all"? is it a blank value?
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 40462728
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
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 40462733
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40462741
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
 

Author Comment

by:Malloy1446
ID: 40463229
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40463285
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
 

Author Comment

by:Malloy1446
ID: 40463314
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40463333
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 40463351
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
 

Author Closing Comment

by:Malloy1446
ID: 40472653
Thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
This article discusses how to create an extensible mechanism for linked drop downs.
In this tutorial viewers will learn how add a full-size background image to a webpage using CSS3. Create a new HTML document with an internal stylesheet.: In CSS, define the html element to have a background image. Use a high resolution image.: In t…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now