#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    37
    Rep Power
    15

    Question display db records from multiple tables problem


    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
    %>

  2. #2
  3. Digitally Challenged
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    280
    Rep Power
    16
    i'm guessing its a problem with your query. could you post it?
    My brain cells are like a storm trooper's armor: useless
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    37
    Rep Power
    15
    I am unclear what you mean by "post."
  6. #4
  7. Digitally Challenged
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    280
    Rep Power
    16
    show us the code. in stead of giving us
    sql="SELECT * FROM jobdescmain,Prim1,Prim2 ...etc..."
    give us the whole querystatement. post: put it on the forum so we can see it.
    My brain cells are like a storm trooper's armor: useless
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    37
    Rep Power
    15
    Sorry for taking so long, the whole SQL statement is supposed to look like this:

    sql="SELECT * FROM jobdescmain,Prim1,Prim2,Prim3,Prim4,Prim5,Prim6,Prim7,Prim8,Prim9,Prim10,Prim11,Prim12,Prim13,Prim14 ,Prim15,Prim16,Prim17,Prim18,Prim19,Prim20"

    Though, I get an error if I go past Prim5.

  10. #6
  11. Digitally Challenged
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    280
    Rep Power
    16
    ok, are you selecting a bunch of stuff from like 20 different tables? because if you aren't, then your query is messed up. maybe it should look more like
    Code:
    select * from table name
    and your loop, well maybe you want to try something like this
    Code:
    <%while not rs.EOF
    for x = 0 to rs.Fields.Count - 1
    response.write (rs(x)) & "<br>"
    next
    rs.MoveNext
    wend%>
    if this isn't it, could you tell me what you are trying to do?
    My brain cells are like a storm trooper's armor: useless

IMN logo majestic logo threadwatch logo seochat tools logo