Derek Fisher's Portfolio

'	Open connection to database 
		set dbc=Server.CreateObject("ADODB.Connection")
		dbc.Provider="Microsoft.Jet.OLEDB.4.0"
		dbc.Open = "EDO.mdb"
    
'	Create variables from previous forms and format the strings for the SQL query
		Dim sql, srFld, sqlWhere1, sqlWhat1, sqlWhere2, sqlWhat2, sqlWhere3, sqlWhat3, all, rtnCnt, sqlStr
		srFld = Request.Form("searchfield")
		sqlWhere1 = "[" &Request.Form("searchfield")& "]"
		sqlWhere2 = "[" &Request.Form("searchfield2")& "]"
		sqlWhere3 = "[" &Request.Form("searchfield3")& "]"
		sqlWhat1 = Request.Form("searchstring")
		sqlWhat2 = Request.Form("searchstring2")
		sqlWhat3 = Request.Form("searchstring3")
		sqlWhat1 = "'%"&sqlWhat1&"%'"
		sqlWhat2 = "'%"&sqlWhat2&"%'"
		sqlWhat3 = "'%"&sqlWhat3&"%'"
		sql = "SELECT *" 
		rtnCnt = 0
				
		Dim i, flds, data

'	Form SQL string
		sqlStr = " FROM [" & Request.Form("table") & "] WHERE " &sqlWhere1& " LIKE " &sqlWhat1& " AND " &sqlWhere2& " LIKE " &sqlWhat2& " AND " &sqlWhere3& " LIKE " &sqlWhat3& " ORDER BY PartNumber;"
		sql = sql & sqlStr
'		Response.Write sql	
'	Execute the SQL string
		Set rsData = dbc.Execute(sql)

'	Create ADO schema    
		Set rsFieldNames = dbc.OpenSchema(adSchemaColumns)

'	Header for the results page
		Response.Write "<HTML><head>"
		Response.Write "<script language='JavaScript' type='text/javascript'>"

Response.Write "</script></head>"
		Response.Write "<BODY onLoad='menuOnLoad()'><font face='verdana,arial' size=2>"
		Response.Write "<img src = 'assests\bg.jpg'><br /><br />"
		Response.Write "<table bgcolor='#c0c0c0' width='100%'><tr>"
		Response.Write "<td width='20%' align ='center'><a href = 'index.asp'>Home</a></td>"
		Response.Write "<td width='20%' align ='center'><a href = 'mControl.html'>Mission Control</a></td>"
		Response.Write "<td width='20%' align ='center'><a href = 'library.html'>Library</a></td>"
		Response.Write "<td width='20%' align ='center'><a href = 'tools.html'>Tools</a></td>"
		Response.Write "<td width='20%' align ='center'><a href = 'contact.html'>Contact</a></td>"
		Response.Write "</tr></table>"
		Response.Write "<table width='80%' BORDER=1 Float=left><th width='100%' valign='top'>Parts Query Form</th>"
		Response.Write "<tr><td><center><b>Table:   </b> " & Request.Form("table") & "<br />"
		Response.Write "</td></tr><br />"		
'	Un-comment this to see what the exact string is being passed in SQL
'       Response.Write "<b>Query:   </b> " & sql & "<br><br>"

'	Create tables for displaying the results of the query
		idNum = 0
Do While Not(rsData.EOF)

Dim hlink, cCode, id, pref, pType, fldsCnt, loopct, idNum

Response.Write "<TABLE WIDTH=80% BORDER=1 Float=left>"
		Response.Write "<TR><TD width='15%'bgcolor=#00FF00><font face='verdana,arial' size=2><B><center>Field Name</b></TD>"
		Response.Write "<TD width='65%'bgcolor=#00FF00><font face='verdana,arial' size=2><B><center>Value</b></TD></TR>"

fldsCnt = Request.Form("selectfields").Count

For i = 1 to fldsCnt
              rsFieldNames.filter = "TABLE_NAME='" & Request.Form("table") & "'" & " AND COLUMN_NAME='" & Request.Form("selectfields")(i) & "'"

flds = Request.Form("selectfields")(i)					
		data = rsData(flds)
		
			If flds = "Hyperlink" Then
						hlink = "<a href = 'http://"&data&"'>"
					ElseIf flds = "CageCode" Then
						cCode = data
        		    ElseIf flds = "Value" Then
             			Response.Write "<TR>"
						Response.Write "<TD width='15%' bgcolor=#C0C0C0><font face='verdana,arial' size=2>" & flds & " </TD>"
						Response.Write "<TD width='30%'><font face='verdana,arial' size=2>"&data&"</TD>"
        		        Response.Write "</TR>"
        		    ElseIf flds = "PartNumber" Then
             			Response.Write "<TR>"
						Response.Write "<TD width='15%' bgcolor=#C0C0C0><font face='verdana,arial' size=2>" & flds & " </TD>"
						Response.Write "<TD width='30%'><font face='verdana,arial' size=2>"&data&"</TD>"
        		        Response.Write "</TR>"
        		    ElseIf flds = "Description" Then
             			Response.Write "<TR>"
						Response.Write "<TD width='15%' bgcolor=#C0C0C0><font face='verdana,arial' size=2>" & flds & " </TD>"
						Response.Write "<TD width='30%'><font face='verdana,arial' size=2>"&data&"</TD>"
        		        Response.Write "</TR>"
        	        ElseIf flds = "Mfr" Then
        	            Response.Write "<TR>"
        	        	Response.Write "<TD width='15%' bgcolor=#C0C0C0><font face='verdana,arial' size=2>" & flds & " </TD>"
						Response.Write "<TD width='30%'><font face='verdana,arial' size=2>"&hlink&"("&data&", "&cCode&")</a></TD>"
        	        	Response.Write "</TR>"
             		ElseIf flds = "Datasheet" Then
             			Response.Write "<TR>"
						Response.Write "<TD width='15%' bgcolor=#C0C0C0><font face='verdana,arial' size=2>" & flds & " </TD>"
						Response.Write "<TD width='30%'><font face='verdana,arial' size=2><a href ='\Datasheets\"&rsData(flds)&".pdf' target ='_blank'>"&data&"</a> </TD>"
        		        Response.Write "</TR>"   
			End If

Next 
					Response.Write "</TABLE>"
					Response.Write "<a href='javascript:toggleClamShellMenu("&idNum&")'> +More Info</a><br />"
					Response.Write "<div id ='mInfo'>"
					Response.Write "<div id ="&idNum&" style='display:none';>"
For i = 1 to fldsCnt
              	rsFieldNames.filter = "TABLE_NAME='" & Request.Form("table") & "'" & " AND COLUMN_NAME='" & Request.Form("selectfields")(i) & "'"
				flds = Request.Form("selectfields")(i)					
				data = rsData(flds)			
					If data <> "" AND flds <> "Hyperlink" AND flds <> "CageCode" AND flds <> "Value" AND flds <> "PartNumber"_
							AND flds <> "Description" AND flds <> "ID" AND flds <> "Datasheet" AND flds <> "Mfr" Then	
							Response.Write "    -"&flds&": "&data&"<br />"
					End If
Next
					Response.Write "</span></div>"
					Response.Write"<br /><br />"
					idNum = idNum + 1
					rtnCnt = rtnCnt + 1
					rsData.MoveNext
Loop
       	Response.Write "Total number of parts returned: "&rtnCnt
        Response.Write "<br /><a href = 'index.asp'>Home</a>"
        
        
'	Empty memory/Cleanup
		rsData.Close
		rsFieldNames.Close
		dbc.Close
		set dbc = Nothing

End If