Why is a field repeating?

[Daz]

I'm working on a website in work at the moment which will give a list of publications held in an Access database. The field "Class"(category) I'm using to generate a menu on the left. Click on a category and it'll bring up all the publications under that category. Now I've two problems with what I've done so far.

Firstly if you go to this page you will see that the category "Customer Information and External Guidance" has been repeated twice. Why I do not know! I've used the lookup wizard to pull the categories in from another table in the db so the should be no spelling mistakes etc.

Secondly if you click on a category it only pulls out text before the loop in my script. Click refresh, nothing happens, click the category again and the info I want pulled out is displayed, weird!

Here's the script


[VBS] <%
Dim cn
Dim rs
Dim strconn
Dim strSQL

set cn = CreateObject("ADODB.Connection")
strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/dfppubs.mdb")
cn.open strconn

Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM PUBLICATIONS ORDER by Class"
rs.open strSQL, strconn

tempClass = "null"
Do until rs.eof
If rs("Class")<>tempClass then
Response.Write "<tr><td><a href=publicationslist.asp?Category=" & Server.URLEncode(rs("Class")) & " class=submenu>" & rs("Class") & "</a></td></tr>" & vbCrLf
Response.Write "<tr><td width=180 bgcolor=#999966><img src=../pics/space.gif width=180 height=1 border=0></td></tr>"
tempClass = rs("Class")
headerCount = headerCount + 1
End If
rs.movenext
Loop
rs.close
cn.close
%>[/VBS]

[DaveMaxwell]

Is this the whole code or just the part that seems to be having the problem? I don't see anything wrong with what you are doing so I'm guessing there is more to this code that what you're showing....

[Daz]

This is the VB script for the link I posted, all of it.

[JimBolla]

that class is in the publications table more than once. try SELECT DISTINCT * FROM PUBLICATIONS ORDER by Class

[DaveMaxwell]

Originally posted by crowdozer
that class is in the publications table more than once. try SELECT DISTINCT * FROM PUBLICATIONS ORDER by Class

That still shouldn't provide it out of order. The order by clause should at least order it correctly. There is also code there already which handles checking the previous class.

[JimBolla]

hmmm. true. the ordering problem has stumped me.

[DaveMaxwell]

Originally posted by Daz
This is the VB script for the link I posted, all of it.

OK then, can we see the code for publicationslist.asp and see if we can't figure out the rest of the problem? I still don't understand the bit with the order being ignored somehow...

What platform are you running on? WinNT/2K/Chilisoft?

[Daz]

Dave the site is on a Windows NT server. Here's the VBscript from publicationslist.asp

[VBS]<%
Dim cn
Dim rs
Dim strconn
Dim strSQL

set cn = CreateObject("ADODB.Connection")
strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db/dfppubs.mdb")
cn.open strconn

Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM PUBLICATIONS"
rs.open strSQL, strconn

Do until rs.eof
If rs("Class")<>tempClass then
Response.Write "<tr><td><a href=publicationslist.asp?Class=" & Server.URLEncode(rs("Class")) & " class=submenu>" & rs("Class") & "</a></td></tr>" & vbCrLf
Response.Write "<tr><td width=180 bgcolor=#999966><img src=pics/space.gif width=180 height=1 border=0></td></tr>"
tempClass = rs("Class")
headerCount = headerCount + 1
End If
rs.movenext
Loop
rs.close
%>[/VBS]

[DaveMaxwell]

Huh? OK, now I'm really confused. That looks almost EXACTLY like the code from the publications.asp file you posted in the first post of the topic (minus the order by).

How are you showing the information for the publications? There isn't any code that I see that will give you the results that your page is doing.

I'm cornfused.