cwiz's Content - W3Schools Forum

ASP against MS Access DB - close but no cigar...

cwiz replied to cwiz's topic in ASP

Thanks redsun! That did it. You fricken' rock...C.

ASP against MS Access DB - close but no cigar...

cwiz replied to cwiz's topic in ASP

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]"><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><CAPTION>~Query1</CAPTION><THEAD><TR><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >CaseID</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Received</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Closed</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >ID</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >PersonName</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Assignment</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Last Name</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Retire Date</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Retirement Code</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Link to File</TH></TR></THEAD><TBODY><%On Error Resume Nextrs.MoveFirstdo while Not rs.eof %><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("CaseID").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><%=Server.HTMLEncode(rs.Fields("Received").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><%=Server.HTMLEncode(rs.Fields("Closed").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("PersonIDNo").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("PersonName").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("PersonUnit").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("StaffNameLast").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><%=Server.HTMLEncode(rs.Fields("PersonRetireDate").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("RetireID").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><A HREF="<%=Server.HTMLEncode(rs.Fields("Link to File").Value)%>"><%=Server.HTMLEncode(rs.Fields("Link to File").Value)%></A> </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.

ASP against MS Access DB - close but no cigar...

cwiz replied to cwiz's topic in ASP

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.

ASP against MS Access DB - close but no cigar...

cwiz replied to cwiz's topic in ASP

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.

ASP against MS Access DB - close but no cigar...

cwiz posted a topic in ASP

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><CAPTION>~Query1</CAPTION><THEAD><TR><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >CaseID</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Received</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Closed</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >ID</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >PersonName</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Assignment</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Last Name</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Retire Date</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Retirement Code</TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >Link to File</TH></TR></THEAD><TBODY><%On Error Resume Nextrs.MoveFirstdo while Not rs.eof %><TR VALIGN=TOP><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("CaseID").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><%=Server.HTMLEncode(rs.Fields("Received").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><%=Server.HTMLEncode(rs.Fields("Closed").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("PersonIDNo").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("PersonName").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("PersonUnit").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("StaffNameLast").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><%=Server.HTMLEncode(rs.Fields("PersonRetireDate").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("RetireID").Value)%> </TD><TD BORDERCOLOR=#c0c0c0 ><%=Server.HTMLEncode(rs.Fields("Link to File").Value)%> </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.

Sign In

cwiz

Posts

Joined

Last visited

Content Type

Profiles

Forums

Events

Everything posted by cwiz

ASP against MS Access DB - close but no cigar...

ASP against MS Access DB - close but no cigar...

ASP against MS Access DB - close but no cigar...

ASP against MS Access DB - close but no cigar...

ASP against MS Access DB - close but no cigar...

Browse

Activity