cwiz Posted September 21, 2006 Share Posted September 21, 2006 Hi there,I have the following piece of ASP code (bottom codebox) on my intranet that does basically what I want it to: opens a DSN, executes an SQL statement against an Access 2000 database and returns results in a basic HTML table. I've even got it forming proper paths to local files I want to download from the results page. What I'd like to know is:1) how to get the paths to appear as hyperlinks2) how to get it to take input/retrieve results based on the following simple form. Currently I have entered "123456" where I would want the SQL statement to take input from the ID Number field of the form: <HTML><HEAD><META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252"><TITLE>~Query1</TITLE></HEAD><BODY><%Session.timeout = 5If IsObject(Session("CTS_conn")) Then Set conn = Session("CTS_conn")Else Set conn = Server.CreateObject("ADODB.Connection") conn.open "CTS","login","password" Set Session("CTS_conn") = connEnd If%><%If IsObject(Session("_Query1_rs")) Then Set rs = Session("_Query1_rs")Else sql = "SELECT [tblCase].[CaseID], [tblCase].[CaseReceivedDate] AS Received, [tblCase].[CaseClosedDate] AS Closed, [tblPerson].[PersonIDNo], [tblPerson]![PersonNameLast] & ', ' & [tblPerson]![PersonNameFirst] & ' ' & [tblPerson]![PersonNameMI] AS PersonName, [qryPersonCase].[PersonUnit], [tblStaff].[staffNameLast], [tblPerson].[PersonRetireDate], [tblPerson].[RetireID], 'file://///p:/' & [tblCase]![CaseYear] & '%20CASES/' & [tblCase]![CaseID] & '.pdf' AS [Link to File] FROM ((tblCase INNER JOIN (qryPersonCase INNER JOIN trelPersonAllegation ON [qryPersonCase].[PersonCaseID]=[trelPersonAllegation].[PersonCaseID]) ON [tblCase].[CaseID]=[qryPersonCase].[CaseID]) LEFT JOIN tblStaff ON [tblCase].[CaseAssignTo]=[tblStaff].[staffID]) INNER JOIN tblPerson ON [qryPersonCase].[PersonID]=[tblPerson].[PersonID] GROUP BY [tblCase].[CaseID], [tblCase].[CaseReceivedDate], [tblCase].[CaseClosedDate], [tblPerson].[PersonIDNo], [tblPerson]![PersonNameLast] & ', ' & [tblPerson]![PersonNameFirst] & ' ' & [tblPerson]![PersonNameMI], [qryPersonCase].[PersonUnit], [tblStaff].[staffNameLast], [tblPerson].[PersonRetireDate], [tblPerson].[RetireID], 'file://///p:/' & [tblCase]![CaseYear] & '%20CASES/' & [tblCase]![CaseID] & '.pdf' HAVING (((tblPerson.PersonIDNo)='123456')) ORDER BY [tblCase].[CaseReceivedDate] " Set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql, conn, 3, 3 If rs.eof Then rs.AddNew End If Set Session("_Query1_rs") = rsEnd If%><TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>~Query1</B></CAPTION></FONT><THEAD><TR><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>CaseID</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Received</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Closed</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>ID</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>PersonName</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Assignment</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Last Name</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Retire Date</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Retirement Code</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Link to File</FONT></TH></TR></THEAD><TBODY><%On Error Resume Nextrs.MoveFirstdo while Not rs.eof %><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("CaseID").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Received").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Closed").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonIDNo").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonName").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonUnit").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("StaffNameLast").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonRetireDate").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("RetireID").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Link to File").Value)%><BR></FONT></TD></TR><%rs.MoveNextloop%></TBODY><TFOOT></TFOOT></TABLE></BODY></HTML> Thanks for your time in reading this and for any help you can give...C. Link to comment Share on other sites More sharing options...
justsomeguy Posted September 21, 2006 Share Posted September 21, 2006 Assuming the paths are URLs (it looks like they are file:// URLs), it would be like this:<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><a href="<%=rs.Fields("Link to File").Value%>"><%=Server.HTMLEncode(rs.Fields("Link to File").Value)%></a><BR></FONT></TD>For the form, it's best not to use special characters or spaces in your field names, so change it to this:<INPUT TYPE="Text" NAME="id_number">Then on the ASP page you can get it like this:id_number = CInt(Request.form("id_number"))And use it in a WHERE clause in the SQL statement:SELECT <field list> FROM <table list> ... WHERE id=<your id number> Link to comment Share on other sites More sharing options...
redsun Posted September 21, 2006 Share Posted September 21, 2006 Hi, if I understand right to your questions, here are my suggestions:1) Just edit your SQL a little bit - you need to add a 'Request.Form("form_field_name")' into your query: HAVING (((tblPerson.PersonIDNo)='123456')) ORDER BYchange toHAVING (((tblPerson.PersonIDNo)='" & Request.Form("[ID Number]") & "')) ORDER BY 2) Edit your table line just by adding the hyperlink as usual: your code:<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Link to File").Value)%><BR></FONT></TD>new code:<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><A HREF="<%=Server.HTMLEncode(rs.Fields("Link to File").Value)%>"><%=Server.HTMLEncode(rs.Fields("Link to File").Value)%></A><BR></FONT></TD> I hope that this will help!Redsun Link to comment Share on other sites More sharing options...
cwiz Posted September 22, 2006 Author Share Posted September 22, 2006 Thank you both for your lightning responses. I have the hyperlinks working as you both suggested and have attempted redsun's suggestion with the form a number of ways - with both HAVING and WHERE and also both Request.Form and Request.QueryString. All of those iterations end up timing out with this error type:Error Type:Active Server Pages, ASP 0113 (0x80004005)The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.justsomeguy - I'm curious how my syntax would look with the method you suggested. I actually got this code by using ASP export out of Access and cleaned it up a little. I'm not as bright as it may seem... Again, thanks a million...C. Link to comment Share on other sites More sharing options...
redsun Posted September 22, 2006 Share Posted September 22, 2006 Just to ensure that your ASP code retrieves the right data, remove or disable the SQL query and put in the page <%= Request("[iD Number]") %>.If your FORM value is correctly displayed, go back to SQL query.Here you have 2 option what to do:a) Try to change the Request in MULT_FILE_FINDER.ASP to Request("[iD Number]"). You must not specify from what collection you want to do your request. GET FORM METHOD passes the data in URL, POST doesn't. Change your FORM METHOD from GET to POST. Then on MULT_FILE_FINDER.ASP you should have Request.Form("[iD Number]").Let me know if the timeout still occurs.Redsun Link to comment Share on other sites More sharing options...
cwiz Posted September 22, 2006 Author Share Posted September 22, 2006 Hi Redsun,Both your suggestions a and b did work right out of the box, however ONLY on the first run. If I went back to the form and changed the ID Number the results page would still display the table/records associated with the first ID I had entered - even if I cleared the browser cache.If I restarted IIS at the server it would then work with my second entry, but then do the same thing again with all subsequent entriesWhen I replaced the SQL query with <%= Request("[iD Number]") %>, I would get something like:#Error Type:Microsoft VBScript compilation (0x800A0400)Expected statement/getpdfs/MULT_FILE_FINDER.ASP, line 21# Browser Type:Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.4) Gecko/20060508 Firefox/1.5.0.4# Page:POST 16 bytes to /getpdfs/MULT_FILE_FINDER.ASP# POST Data:ID+Number=123or%5BID Number%5D=123etc.I did play around with removing brackets and using underscores like justsomeguy suggested and then retryed your suggestions again - usually getting a timeout. Thanks again for your patience and persistence...C. Link to comment Share on other sites More sharing options...
redsun Posted September 22, 2006 Share Posted September 22, 2006 Please post your code in the current state. Link to comment Share on other sites More sharing options...
cwiz Posted September 22, 2006 Author Share Posted September 22, 2006 Please post your code in the current state.Hi redsun,Here is the form:<HTML><HEAD><META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252"><TITLE>ONLINE MULT CARD - GET CASE PDFS</TITLE><BODY><FORM METHOD="GET" ACTION="MULT_FILE_FINDER.ASP">[ID Number] <INPUT TYPE="Text" NAME="[ID Number]"><P><INPUT TYPE="Submit" VALUE="Find Cases"></FORM></BODY></HTML> Here is the ASP page: <HTML><HEAD><META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252"><TITLE>~Query1</TITLE></HEAD><BODY><%Session.timeout = 5If IsObject(Session("OCCCTS_conn")) Then Set conn = Session("OCCCTS_conn")Else Set conn = Server.CreateObject("ADODB.Connection") conn.open "CTS","login","password" Set Session("CTS_conn") = connEnd If%><%If IsObject(Session("_Query1_rs")) Then Set rs = Session("_Query1_rs")Else sql = "SELECT [tblCase].[CaseID], [tblCase].[CaseReceivedDate] AS Received, [tblCase].[CaseClosedDate] AS Closed, [tblPerson].[PersonIDNo], [tblPerson]![PersonNameLast] & ', ' & [tblPerson]![PersonNameFirst] & ' ' & [tblPerson]![PersonNameMI] AS PersonName, [qryPersonCase].[PersonUnit], [tblStaff].[StaffNameLast], [tblPerson].[PersonRetireDate], [tblPerson].[RetireID], 'file:///p:/' & [tblCase]![CaseYear] & '%20CASES/' & [tblCase]![CaseID] & '.pdf' AS [Link to File] FROM ((tblCase INNER JOIN (qryPersonCase INNER JOIN trelPersonAllegation ON [qryPersonCase].[PersonCaseID]=[trelPersonAllegation].[PersonCaseID]) ON [tblCase].[CaseID]=[qryPersonCase].[CaseID]) LEFT JOIN tblStaff ON [tblCase].[CaseAssignTo]=[tblStaff].[StaffID]) INNER JOIN tblPerson ON [qryPersonCase].[PersonID]=[tblPerson].[PersonID] GROUP BY [tblCase].[CaseID], [tblCase].[CaseReceivedDate], [tblCase].[CaseClosedDate], [tblPerson].[PersonIDNo], [tblPerson]![PersonNameLast] & ', ' & [tblPerson]![PersonNameFirst] & ' ' & [tblPerson]![PersonNameMI], [qryPersonCase].[PersonUnit], [tblStaff].[StaffNameLast], [tblPerson].[PersonRetireDate], [tblPerson].[RetireID], 'file:///p:/' & [tblCase]![CaseYear] & '%20CASES/' & [tblCase]![CaseID] & '.pdf' HAVING ((([tblPerson].[PersonIDNo])='" & Request("[ID Number]") & "')) ORDER BY [tblCase].[CaseReceivedDate] " Set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql, conn, 3, 3 If rs.eof Then rs.AddNew End If Set Session("_Query1_rs") = rsEnd If%><TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>~Query1</B></CAPTION></FONT><THEAD><TR><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>CaseID</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Received</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Closed</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>ID</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>PersonName</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Assignment</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Last Name</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Retire Date</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Retirement Code</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial" COLOR=#000000>Link to File</FONT></TH></TR></THEAD><TBODY><%On Error Resume Nextrs.MoveFirstdo while Not rs.eof %><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("CaseID").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Received").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Closed").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonIDNo").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonName").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonUnit").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("StaffNameLast").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PersonRetireDate").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("RetireID").Value)%><BR></FONT></TD><TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><A HREF="<%=Server.HTMLEncode(rs.Fields("Link to File").Value)%>"><%=Server.HTMLEncode(rs.Fields("Link to File").Value)%></A><BR></FONT></TD></TR><%rs.MoveNextloop%></TBODY><TFOOT></TFOOT></TABLE></BODY></HTML> Again, this works only for the first entry after an IIS restart, then displays the same results page if you change the ID Number in the form... Thanks again...C. Link to comment Share on other sites More sharing options...
redsun Posted September 22, 2006 Share Posted September 22, 2006 This is not the cause of your problem, but it is better to let the IIS know, what language do you use in your page. So add this on the first line in (every) ASP page: <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%> 1252 for western langs, or use one of your preferenceNow I looked at the code before the SQL statement - you have If Then clause combined with declaration of session. The session contains your SQL query, but contains also the requested FORM VALUE - it is not only SQL!According to your If Then Else statement - if you run your query for the first time, you save it in the session which "lives" for next 5 minutes in IIS. The statement says - if my query exists, use it. If you session times out or you restart IIS, it will work again just for one time.Just test it in another page by running this: <%=Session("_Query1_rs")%> You will see your SQL query combined with the FORM VALUE. Saying the truth, I don't know why do you have that If Then statement in place, because it blocks the query from running everytime again. If you just copyed the statement from the abowe query which is correct - login procedure is still the same for one user.After removing the If Then Else statement your "sql" code block should look like: <%sql = "SELECT [tblCase].[CaseID], [tblCase].[CaseReceivedDate] AS Received, [tblCase].[CaseClosedDate] AS Closed, [tblPerson].[PersonIDNo], [tblPerson]![PersonNameLast] & ', ' & [tblPerson]![PersonNameFirst] & ' ' & [tblPerson]![PersonNameMI] AS PersonName, [qryPersonCase].[PersonUnit], [tblStaff].[StaffNameLast], [tblPerson].[PersonRetireDate], [tblPerson].[RetireID], 'file:///p:/' & [tblCase]![CaseYear] & '%20CASES/' & [tblCase]![CaseID] & '.pdf' AS [Link to File] FROM ((tblCase INNER JOIN (qryPersonCase INNER JOIN trelPersonAllegation ON [qryPersonCase].[PersonCaseID]=[trelPersonAllegation].[PersonCaseID]) ON [tblCase].[CaseID]=[qryPersonCase].[CaseID]) LEFT JOIN tblStaff ON [tblCase].[CaseAssignTo]=[tblStaff].[StaffID]) INNER JOIN tblPerson ON [qryPersonCase].[PersonID]=[tblPerson].[PersonID] GROUP BY [tblCase].[CaseID], [tblCase].[CaseReceivedDate], [tblCase].[CaseClosedDate], [tblPerson].[PersonIDNo], [tblPerson]![PersonNameLast] & ', ' & [tblPerson]![PersonNameFirst] & ' ' & [tblPerson]![PersonNameMI], [qryPersonCase].[PersonUnit], [tblStaff].[StaffNameLast], [tblPerson].[PersonRetireDate], [tblPerson].[RetireID], 'file:///p:/' & [tblCase]![CaseYear] & '%20CASES/' & [tblCase]![CaseID] & '.pdf' HAVING ((([tblPerson].[PersonIDNo])='" & Request("[ID Number]") & "')) ORDER BY [tblCase].[CaseReceivedDate] " Set rs = Server.CreateObject("ADODB.Recordset") rs.Open sql, conn, 3, 3 If rs.eof Then rs.AddNew End If%> I hope this will help you already ;o) Link to comment Share on other sites More sharing options...
cwiz Posted September 23, 2006 Author Share Posted September 23, 2006 Thanks redsun! That did it. You fricken' rock...C. Link to comment Share on other sites More sharing options...
redsun Posted September 23, 2006 Share Posted September 23, 2006 Well, I'm glad you have a nice piece of working code now ;o) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now