display db records from multiple tables problem

[blodefood]

I am trying to set up a catalogue of job descriptions for the company. I have a database with 21 tables. There is the MAIN table with the job description name and general duties. (2 fields plus one for Primary Duties). Then for the primary duties there are 20 tables, each with a primary duty title and a description of that duty. (2 fields with ID linked to Primary Duties in MAIN). This was to allow for up to 20 primary duties per job description. There are two sample records in this database.

I have an ASP page with an HTML table to display the records, but it is not picking up the primary duties for the right position and repeats blank table rows until there are 20 some rows

At the moment I am trying to display all the records, but eventually, I would like to set up a separate page with clickable links to display selected records, i.e. "click here to view Receptionist job description."

This is the code I have thus far. Can anyone help?


<%
if request.querystring("sort")<>"" then
sort=request.querystring("sort")
else
sort="JobDescName"
end if

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("jobdesc.mdb"))
set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM jobdescmain,Prim1,Prim2 ...etc..."
rs.Open sql,conn

do until rs.EOF
for each x in rs.Fields%>

<table width="700" border="1" cellspacing="4" cellpadding="4" bordercolor="#007A62">
<tr>
<td width="200" valign="top" bgcolor="#F5DE92"><font size="2" face="Verdana, Arial, Helvetica,

sans-serif"><b><%response.write rs.Fields("JobDescName")%></b><br><br>
<hr width="195" align="center" color="#CC6319"><b>
General Duties:</b><br>
<br>
<%response.write rs.Fields("GenDuties")%>
</font></td>
<td width="500" valign="top" bgcolor="#F8F5D4"><b><font size="2" face="Verdana, Arial,

Helvetica, sans-serif">Primary
Duties:<br></b>
<br><%response.write rs.Fields("PrimeDuties")%><br>

</font></td>
</tr>
</table>


<%
next
rs.MoveNext
loop
rs.close
conn.close
%>

:confused:

[Ribeyed]

Hi,

one thing i pick up on is the use of the word "sort" as a variable. "sort" is a reserved word and should not be used as a variable. Change your variable to something like "thesort"

Hope this helps

[Ribeyed]

Hi,
also i don't understand the double loop you have can you explain why you need to do a loop within a loop?

If you don't know what i mean i'll explain my self further.

[blodefood]

I guess this is a case of recycling old code from other pages. One can only adapt it so far.

I take it the sort is not necessary if I specifiy ORDER BY in my SQL statement.

I am out of the loop on the double loop as I used this piece from someone else. I am fairly new to ASP, so sometimes I don't understand all the pieces I have put together.

Again, it is simply to display all the records and once I get that working, I will work on a separate page with a query in the link so only a single job description will display at a time.

:rolleyes: