I’m working on a small website and I’m using ASP for some database driven content. Right now I’m retrivieng a set of records into a table. And I’m thinking there should be a more efficient way accomplishing this.
The problem I’m running into is the HTML code for the table. Essentially, a table is built by rows. So first, the top row is displayed, then the second row, etc. Each record is made up of 5 rows (only two have database content, the rest are for looks) and 3 columns, and the table is limited to a max of 5 such records per line (due to size). So what I have to do is iterate through the recordset over and over again, and run loops that’ll first display the first row 5 times (if there are 5 or more records), then the next row 5 times, whilst iterating through the database, then I have to close the recordset, and reopen it two rows down, all the while looping.
This whole process repeats every 5 records (the end of each line). Is there a more elegant way of hadling this problem? I wish I could just repeat a small table (of 5 rows and 3 columns) per each record, but if I do this, each table is placed on a new line. With the method I’m using now, the end product is a whole table with 5 records per line, but it’s clunky.
My first thought is to dummy up your SQL statement so it looks like the non-DB stuff comes from the DB, ie:
select ‘firstrowdata’, ‘secondrowdata’, ‘thirdrowdata’, customer.name, customer.address from customers
Then you only have one loop, splatting info from the DB into the table. Even if the data you have to put into the first 3 rows is not static, you can at least use the ‘firstrowdata’ columns as placeholders and replace it with the actual data, eliminating the need to create all those loops.
Another question - any reason why you’re using a table, and not one of the built-in ASP grids (dataview control, iirc)?
I don’t think I can quite picture what the problem is. Any chance you could post a relevant code snipp?
And, in line with Athena’s comment… is this ASP classic (.asp files) or ASP dot net?? ASP classic is a fine design choice for something like this, but it does not have the built-in databound controls. Not sure that they’d work well for your situation anyway – though that may change once I understand more about the problem.
I’m working with classic ASP. I knew it would be hard to get my point across to the experts I’m going to try and post some code, and a link to the page in a few minutes see if that helps any.
I think you may need to use a JOIN in your SQL…but that’s just a guess, not having seen the code.
So you need to get all of the info for one HTML table (or er…one set of 5 rows for the table?) in one row of the recordset.
Sort of like this:
Dim strSQL, objRS
strSQL = "SELECT c.CompanyName, p.FirstName, p.LastName, p.CompanyID FROM People p INNER JOIN Companies c ON c.ID = p.CompanyID ORDER BY c.CompanyName"
Set objRS = Connect.Execute(strSQL)
Dim CompanyName, FirstName, LastName, LastCompanyName
If NOT objRS.EOF Then
Response.Write "<table width=600 cellpadding=2 cellspacing=0 border=1>"
Do Until objRS.EOF
CompanyName = objRS("CompanyName")
FirstName = objRS("FirstName")
LastName = objRS("LastName")
If CompanyName <> LastCompanyName Then
Response.Write "<tr><td colspan=4>"& CompanyName &"</td></tr>"
End If
Response.Write "<tr><td>"& FirstName &"</td>"
Response.Write "<td>"& LastName &"</td>"
Response.write "<td>EDIT</td>"
Response.Write "<td>DELETE</td>"
Response.Write "</tr>"
LastCompanyName = CompanyName
objRS.MoveNext
Loop
Response.Write "</table>"
Else
Response.write "NO RECORDS TO SHOW"
End If
objRS.Close
Set objRS = Nothing
Connect.Close
Set Connect = Nothing
So in this example, we get the company name for each person as part of the record retrieval row for that person. When writing out the HTML we only write the Company Name if it’s different than the previous company name. The results would be like:
COMPANY A
John Doe EDIT DELETE
Jane Doe EDIT DELETE
Bill Smith EDIT DELETE
COMPANY B
Ronald Regan EDIT DELETE
Jimmy Carter EDIT DELETE
George Bush EDIT DELETE
Bill Clinton EDIT DELETE
COMPANY C
Donald Trump EDIT DELETE
Martha Stewart EDIT DELETE
Bill Gates EDIT DELETE
Steve Jobs EDIT DELETE
Whiz Kid EDIT DELETE
Basically the page in question retrieves member data from the database, and displays a table with each member’s ID picture and the member name underneath (you can see the gist of it at the actual page here:www.arma-nj.org/members.asp)
This is the code I have so far. It’s not all there yet, I’m just experimenting to see what solution works best. Unfortunately it’s long and I havent commented it yet
The basic though process:
Retrive the number of records in this category and assign them to a variable.
Re-open the DB connection since I’m going to read the records in now.
Start my main loop.
Use an if statement to check and see if there ar emore than 5 records (that’s all that’ll fit in one line). If there are more than 5 records, the whole loop will restart after 5 records and start a new table a new. If there are less than 5 records, then a variable is set to make sure that only the correct number of columns are created.
Table heading.
My first inner loop. Here I’m iterating through the top row. It’s just a black “line” essentially. It should run through as many times as there are records (upto 5 times).
My next loop does the same as the above, the member pictures go in this row however, so the recordset is iterated through and the correct picture is displayed. Then the recordset is closed.
Same as #6.
Same as #7, except that the member name is displayed rather than member picture. The recordset is reopened and reclosed.
same as #6
Finally the whole thing repeats itself for the next 5 records.
Thsi is why I’m concerned, it seems like alot of opening and closing of the recordset, and a lot of loops
<!--#INCLUDE FILE="connection.inc"-->
<%
Set RS = oConn.Execute("SELECT * FROM members WHERE studygroup='NorthJersey'")
iNumRec = 0
While Not RS.EOF
iNumRec = iNumRec + 1
RS.MoveNext
Wend
Response.Write iNumRec
RS.Close
Set RS = oConn.Execute("SELECT * FROM members WHERE studygroup='NorthJersey'")
x = 0
If NOT RS.EOF Then
If iNumRec - x >= 5 Then
iRows = 5
iTemp = iRows
ElseIf (iNumRec - x < 5) And (iNumRec - x <> 0) Then
iRows = iNumRec - x
iTemp = iRows
Else
iRows = 0
iTemp = iRows
End If
%>
<table border="0" cellspacing="0" cellpadding="0">
<tr height="2">
<%
Do While iRows > 0
%>
<td bgcolor="black" width="2" height="2"></td>
<td bgcolor="black" width="100" height="2"></td>
<td bgcolor="black" width="2" height="2"></td>
<%
If iRows > 1 Then
%>
<td width="5" height="2"></td>
<%
End If
iRows = iRows - 1
Loop
%>
</tr>
<%
iRows = iTemp
%>
<tr height="100">
<%
Do While iRows > 0
Response.Write iRows
%>
<td bgcolor="black" width="2" height="100"></td>
<td align="center" valign="middle" bgcolor="white" width="100" height="100">
<div align="center">
<%
If RS.("biopic1") <> ''
%>
<img src="images/biopics/<%RS.("biopic1")%>" alt="" width="100" height="100" border="0"></div>
<%
Else
%>
<img src="images/biopics/NoBioPic_s.gif" alt="" width="100" height="100" border="0"></div>
<%
End If
%>
</td>
<td bgcolor="black" width="2" height="100"></td>
<%
If iRows > 1 Then
%>
<td width="5" height="100"></td>
<%
End If
iRows = iRows - 1
RS.MoveNext
Loop
RS.Close
%>
</tr>
<%
iRows = iTemp
%>
<tr height="2">
<%
Do While iRows > 0
%>
<td bgcolor="black" width="2" height="2"></td>
<td bgcolor="black" width="100" height="2"></td>
<td bgcolor="black" width="2" height="2"></td>
<%
If iRows > 1 Then
%>
<td width="5" height="2"></td>
<%
End If
iRows = iRows - 1
Loop
%>
</tr>
<%
iRows = iTemp
%>
<tr>
<%
Set RS = oConn.Execute("SELECT * FROM members WHERE studygroup='NorthJersey'")
Do While iRows > 0
%>
<td width="2"></td>
<td class="BlackSmall" align="center" valign="top" width="100">
<div align="center">
Gary Grzybek<br>
(Director)</div>
</td>
<td width="2"></td>
<%
If iRows > 1 Then
%>
<td width="5"></td>
<%
End If
iRows = iRows - 1
Loop
%>
</tr>
<%
RS.Close
%>
</table>
<%
Else
Response.Write "No Links In This Category."
RS.Close
End If
%>
I’m going to try and post some code, and a link to the page in a few minutes see if that helps any.