Hi,
I have built a page that lopps through a recordset and then through another one inside the first to enable me to get the results i need. Is there anyway of using one SQL query instead of the 2 that i am using, it works, but i am sure someone that knows could give me some good advice to use less code.
Thanks.
I have built a page that lopps through a recordset and then through another one inside the first to enable me to get the results i need. Is there anyway of using one SQL query instead of the 2 that i am using, it works, but i am sure someone that knows could give me some good advice to use less code.
Thanks.
Code:
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<!--#include file="includes/connection.asp"-->
<%
Dim connStr, qry, qry1, oRs, oRs1, systemuser
'CALLS THE UNIQUE ID OFTHE USER LOFGGED IN
systemuser = Request.QueryString("systemuser")
'response.write(systemuser)
If Request.QueryString("ownerresults") <> "" Then
qry = "SELECT * FROM wce_contact INNER JOIN wce_mailer ON wce_contact.idstatus = wce_mailer.refno LEFT JOIN wce_mailer_response ON wce_mailer.uniqueid = wce_mailer_response.uniqueid RIGHT JOIN wce_mailer_link ON wce_mailer.uniqueid = wce_mailer_link.mailerid WHERE wce_mailer.RefNo = '"& Request.QueryString("ownerresults") &"'"
'Response.write(qry)
'response.end
Set oRs = connStr.Execute(qry)
Session("applicantid") = (oRs("contactid"))
Session("mailerid") = (oRs("mailerid"))
Session("idstatus") = (oRs("idstatus"))
Session("title") = (oRs("title"))
qry1 = "SELECT * FROM wce_contact INNER JOIN wce_mailer_link ON wce_contact.uniqueid = wce_mailer_link.contactid WHERE wce_mailer_link.idstatus = '"& Request.QueryString("ownerresults") &"' ORDER BY wce_mailer_link.mailerid desc"
'Response.write(qry1)
'response.end
Set oRs1 = connStr.Execute(qry1)
Else
qry = "SELECT * FROM wce_contact INNER JOIN wce_mailer_link ON wce_contact.UNIQUEID = wce_mailer_link.Contactid LEFT JOIN wce_mailer ON wce_mailer.uniqueid = wce_mailer_link.mailerid WHERE wce_contact.uniqueid = '"& Request.QueryString("id") &"' ORDER BY wce_mailer_link.mailerid desc"
'Response.write(qry)
'response.end
Set oRs = connStr.Execute(qry)
End If
%>
<html>
<head>
<title>Mailer Results</title>
<script type="text/javascript">
<!--
function myPopup() {
window.open( "#", "myWindow",
"status = 1, height = 400, width = 400, resizable = 0" )
}
//-->
</script>
<style type="text/css">
table.mailerresults td
{ font-family: Verdana;
font-weight: normal;
font-size: 11px;
color: #404040;
background-color: #fafafa;
border: 0px #6699CC solid;
border-collapse: collapse;
border-spacing: 2px;
margin-top: 0px;}
A:link {text-decoration: none; color: #ff3300;}
A:visited {text-decoration: none; color: #ff3300;}
A:active {text-decoration: none; color: #ff3300;}
A:hover {text-decoration: none; color: #0099cc;}
</style>
</head>
<body bgcolor="#F7EFCE" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form name="response" method="post" onSubmit="openTarget(this, 'width=400,height=400,resizable=1,scrollbars=1'); return true;" target="newpopup" action="MailerResponse.asp">
<br />
<table border="0" width="95%" align="center" class="mailerresults" cellspacing="0" cellpadding="0">
<tr>
<td colspan="3">
<table border="1" class="mailerresults" cellspacing="1" cellpadding="1" width="100%">
<tr>
<td width="25%"><b>Ref Num</b></td>
<td align="left" colspan="6"><b>Date / Time created</b></td>
</tr>
<tr>
<td colspan="6"><b><hr></b></td>
</tr>
<%
If Request.QueryString("ownerresults") <> "" Then
Do While Not oRs.EOF
Do While Not oRs1.EOF
%>
<tr:hover { background: #fcd; }>
<td><a href="wceweb.dll?function=contact&etable=wce_contact&lup=0&page=frContact.html&eid=<%=(oRs1("contactid"))%>" Target="mainview"><%=(oRs1("contact"))%><a/></td>
<td><%=(oRs1("createtime"))%></td>
<td><%=(oRs1("mailerid"))%></td>
<td><%=(oRs1("title"))%></td>
<td width="15%"><a href="MailerResponse.asp?contactid=<%=(oRs1("contactid"))%>&response=specific&mailerid=<%=(oRs1("mailerid"))%>&mailerresponseid=<%=(oRs1("uniqueid"))%>">View Response</a></td>
<td width="15%"><a href="MailerResponse.asp?refnum=<%=(Request.QueryString("ownerresults"))%>&contactid=<%=(oRs1("contactid"))%>&response=delete&mailerid=<%=(oRs1("mailerid"))%>&mailerresponseid=<%=(oRs1("uniqueid"))%>">Remove contact</a></td>
</tr>
<%
oRs1.MoveNext
Loop
%>
<%
oRs.MoveNext
Loop
%>
</table>
</td>
</tr>
<%
Else
Do While Not oRs.EOF
%>
<tr>
<td><a href="MailerAttachments.asp?fileid=<%=(oRs("fileid"))%>&mailername=<%=(oRs("RefNo"))%>" target="myWindow" onClick="myPopup()"><%=(oRs("RefNo"))%></a></td>
<!-- <td>/td>-->
<td><%=(oRs("createtime"))%></td>
<td><%=(oRs("mailerid"))%></td>
<td><%=(oRs("title"))%></td>
<td width="30%"><a href="MailerResponse.asp?systemuser=<%=(systemuser)%>&contactid=<%=(Request.QueryString("id"))%>&response=add&mailername=<%=(oRs("RefNo"))%>&mailerid=<%=(oRs("uniqueid"))%>" target="_blank">Add Repsonse</a> -
<a href="MailerResponse.asp?contactid=<%=(Request.QueryString("id"))%>&response=specific&mailerid=<%=(oRs("uniqueid"))%>&fileid=<%=(oRs("fileid"))%>">View Response</a></td>
</tr>
<%
oRs.MoveNext
Loop
End If
%>
</table>
</form>
</body>
</html>