Forums
This topic is locked
Repeat Region/Show If Empty
Posted 10 Mar 2002 09:15:20
1
has voted
10 Mar 2002 09:15:20 frog jumps posted:
<font face='Tahoma'>Here goes:
I have a Access db w/ 3 tables
1. Customers - Info pertaining to customer is listed here
2. Notes - Notes pertaining to customers in Customers Table, updated
constantly--sales info, general notes, invoicing info etc....
3. Employees - employee info, name, user id, password etc...
My problem lies within a REPEATED REGION....
When displaying the Customers Record I get ALL the NOTES
on ALL customers, not just notes pertaining to the current record
being displayed. I think my problem might be in the SQL, not sure.
But the Repeat Region is tweaked regardless...
here's the code, can someone HELP:
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/cnDH.asp" -->
<%
set rsCust = Server.CreateObject("ADODB.Recordset"
rsCust.ActiveConnection = MM_cnDH_STRING
rsCust.Source = "SELECT Customers.CustID, Customers.CompanyName,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.Address, Customers.Address2, Customers.Address3,
Customers.City, Customers.State, Customers.ZipCode,
Customers.PhoneNumber, Customers.FaxNumber, Customers.AltPhone,
Notes.TaskID, Notes.nCustID, Notes.Employee, Notes.Time, Notes.Date,
Notes.Notes FROM Customers LEFT OUTER JOIN Notes ON (Notes.nCustID =
Customers.CustID)"
rsCust.CursorType = 3
rsCust.CursorLocation = 2
rsCust.LockType = 3
rsCust.Open()
rsCust_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsCust_numRows = rsCust_numRows + Repeat1__numRows
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare
stats variables
' set the record count
rsCust_total = rsCust.RecordCount
' set the number of rows displayed on this page
If (rsCust_numRows < 0) Then
rsCust_numRows = rsCust_total
Elseif (rsCust_numRows = 0) Then
rsCust_numRows = 1
End If
' set the first and last displayed record
rsCust_first = 1
rsCust_last = rsCust_first + rsCust_numRows - 1
' if we have the correct record count, check the other stats
If (rsCust_total <> -1) Then
If (rsCust_first > rsCust_total) Then rsCust_first = rsCust_total
If (rsCust_last > rsCust_total) Then rsCust_last = rsCust_total
If (rsCust_numRows > rsCust_total) Then rsCust_numRows =
rsCust_total
End If
%>
<%
' *** Move To Record and Go To Record: declare variables
Set MM_rs = rsCust
MM_rsCount = rsCust_total
MM_size = rsCust_numRows
MM_uniqueCol = ""
MM_paramName = ""
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "" Then
MM_paramIsDefined = (Request.QueryString(MM_paramName) <> ""
End If
%>
<%
' *** Move To Record: handle 'index' or 'offset' parameter
if (Not MM_paramIsDefined And MM_rsCount <> 0) then
' use index parameter if defined, otherwise use offset parameter
r = Request.QueryString("index"
If r = "" Then r = Request.QueryString("offset"
If r <> "" Then MM_offset = Int(r)
' if we have a record count, check if we are past the end of the
recordset
If (MM_rsCount <> -1) Then
If (MM_offset >= MM_rsCount Or MM_offset = -1) Then ' past end or
move last
If ((MM_rsCount Mod MM_size) > 0) Then ' last page not a
full repeat region
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If
' move the cursor to the selected record
i = 0
While ((Not MM_rs.EOF) And (i < MM_offset Or MM_offset = -1))
MM_rs.MoveNext
i = i + 1
Wend
If (MM_rs.EOF) Then MM_offset = i ' set MM_offset to the last
possible record
End If
%>
<%
' *** Move To Record: if we dont know the record count, check the
display range
If (MM_rsCount = -1) Then
' walk to the end of the display range for this page
i = MM_offset
While (Not MM_rs.EOF And (MM_size < 0 Or i < MM_offset + MM_size))
MM_rs.MoveNext
i = i + 1
Wend
' if we walked off the end of the recordset, set MM_rsCount and
MM_size
If (MM_rs.EOF) Then
MM_rsCount = i
If (MM_size < 0 Or MM_size > MM_rsCount) Then MM_size = MM_rsCount
End If
' if we walked off the end, set the offset based on page size
If (MM_rs.EOF And Not MM_paramIsDefined) Then
If (MM_offset > MM_rsCount - MM_size Or MM_offset = -1) Then
If ((MM_rsCount Mod MM_size) > 0) Then
MM_offset = MM_rsCount - (MM_rsCount Mod MM_size)
Else
MM_offset = MM_rsCount - MM_size
End If
End If
End If
' reset the cursor to the beginning
If (MM_rs.CursorType > 0) Then
MM_rs.MoveFirst
Else
MM_rs.Requery
End If
' move the cursor to the selected record
i = 0
While (Not MM_rs.EOF And i < MM_offset)
MM_rs.MoveNext
i = i + 1
Wend
End If
%>
<%
' *** Move To Record: update recordset stats
' set the first and last displayed record
rsCust_first = MM_offset + 1
rsCust_last = MM_offset + MM_size
If (MM_rsCount <> -1) Then
If (rsCust_first > MM_rsCount) Then rsCust_first = MM_rsCount
If (rsCust_last > MM_rsCount) Then rsCust_last = MM_rsCount
End If
' set the boolean used by hide region to check if we are on the last
record
MM_atTotal = (MM_rsCount <> -1 And MM_offset + MM_size >= MM_rsCount)
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining
URL and Form parameters
' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "" Then MM_removeList = MM_removeList & "&" &
MM_paramName & "="
MM_keepURL="":MM_keepForm="":MM_keepBoth="":MM_keepNone=""
' add the URL parameters to the MM_keepURL string
For Each Item In Request.QueryString
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepURL = MM_keepURL & NextItem &
Server.URLencode(Request.QueryString(Item))
End If
Next
' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepForm = MM_keepForm & NextItem &
Server.URLencode(Request.Form(Item))
End If
Next
' create the Form + URL string and remove the intial '&' from each of
the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> "" Then MM_keepBoth = Right(MM_keepBoth,
Len(MM_keepBoth) - 1)
if (MM_keepURL <> "" Then MM_keepURL = Right(MM_keepURL,
Len(MM_keepURL) - 1)
if (MM_keepForm <> "" Then MM_keepForm = Right(MM_keepForm,
Len(MM_keepForm) - 1)
' a utility function used for adding additional parameters to these
strings
Function MM_joinChar(firstItem)
If (firstItem <> "" Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<%
' *** Move To Record: set the strings for the first, last, next, and
previous links
MM_keepMove = MM_keepBoth
MM_moveParam = "index"
' if the page has a repeated region, remove 'offset' from the
maintained parameters
If (MM_size > 0) Then
MM_moveParam = "offset"
If (MM_keepMove <> "" Then
params = Split(MM_keepMove, "&"
MM_keepMove = ""
For i = 0 To UBound(params)
nextItem = Left(params(i), InStr(params(i),"=" - 1)
If (StrComp(nextItem,MM_moveParam,1) <> 0) Then
MM_keepMove = MM_keepMove & "&" & params(i)
End If
Next
If (MM_keepMove <> "" Then
MM_keepMove = Right(MM_keepMove, Len(MM_keepMove) - 1)
End If
End If
End If
' set the strings for the move to links
If (MM_keepMove <> "" Then MM_keepMove = MM_keepMove & "&"
urlStr = Request.ServerVariables("URL" & "?" & MM_keepMove &
MM_moveParam & "="
MM_moveFirst = urlStr & "0"
MM_moveLast = urlStr & "-1"
MM_moveNext = urlStr & Cstr(MM_offset + MM_size)
prev = MM_offset - MM_size
If (prev < 0) Then prev = 0
MM_movePrev = urlStr & Cstr(prev)
%>
<html>
<head>
<title>The Direct Hit</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
<script language="JavaScript">
<!--
function
GP_AdvOpenWindow(theURL,winName,features,popWidth,popHeight,winAlign,ignorelink,alwaysOnTop,autoCloseTime,borderless)
{ //v2.0
var leftPos=0,topPos=0,autoCloseTimeoutHandle, ontopIntervalHandle,
w = 480, h = 340;
if (popWidth > 0) features += (features.length > 0 ? ',' : '') +
'width=' + popWidth;
if (popHeight > 0) features += (features.length > 0 ? ',' : '') +
'height=' + popHeight;
if (winAlign && winAlign != "" && popWidth > 0 && popHeight > 0) {
if (document.all || document.layers || document.getElementById) {w
= screen.availWidth; h = screen.availHeight;}
if (winAlign.indexOf("center" != -1) {topPos =
(h-popHeight)/2;leftPos = (w-popWidth)/2;}
if (winAlign.indexOf("bottom" != -1) topPos =
h-popHeight; if (winAlign.indexOf("right" != -1) leftPos =
w-popWidth;
if (winAlign.indexOf("left" != -1) leftPos = 0; if
(winAlign.indexOf("top" != -1) topPos = 0;
features += (features.length > 0 ? ',' : '') + 'top=' +
topPos+',left='+leftPos;}
if (document.all && borderless && borderless != "" &&
features.indexOf("fullscreen" != -1) features+=",fullscreen=1";
if (window["popupWindow"] == null) window["popupWindow"] = new
Array();
var wp = popupWindow.length;
popupWindow[wp] = window.open(theURL,winName,features);
if (popupWindow[wp].opener == null) popupWindow[wp].opener = self;
if (document.all || document.layers || document.getElementById) {
if (borderless && borderless != ""
{popupWindow[wp].resizeTo(popWidth,popHeight);
popupWindow[wp].moveTo(leftPos, topPos);}
if (alwaysOnTop && alwaysOnTop != "" {
ontopIntervalHandle =
popupWindow[wp].setInterval("window.focus();", 50);
popupWindow[wp].document.body.onload = function()
{window.setInterval("window.focus();", 50);}; }
if (autoCloseTime && autoCloseTime > 0) {
popupWindow[wp].document.body.onbeforeunload = function() {
if (autoCloseTimeoutHandle)
window.clearInterval(autoCloseTimeoutHandle);
window.onbeforeunload = null; }
autoCloseTimeoutHandle =
window.setTimeout("popupWindow["+wp+"].close()", autoCloseTime *
1000); }
window.onbeforeunload = function() {for (var
i=0;i<popupWindow.length;i++) popupWindow[i].close();}; }
document.MM_returnValue = (ignorelink && ignorelink != "" ? false :
true;
}
//-->
</script>
</head>
<body text="#000000" link="#0000FF" vlink="#FF0000" alink="#FFFF00"
bgcolor="#FFFFFF">
<font face="Tahoma"><font face="Tahoma"><font size="2"> <font
color="#333333"><img src="/fireworks/directhit.png" width="160"
height="20"><br>
<br>
</font></font><font size="2" color="#333333"> <A
HREF="<%=MM_moveFirst%>">First</A>
| <A HREF="<%=MM_moveNext%>">Next</A>
| <A HREF="<%=MM_movePrev%>">Previous</A>
| <A HREF="<%=MM_moveLast%>">Last</A><br>
</font><font face="Tahoma" color="#333333"></font></font></font>
<form name="Form1" action="/modify.asp" method="POST">
<font face="Tahoma"><font face="Tahoma"><font face="Tahoma"
color="#333333"><font size="2"><A HREF="/modify.asp?<%= MM_keepURL &
MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value %>">Modify
Record</A></font> <font size="2">| <A HREF="/insert.asp?<%=
MM_keepURL & MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value %>">Insert
New Record</A> | <A HREF="/delete.asp?<%= MM_keepURL &
MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value %>">Delete
Current Record</A> |</font> <font size="2">Schedule Call | Invoice |
</font></font></font></font>
<br>
<br>
<table border="0" width="797">
<tr>
<td align="right" width="113">
<div align="left"><font face="Tahoma" color="#000000"
size="2.5">Customer
#</font></div>
</td>
<td colspan="2"><font size="2.5" color="#000000"
face="Tahoma"><%=(rsCust.Fields.Item("CustID".Value)%></font></td>
<td width="99"><font face="Tahoma" color="#000000"
size="2.5">Address</font></td>
<td width="356"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("Address".Value)%></font></td>
</tr>
<tr>
<td align="right" width="113">
<div align="left"><font face="Tahoma" color="#000000"
size="2.5">Customer's
Name</font></div>
</td>
<td colspan="2"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("CompanyName".Value)%></font></td>
<td width="99"><font face="Tahoma" color="#000000"
size="2.5">Address line
2</font></td>
<td width="356"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("Address2".Value)%></font></td>
</tr>
<tr>
<td align="right" width="113">
<div align="left"><font face="Tahoma" color="#000000"
size="2.5">Contact</font></div>
</td>
<td colspan="2"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("ContactFirstName".Value)%><%=(rsCust.Fields.Item("ContactLastName".Value)%></font></td>
<td width="99"><font face="Tahoma" color="#000000"
size="2.5">Address line
3</font></td>
<td width="356"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("Address3".Value)%></font></td>
</tr>
<tr>
<td align="right" width="113">
<div align="left"><font face="Tahoma" color="#000000"
size="2.5">Alternate
Contact</font></div>
</td>
<td colspan="2"><font face="Tahoma" color="#000000"
size="2.5"></font></td>
<td width="99"><font face="Tahoma" color="#000000"
size="2.5">City</font></td>
<td width="356"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("City".Value)%></font></td>
</tr>
<tr>
<td align="right" width="113">
<div align="left"><font face="Tahoma" color="#000000"
size="2.5">Phone
#</font></div>
</td>
<td colspan="2"> <font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("PhoneNumber".Value)%></font></td>
<td width="99"><font face="Tahoma" color="#000000"
size="2.5">State</font></td>
<td width="356"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("State".Value)%></font></td>
</tr>
<tr>
<td align="right" width="113">
<div align="left"><font face="Tahoma" color="#000000"
size="2.5">Alternate
Phone #</font></div>
</td>
<td colspan="2"> <font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("AltPhone".Value)%></font></td>
<td width="99"><font face="Tahoma" color="#000000"
size="2.5">Zip Code</font></td>
<td width="356"><font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("ZipCode".Value)%></font></td>
</tr>
<tr>
<td align="right" width="113">
<div align="left"><font face="Tahoma" color="#000000"
size="2.5">Fax #</font></div>
</td>
<td colspan="2"> <font face="Tahoma" color="#000000"
size="2.5"><%=(rsCust.Fields.Item("FaxNumber".Value)%></font></td>
<td width="99"><font color="#000000" face="Tahoma"
size="2.5">eMail</font></td>
<td width="356"><font color="#000000" size="2.5"
face="Tahoma"></font></td>
</tr>
</table>
<font face="Tahoma" color="#333333"><br>
</font> <font face="Tahoma" size="2"><font color="#333333">| <font
color="#0000CC" onClick="GP_AdvOpenWindow('/ins_note.asp?<%=
MM_keepURL & MM_joinChar(MM_keepURL) & "CustID=" &
rsCust.Fields.Item("CustID".Value
%>','InsertNote','fullscreen=no,toolbar=no,location=no,status=no,menubar=yes,scrollbars=no,resizable=yes',400,300,'center','','',0,'');return
document.MM_returnValue">Insert
Note</font> |</font> Activities | Quote | Invoice |</font><font
face="Tahoma" size="2"></font><font face="Tahoma" size="2"></font>
</form>
<font face="Tahoma" size="4"> <font size="2">Account
History</font></font><font face="Tahoma" size="2">
</font><font face="Tahoma" size="2"><br>
</font>
<table width="73%" border="0">
<tr bgcolor="#66CCFF">
<td width="10%"><i><font face="Tahoma"
size="2.5">Date</font></i></td>
<td width="10%"><i><font face="Tahoma"
size="2.5">Time</font></i></td>
<td width="52%"><i><font face="Tahoma"
size="2.5">Notes</font></i></td>
<td width="14%"><i><font face="Tahoma"
size="2.5">Salesman</font></i></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCust.EOF))
%>
<%If (Repeat1__numRows Mod 2) Then%>
<tr bgcolor="#CCCCCC">
<%Else%>
<tr bgcolor="#FFFFFF">
<%End If%>
<td width="10%"><font face="Tahoma" size="2"
color="#000000"><%=(rsCust.Fields.Item("Date".Value)%></font></td>
<td width="10%"><font face="Tahoma" size="2"
color="#000000"><%=(rsCust.Fields.Item("Time".Value)%></font></td>
<td width="52%"><font face="Tahoma" size="2"
color="#000000"><%=(rsCust.Fields.Item("Notes".Value)%></font></td>
<td width="14%"><font face="Tahoma" size="2"
color="#000000"><%=(rsCust.Fields.Item("Employee".Value)%></font></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCust.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
rsCust.Close()
%>
</font id='Tahoma'>
Replies
Replied 10 Mar 2002 11:05:04
10 Mar 2002 11:05:04 Owen Eastwick replied:
The problem is definately with the SQL statement, there is no WHERE clause to relate what you are selecting to the current customer.
I would expect something like:
rsCust.Source = "SELECT Customers.CustID, Customers.CompanyName,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.Address, Customers.Address2, Customers.Address3,
Customers.City, Customers.State, Customers.ZipCode,
Customers.PhoneNumber, Customers.FaxNumber, Customers.AltPhone,
Notes.TaskID, Notes.nCustID, Notes.Employee, Notes.Time, Notes.Date,
Notes.Notes FROM Customers LEFT OUTER JOIN Notes ON (Notes.nCustID =
Customers.CustID) WHERE Customers.CustID = " & varCustID
Where varCustID is determined when the user logs in, perhaps you could retreive this and hold it in a session variable, in which case you could do something like:
...WHERE Customers.CustID = " & Session("CustID"
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
I would expect something like:
rsCust.Source = "SELECT Customers.CustID, Customers.CompanyName,
Customers.ContactFirstName, Customers.ContactLastName,
Customers.Address, Customers.Address2, Customers.Address3,
Customers.City, Customers.State, Customers.ZipCode,
Customers.PhoneNumber, Customers.FaxNumber, Customers.AltPhone,
Notes.TaskID, Notes.nCustID, Notes.Employee, Notes.Time, Notes.Date,
Notes.Notes FROM Customers LEFT OUTER JOIN Notes ON (Notes.nCustID =
Customers.CustID) WHERE Customers.CustID = " & varCustID
Where varCustID is determined when the user logs in, perhaps you could retreive this and hold it in a session variable, in which case you could do something like:
...WHERE Customers.CustID = " & Session("CustID"
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo