Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Displaying records in a table

Status
Not open for further replies.
rosebud289

rosebud289

Programmer
Jan 13, 2005
25
0
0
US
I have developed a web page that retrieves a list of topics and speakers from an Access database and displays them in a table. Some topics have multiple speakers, therefore I do not want the topics to be displayed again - just additional speakers. The code I have developed works fine for one or two speakers, but if there's more than two then the speaker's name is displayed above the table. Could you please let me know how to fix this problem? Thanks!

Example: TOPIC SPEAKER(S)
ABC's of Scuba Diving Joan Dickson
Mike Reynolds
George Stevens

Here's the code:
<% Dim Bool
header = Request.Form("header")
speaker_name = Request.Form("speaker_name")
lecture_time = Request.Form("lecture_time")
header = replace(header, "'", "")
q = "SELECT s_id, topic, description, min_time, first_name, last_name, headers, av_requirements FROM webupdates WHERE headers LIKE '%" & header & "%' AND S_ID LIKE '%" & speaker_name & "%' AND min_time LIKE '%" & lecture_time & "%' order BY topic, last_name, first_name"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open q, "DSN=access;"
%>
<%If NOT rs.EOF then%>
<% Bool = True %>
<% num = 0 %>
<%while not rs.EOF%>
<% num = num + 1 %>
<% if num mod 2 = 0 then %>
<% strColor = "#CCDDDD" %>
<% else %>
<% strColor = "#d1e3f8" %>
<% end if %>
<%noav=rs("av_requirements").value%>
<%if nodup<>rs("topic").value then%>
<tr>
<td bgcolor="<%=strColor%>" height="127" valign="middle"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b><font face="Arial, Helvetica, sans-serif"><%=rs("topic")%></font></b><font face="Arial, Helvetica, sans-serif"><br>
<%=rs("description")%></font></font></td>
<td valign="middle" bgcolor="<%=strColor%>">
<div align="center"><font face="Arial, Helvetica, sans-serif" size="2"><%=rs("min_time")%>
min.</font></div>
</td>
<td valign="middle" bgcolor="<%=strColor%>" align="center"><font face="Arial, Helvetica, sans-serif" size="2">

<%if noav=" " then%>
<%Response.Write("see ")%>
<%else%><%=rs("av_requirements")%><% end if%>

</font> </td>
<td bgcolor="<%=strColor%>" valign="middle"><a href="bios_revised.asp?s_id=<%=rs("s_id")%>"><font face="Arial, Helvetica, sans-serif" size="2"><%=rs("first_name")%>
<%Response.Write(" ")%>
<%=rs("last_name")%></font></a><br>
<%else%>
<a href="bios_revised.asp?s_id=<%=rs("s_id")%>"><font face="Arial, Helvetica, sans-serif" size="2"><%=rs("first_name")%>
<%Response.Write(" ")%>
<%=rs("last_name")%></font></a> </td>

<%end if%>
<% nodup=rs("topic").value%>
<%rs.MoveNext%>
<br>
<%wend%>
<%else%>
<%Bool=False%>
<% Response.Write("No topics found.") %><br><br>
<% end if%>
</tr>
 
Sort by date Sort by votes
That didn't work...any other suggestions? Thanks!
 
Upvote 0 Downvote
Code: 
<%
Dim Bool
header = Request.Form("header")
speaker_name = Request.Form("speaker_name")
lecture_time = Request.Form("lecture_time")
header = replace(header, "'", "")

Set rs = Server.CreateObject("ADODB.Recordset")
q = "SELECT s_id, topic, description, min_time, first_name, last_name, headers, av_requirements " _
  & "FROM webupdates WHERE headers LIKE '%" & header & "%' " _
  & "AND S_ID LIKE '%" & speaker_name & "%' AND min_time LIKE '%" & lecture_time & "%' " _
  & "order BY topic, last_name, first_name"
rs.Open q, "DSN=access;"

If NOT rs.EOF then
  Bool = True 
  num = 0 
  While not rs.EOF
    num = num + 1 
    if num mod 2 = 0 then 
      strColor = "#CCDDDD" 
    else 
      strColor = "#d1e3f8" 
    end if 
		
    noav = rs("av_requirements").value
    if (nodup <> rs("topic").value) then
      'New topic.  Start new table row and populate the TD cells 
      'for lecture topic, time, and requirements
%>        
        <tr> 
          <td bgcolor="<%= strColor%>" valign="middle" height="127">
            <!-- Lecture topic table cell -->
            <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
              <b>
                <font face="Arial, Helvetica, sans-serif">
                  <%= rs("topic")%>
                </font>
              </b>
              <font face="Arial, Helvetica, sans-serif">
              <br>
              <%= rs("description")%>
              </font>
            </font>
          </td>
          <td bgcolor="<%=strColor%>" valign="middle" > 
            <!-- time table cell -->
            <div align="center">
						  <font face="Arial, Helvetica, sans-serif" size="2">
						    <%= rs("min_time")%> min.
						  </font>
						</div>
          </td>
          <td bgcolor="<%=strColor%>" valign="middle" align="center">
            <!-- requirements table cell -->
            <font face="Arial, Helvetica, sans-serif" size="2"> 
<%
      if (noav = " ") then
        Response.Write "see "
      else
				Response.Write rs("av_requirements")
		  end if
%> 
            </font> 
          </td>
          
          
<%  else 
      'Topic same as previous row.  Wtart new table row with 
      'EMTPY cells for topic, time, and requirements
%>
        <tr>  
          <td bgcolor="<%= strColor%>" valign="middle" height="127">
            <!-- Lecture topic table cell is empty -->
            &nbsp;  
          </td>	          
          <td bgcolor="<%= strColor%>" valign="middle" height="127">
            <!-- time table cell is empty -->
            &nbsp;  
          </td>	          
          <td bgcolor="<%= strColor%>" valign="middle" height="127">
            <!-- requirements table cell is empty -->
            &nbsp;  
          </td>	          
<%  
    end if 
    
    'Now do table cell for speaker's name cell and close the table row
%>
          <td bgcolor="<%= strColor%>" valign="middle">
					  <!-- speaker name table cell -->
					  <font face="Arial, Helvetica, sans-serif" size="2">
					    <a href="bios_revised.asp?s_id=<%= rs("s_id")%>">
						    <%= rs("first_name") & " " & rs("last_name")%> 
              </a>
            </font>
            <br>
          </td>
        </tr>
<%          
    nodup = rs("topic").value
    rs.MoveNext
  wend 
else
  'Recordset was empty
  Bool = False
  Response.Write "No topics found. <br><br>" 
end if
%>
 
Upvote 0 Downvote
The code you provided worked but the speakers' names are listed in separate rows. Is it possible to have them all listed in the same cell?
 
Upvote 0 Downvote
There is always a way. :)

You'll need two loops instead of just one.

The quick & dirty way would run both loops off the same recordset. This would work but the only way I'd do it is with some really explicit comments in the code, because it is really not the way one SHOULD do such things.

A cleaner way to do it would be to use 2 separate recordsets. The outer loop would use a recordset of distinct topics. Inside that loop you create one table row for each topic and then make a second recordset of the speakers for that topic. Loop through the second recordset to write the values for every speaker into a single cell of the table row.

Code: 
Set rsOuter = Server.CreateObject("ADODB.Recordset")
Set rsInner = Server.CreateObject("ADODB.Recordset")

q = "SELECT Distinct topic FROM webupdates  ORDER BY topic"
rsOuter.Open q, "DSN=access;"
Do While Not rsOuter.EOF
  
  'Get topic details
  q = "SELECT topic, description, min_time, av_requirements " _
    & "FROM webupdates WHERE topic = '" & rsOuter(0) & "'"
  rsInner.Open q, "DSN=access;"

  'Start table row
%>
  <tr>
    <td>
      <%= rsInner("topic") %>
      <br>
      <%= rsInner("description") %>
    </td>
    <td>
      <%= rsInner("min_time") %>
      <br>
      <%= rsInner("av_requirements") %> 
    </td>
    <td>
<%
  'Build the contents of the Speaker table cell
  
  q = "SELECT s_id, first_name, last_name, " _
    & "FROM webupdates WHERE headers LIKE '%" & header & "%' " _
    & "AND S_ID LIKE '%" & speaker_name & "%' AND min_time LIKE '%" & lecture_time & "%' " _
    & "AND topic = '" & rsOuter(0) & "' " 
    & "order BY topic, last_name, first_name"
  
  rsInner.Close 
  rsInner.Open q, "DSN=access;"
  Do While Not rsInner.EOF
%>
    <a href="bios_revised.asp?s_id=<%= rs("s_id")%>">
      <%= rs("first_name") & " " & rs("last_name")%> 
    </a>
    <br>
<%   
    rsInner.MoveNext
  Loop

   'Close both the speaker table cell and the table row  
%>
    </td>
  </tr>
<%
  rsOuter.MoveNext
Loop

Obvious the code above leaves out a lot of the nice HTML in your example. I did this on purpose so that I could try to keep from being distracted. I wrote it in Notepad and obviously didnt test it because I don't have a copy of your database but hopefully you will be able to determine from this stripped down code what I am trying to describe about using an outside loop and an inside loop.
 
Upvote 0 Downvote
I was gonna suggest the distinct clause too but Sheco beat me to it ;-) However, you may want to normailize your database. Since you pretty much answered your own question at the begining, "Some topics have multiple speakers, therefore I do not want the topics to be displayed again - just additional speakers," you should have another table for topics and create a many-to-one or many-to-many relationship and then join the tables. This way you won't have duplication problems because the database will be based on relationships by design and not relying on a flat, error-proned database.

BSL
 
Upvote 0 Downvote
Could you please provide more detailed code for the areas in which you have the word "loop"? Thanks!
 
Upvote 0 Downvote
Loop is part of the code!

The following examples are roughly equivilant:
Code: 
Do While x < 10
  Call DoSomething(x)
  x = x + 1
Loop

Code: 
While x < 10
 Call DoSomething(x)
  x = x + 1
Wend

Code: 
For x = 0 To 9
  Call DoSomething(x)
Next

I prefer to use Do While/Loop syntax instead of While/Wend... thats all.
 
Upvote 0 Downvote
I successfully screwed up the entire page! Please let me know what I did wrong. Thanks !!

<%
Dim Bool
header = Request.Form("header")
speaker_name = Request.Form("speaker_name")
lecture_time = Request.Form("lecture_time")
header = replace(header, "'", "")

Set rsOuter = Server.CreateObject("ADODB.Recordset")
Set rsInner = Server.CreateObject("ADODB.Recordset")

q = "SELECT DISTINCT topic FROM webupdates ORDER BY topic"
rs.Outer.Open q, "DSN=access;"
Do While Not rsOuter.EOF

q = "SELECT topic, description, min_time, av_requirements " _
& "FROM webupdates WHERE topic='" & rsOuter(0) & "'"
rsInner.Open q, "DSN=access;"

If NOT rsOuter.EOF then
Bool = True
num = 0
While not rsOuter.EOF
num = num + 1
if num mod 2 = 0 then
strColor = "#CCDDDD"
else
strColor = "#d1e3f8"
end if

noav = rs("av_requirements").value%>
<tr>
<td bgcolor="<%= strColor%>" valign="middle" height="127">
<!-- Lecture topic table cell -->
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">
<b>
<font face="Arial, Helvetica, sans-serif">
<%= rs("topic")%>
</font>
</b>
<font face="Arial, Helvetica, sans-serif">
<br>
<%= rs("description")%>
</font>
</font>
</td>
<td bgcolor="<%=strColor%>" valign="middle" >
<!-- time table cell -->
<div align="center">
<font face="Arial, Helvetica, sans-serif" size="2">
<%= rs("min_time")%> min.
</font>
</div>
</td>
<td bgcolor="<%=strColor%>" valign="middle" align="center">
<!-- requirements table cell -->
<font face="Arial, Helvetica, sans-serif" size="2">
<%= rs("av_requirements")%>
</font>
</td>

<td bgcolor="<%= strColor%>" valign="middle" height="127">
<!-- Lecture topic table cell is empty -->
&nbsp;
<% q="SELECT s_id, first_name, last_name, "_
& "FROM webupdates WHERE headers LIKE '%" & header & "%' "_
& "AND S_ID LIKE '%" & speaker_name & "%' AND min_time LIKE '%" & lecture_time & "%' "_
& "AND topic = '" & rsOuter(0) & "' "_
& "order BY topic, last_name, first_name"

rsInner.Close
rsInner.Open q, "DSN=access;"
Do While Not rsInner.EOF
%><a href="bios_revised.asp?s_id=<%= rs("s_id")%>">
<%= rs("first_name") & " " & rs("last_name")%>
</a><% rsInner.MoveNext
Loop%></td>

</tr>
<%
rsOuter.MoveNext
Loop
wend %>
<% else
'Recordset was empty
Bool = False
Response.Write "No topics found. <br><br>"
end if
%>

</tr>
 
Upvote 0 Downvote
Code: 
<%
Dim Bool
header = Request.Form("header")
speaker_name = Request.Form("speaker_name")
lecture_time = Request.Form("lecture_time")
header = replace(header, "'", "")

Set rsOuter = Server.CreateObject("ADODB.Recordset")
Set rsInner = Server.CreateObject("ADODB.Recordset")

'Fetch list of distinct topics into rsOuter recordset
q = "SELECT DISTINCT topic FROM webupdates ORDER BY topic"
rsOuter.Open q, "DSN=access;"


If NOT rsOuter.EOF then
  'Topic recordset was not empty
  Bool = True 
  num = 0 
  
  'Loop once for each distinct topic in rsOuter
  Do While Not rsOuter.EOF
    num = num + 1 
    if num mod 2 = 0 then 
      strColor = "#CCDDDD" 
	  else 
      strColor = "#d1e3f8" 
    end if 
	    
	  'Fetch the details about this topic into rsInner recordset
    q = "SELECT topic, description, min_time, av_requirements " _
      & "FROM webupdates WHERE topic='" & rsOuter(0) & "'"
    rsInner.Open q, "DSN=access;"
%>
        <tr> 
          <!-- Begin row for distinct topic -->
          <td bgcolor="<%= strColor%>" valign="middle" height="127">
            <!-- Lecture topic table cell -->
            <font face="Verdana, Arial, Helvetica, sans-serif" size="2">
              <b>
                <font face="Arial, Helvetica, sans-serif">
                  <%= rsInner("topic")%>
                </font>
              </b>
              <br>
              <font face="Arial, Helvetica, sans-serif">
                <%= rsInner("description")%>
              </font>
            </font>
          </td>
          <td bgcolor="<%=strColor%>" valign="middle" > 
            <!-- time table cell -->
            <div align="center">
              <font face="Arial, Helvetica, sans-serif" size="2">
                <%= rsInner("min_time")%> min.
              </font>
            </div>
          </td>
          <td bgcolor="<%=strColor%>" valign="middle" align="center">
            <!-- requirements table cell -->
            <font face="Arial, Helvetica, sans-serif" size="2"> 
              <%= rsInner("av_requirements")%> 
            </font> 
          </td>
          <td bgcolor="<%= strColor%>" valign="middle" height="127">
            <!-- speakers table cell -->
<% 
    'Fetch a list of all speakers for this topic into rsInner recordset
    q = "SELECT s_id, first_name, last_name, "_
      & "FROM webupdates WHERE headers LIKE '%" & header & "%' "_
      & "AND S_ID LIKE '%" & speaker_name & "%' AND min_time LIKE '%" & lecture_time & "%' "_
      & "AND topic = '" & rsOuter(0) & "' "_
      & "ORDER BY topic, last_name, first_name"
     
    rsInner.Close 'clear out previous contents of rsInner
    rsInner.Open q, "DSN=access;"  're-open rsInner now with list of speakers
      
    'Loop once for each speaker in rsInner
    Do While Not rsInner.EOF
%>
            <a href="bios_revised.asp?s_id=<%= rs("s_id")%>">
              <%= rs("first_name") & " " & rs("last_name")%> 
            </a>
            &nbsp; 
<% 
      rsInner.MoveNext  'Move to next speaker
    Loop
%>
          </td>               
          <!-- End row for distinct topic -->
        </tr>
<%          
    rsOuter.MoveNext  'Move to next topic
  Loop 
Else
  'Topic recordset was empty
  Bool = False
  Response.Write "No topics found. <br><br>" 
End If
%>
 
Upvote 0 Downvote
I'm getting an error message that states:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

/speakersbureau/results_revised_original.asp, line 209



Line 209 reads: rsInner.Open q, "DSN=access;"

Any suggestions? Thanks so much for your help!!!
 
Upvote 0 Downvote
The error is in the SELECT statment.

If your code still looks like the code posted above then immediately before this line, add this:

Response.Write q

This assumes that your SELECT statement is in a variable named q

This won't fix the error but it will show you the SQL syntax. You'll probably spot the error right away but if no then reply back with the SQL string.
 
Upvote 0 Downvote
try commenting the line

'rsInner.Open q, "DSN=access;"
response.write q to see what you get

also comment all request to rsInner below the response.write
 
Upvote 0 Downvote
The error message is being displayed in the speakers field. Topic, description and min_time values are being displayed correctly.
 
Upvote 0 Downvote
make sure that you insert that line BEFORE the call to the .Open method.

If so, you might try changing the response.write to have something unique in front of it like this:

Response.Write "ZZZZZZZZZZZ: " & q

Then refresh the page, if you don't see the SQL statement then use "View Source" and search for the ZZZZZZZZZZZ
 
Upvote 0 Downvote
Here's what's being displayed:

SELECT s_id, first_name, last_name, FROM webupdates WHERE header LIKE '%American Culture%' AND S_ID LIKE '%%%' AND min_time LIKE '%%%' AND topic = '19th-Century German Art Songs' ORDER BY topic, last_name, first_name
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
60
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
76
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
78
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
iis woos (not displaying pages correctly)
Replies
1
Views
75
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
78
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top