Code executing even though end of recordset

[emozley]

For some reason even though the query executing returns no records there is still a chunk of code executing. So it's showing a row in the table allowing you to either update/delete when there is nothing to update or delete. What is the best way of stopping this?

Thanks very much

Ed

<%

Set DB = Server.CreateObject("ADODB.Connection")
Set TBL = Server.CreateObject("ADODB.RecordSet")

DB.Mode = adModeReadWrite
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("../../directory/userdb.mdb"))

TBL.Open "SELECT Users.FirstName, Users.Surname, StaffStatusHistory.HistoryID, StaffStatusHistory.PeriodStart, StaffStatusHistory.PeriodEnd, StaffStatusHistory.Monday, StaffStatusHistory.Tuesday, StaffStatusHistory.Wednesday, StaffStatusHistory.Thursday, StaffStatusHistory.Friday FROM Users LEFT JOIN StaffStatusHistory ON Users.UserID=StaffStatusHistory.UserID WHERE Users.UserID=" & Request.QueryString("UserID") & " ORDER BY PeriodStart ASC", DB

%>

<table width="100%" cellspacing="0" cellpadding="5" border="1" bordercolor="navy">
<tr>
<td colspan="7" align="middle" bgcolor="green"><font face="verdana" size="4">
Employment History for <font color="red"><% = TBL("FirstName") & " " & TBL("Surname") %></font>
</font>
</td>
<td rowspan="2" align="middle"><img src="../graphics/bwblogo.jpg" width="64" height="64"></td>
</tr>
<tr>
<td width="15%" bgcolor="FFFFCC"><font face="verdana" size="3"><b>Period Start</td>
<td width="8%" align="middle"><font face="verdana" size="2"><b>MON</font></td>
<td width="8%" align="middle"><font face="verdana" size="2"><b>TUE</font></td>
<td width="8%" align="middle"><font face="verdana" size="2"><b>WED</font></td>
<td width="8%" align="middle"><font face="verdana" size="2"><b>THU</font></td>
<td width="8%" align="middle"><font face="verdana" size="2"><b>FRI</font></td>
<td width="15%" bgcolor="FFFFCC"><font face="verdana" size="3"><b>Period End</td>
</tr>
<%

Do While Not TBL.EOF
%>
<tr>
<form method="post" action="updatestatushistory.asp">
<input type="hidden" name="HistoryID" value="<% = TBL("HistoryID") %>">
<input type="hidden" name="UserID" value="<% = Request.QueryString("UserID") %>">
<td bgcolor="FFFFCC"><input type="text" size="10" name="PeriodStart" value="<% = TBL("PeriodStart") %>" style="font-family: Verdana; font-size: 10pt">
<td align="middle"><input type="checkbox" name="Monday" <% If TBL("Monday")=True Then Response.Write("CHECKED=CHECKED") %>></td>
<td align="middle"><input type="checkbox" name="Tuesday" <% If TBL("Tuesday")=TRUE Then Response.Write("CHECKED=CHECKED") %>></td>
<td align="middle"><input type="checkbox" name="Wednesday" <% If TBL("Wednesday")=TRUE Then Response.Write("CHECKED=CHECKED") %>></td>
<td align="middle"><input type="checkbox" name="Thursday" <% If TBL("Thursday")=TRUE Then Response.Write("CHECKED=CHECKED") %>></td>
<td align="middle"><input type="checkbox" name="Friday" <% If TBL("Friday")=TRUE Then Response.Write("CHECKED=CHECKED") %>></td>
<td align="left" bgcolor="FFFFCC"><input type="text" size="10" name="PeriodEnd" value="<% = TBL("PeriodEnd") %>"<input type="submit" value="Update" style="font-family: Verdana; font-size: 10pt"></td>
<td align="left">
<input type="submit" value="Update" style="font-family: Verdana; font-size: 10pt; width: 100;">
<input type="button" value="Delete" style="font-family: Verdana; font-size: 10pt; width: 100;" onClick="window.location.href='confirmdelete.asp?UserID=<% = Request.QueryString("UserID") %>&HistoryID=<% = TBL("HistoryID") %>'">
</td>
</form>
</tr>
<%

TBL.MoveNext
Loop

TBL.Close

Set TBL=Nothing
Set DB=Nothing

%>
<tr>
<form method="post" action="addstatustohistory.asp">
<input type="hidden" name="UserID" value="<% = Request.QueryString("UserID") %>">
<td><input type="text" size="10" name="PeriodStart" style="font-family: Verdana; font-size: 10pt">
<td align="middle"><input type="checkbox" name="Monday"></td>
<td align="middle"><input type="checkbox" name="Tuesday"></td>
<td align="middle"><input type="checkbox" name="Wednesday"></td>
<td align="middle"><input type="checkbox" name="Thursday"></td>
<td align="middle"><input type="checkbox" name="Friday"></td>
<td align="left">
<input type="text" size="10" name="PeriodEnd" style="font-family: Verdana; font-size: 10pt"></td>
<td align="left">
<input type="submit" value="Add" style="font-family: Verdana; font-size: 10pt; width: 100;">
</td>
</form>
</tr>
</table>

 

[Chopstik]

I must be having flashbacks since I keep thinking I've seen the answers for questions somewhere in the deep recesses of my (usually failing) memory...

Just put an if statement before your loop (and don't forget to close your if statement after your loop):

if not TBL.EOF then

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Tarwn]

Shouldn't be necessary with the While statement worded the way it is (at least I've never seen it gnore a while statement).
My guess would be that you do have a User record and the values from your second table (that you left joined to) are empty/null since there wsn't a record in the second table to match the first. Look at the HTML above the loop and see if it's outputting the users name.

If that is the case, then the easiest way to stop it would be to add an if statement outside the loop to check if something like HistoryID is null.

-T