This is the weirdest problem I have come across in a long time. The following code works if I use a dsn-less connection but if I use a dsn connection it doesn't. The page doesn't error it just doesn't display all the information. I have even put in a few lines at the bottom to make sure the recordset it is returning is the same and it is.
The results should look like this (using a DSN-less connection)
IT - BWBNet > Deeds
> HR
- Hardware knowhow > Memory
> Telephones
- Minutes
- Software knowhow > Adobe Reader
> Dictation Console
> Digital Dictation
> Modular Messaging
> Outlook
> VisualFiles
> Windows
> Winzip
> Workshare
But with a DSN they look like this (ie it is not displaying TBL("DocsCat")
IT - > Deeds
> HR
> Memory
> Telephones
> Adobe Reader
> Dictation Console
> Digital Dictation
> Modular Messaging
> Outlook
> VisualFiles
> Windows
> Winzip
> Workshare
My code is as follows:
<%
Set DB = Server.CreateObject("ADODB.Connection")
Set TBL = Server.CreateObject("ADODB.RecordSet")
DB.Mode = adModeReadWrite
'Comment or uncomment the DB.Open depending on which one you want to use
'DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("../../bwbnet/directory/userdb.mdb"))
DB.Open "bwbnet"
Department=Int(Request.QueryString("Department"))
TBL.Open "SELECT DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat FROM (DocsDept LEFT JOIN DocsCat ON DocsDept.DocsDeptID=DocsCat.DocsDept) LEFT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat WHERE DocsDept.DocsDeptID=" & Department & " ORDER BY DocsDept.DocsDept ASC, Left(DocsCat.DocsCat, 250) ASC, DocsSubCat.DocsSubCat ASC", DB
%>
<body link="blue" alink="blue" vlink="blue">
<table cellspacing="0" cellpadding="5" width="100%">
<%
Do While Not TBL.EOF
%>
<tr>
<td>
<font face="verdana" size="2">
<%
If TBL("DocsDept")<>OldDocsDept Then
Response.Write("<a href='docslist.asp?Department=") & TBL("DocsDeptID") & "'>"
Response.Write(TBL("DocsDept"))
Response.Write("</a>")
End If
%>
</td>
<td>
<font face="verdana" size="2">
<%
If TBL("DocsCat")<>OldDocsCat Then
Response.Write("<b> - </b>")
Response.Write("<a href='docslist.asp?Department=") & TBL("DocsDeptID") & "&DocsCat=" & TBL("DocsCatID") & "'>"
Response.Write(TBL("DocsCat"))
Response.Write("</a>")
End If
%>
</td>
<td>
<font face="verdana" size="2">
<%
If TBL("DocsSubCat")<>"" Then Response.Write(" > ")
Response.Write("<a href='docslist.asp?Department=") & TBL("DocsDeptID") & "&DocsCat=" & TBL("DocsCatID") & "&DocsSubCat=" & TBL("DocsSubCatID") & "'>"
Response.Write(TBL("DocsSubCat"))
%>
</td>
<%
OldDocsDept=TBL("DocsDept")
OldDocsCat=TBL("DocsCat")
OldDocsSubCat=TBL("DocsSubCat")
TBL.MoveNext
Loop
TBL.Close
%>
</table>
<%
'Execute the same query and display recordset without formatting
TBL.Open "SELECT DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat FROM (DocsDept LEFT JOIN DocsCat ON DocsDept.DocsDeptID=DocsCat.DocsDept) LEFT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat WHERE DocsDept.DocsDeptID=" & Department & " ORDER BY DocsDept.DocsDept ASC, Left(DocsCat.DocsCat, 250) ASC, DocsSubCat.DocsSubCat ASC", DB
Do While Not TBL.EOF
Response.Write(TBL("DocsDept")) & "|" & TBL("DocsCat") & "|" & TBL("DocsSubCat") & "<br>"
TBL.MoveNext
Loop
TBL.Close
Set TBL=Nothing
Set DB=Nothing
%>
The results should look like this (using a DSN-less connection)
IT - BWBNet > Deeds
> HR
- Hardware knowhow > Memory
> Telephones
- Minutes
- Software knowhow > Adobe Reader
> Dictation Console
> Digital Dictation
> Modular Messaging
> Outlook
> VisualFiles
> Windows
> Winzip
> Workshare
But with a DSN they look like this (ie it is not displaying TBL("DocsCat")
IT - > Deeds
> HR
> Memory
> Telephones
> Adobe Reader
> Dictation Console
> Digital Dictation
> Modular Messaging
> Outlook
> VisualFiles
> Windows
> Winzip
> Workshare
My code is as follows:
<%
Set DB = Server.CreateObject("ADODB.Connection")
Set TBL = Server.CreateObject("ADODB.RecordSet")
DB.Mode = adModeReadWrite
'Comment or uncomment the DB.Open depending on which one you want to use
'DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath("../../bwbnet/directory/userdb.mdb"))
DB.Open "bwbnet"
Department=Int(Request.QueryString("Department"))
TBL.Open "SELECT DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat FROM (DocsDept LEFT JOIN DocsCat ON DocsDept.DocsDeptID=DocsCat.DocsDept) LEFT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat WHERE DocsDept.DocsDeptID=" & Department & " ORDER BY DocsDept.DocsDept ASC, Left(DocsCat.DocsCat, 250) ASC, DocsSubCat.DocsSubCat ASC", DB
%>
<body link="blue" alink="blue" vlink="blue">
<table cellspacing="0" cellpadding="5" width="100%">
<%
Do While Not TBL.EOF
%>
<tr>
<td>
<font face="verdana" size="2">
<%
If TBL("DocsDept")<>OldDocsDept Then
Response.Write("<a href='docslist.asp?Department=") & TBL("DocsDeptID") & "'>"
Response.Write(TBL("DocsDept"))
Response.Write("</a>")
End If
%>
</td>
<td>
<font face="verdana" size="2">
<%
If TBL("DocsCat")<>OldDocsCat Then
Response.Write("<b> - </b>")
Response.Write("<a href='docslist.asp?Department=") & TBL("DocsDeptID") & "&DocsCat=" & TBL("DocsCatID") & "'>"
Response.Write(TBL("DocsCat"))
Response.Write("</a>")
End If
%>
</td>
<td>
<font face="verdana" size="2">
<%
If TBL("DocsSubCat")<>"" Then Response.Write(" > ")
Response.Write("<a href='docslist.asp?Department=") & TBL("DocsDeptID") & "&DocsCat=" & TBL("DocsCatID") & "&DocsSubCat=" & TBL("DocsSubCatID") & "'>"
Response.Write(TBL("DocsSubCat"))
%>
</td>
<%
OldDocsDept=TBL("DocsDept")
OldDocsCat=TBL("DocsCat")
OldDocsSubCat=TBL("DocsSubCat")
TBL.MoveNext
Loop
TBL.Close
%>
</table>
<%
'Execute the same query and display recordset without formatting
TBL.Open "SELECT DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat FROM (DocsDept LEFT JOIN DocsCat ON DocsDept.DocsDeptID=DocsCat.DocsDept) LEFT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat WHERE DocsDept.DocsDeptID=" & Department & " ORDER BY DocsDept.DocsDept ASC, Left(DocsCat.DocsCat, 250) ASC, DocsSubCat.DocsSubCat ASC", DB
Do While Not TBL.EOF
Response.Write(TBL("DocsDept")) & "|" & TBL("DocsCat") & "|" & TBL("DocsSubCat") & "<br>"
TBL.MoveNext
Loop
TBL.Close
Set TBL=Nothing
Set DB=Nothing
%>
