|
Subject:
|
Query and loop records... HELP!
|
|
Posted By:
|
hcweb
|
Post Date:
|
1/9/2004 2:50:34 PM
|
I'm looking for some feedback and advice from anyone willing to help with the problem below.
I've been handed a phone directory database for use on our Intranet. We need to be able to add, edit, and print directly from the Intranet Administration areas. The problem I believe is going to be with the database design.
Currently, it's set up like so:
Column names:
department | subdepartment | last | first | phone...
With entries like so:
sheriff patrol doe john 999-9999
sheriff booking smith james 999-9999
sheriff records jones davey 999-9999
On viewable pages, the plan is to have a user select a department from a drop down menu and on a foloowing page, display the first subdepartment (if one's present), then display all users within that subdepartment, then move on to the next subdepartment and so on..
I.E.:
The "Sheriff" employees can be found at:
--Patrol
John Doe - 999-9999
--Booking
James Smith - 999-9999
--Records
Davey Jones - 999-9999
If I loop through the records, it returns the above layout 3 seperate times. I know why it's doing this, but not how to combat the problem. Should I have the database reformetted to better accomodate my needs, or is there a way to single out the data I need as shown above. If the databse could be better setup, I'm completely open to anyone's suggestions!
Chris
|
|
Reply By:
|
stu9820
|
Reply Date:
|
1/9/2004 3:11:53 PM
|
Can you please post your code?
|
|
Reply By:
|
hcweb
|
Reply Date:
|
1/9/2004 3:16:51 PM
|
This the current code that I'm testing with.
<%
Dim objCommand, objRS, objCommand1, objRS1, strDept
strDept = "Data Processing"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText = "SELECT subdepartment FROM directory WHERE department = '" & strDept & "'"
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
If objRS.EOF Then
Response.Write "<table width=600 border=0><tr>" & _
"<td class=text align=center><b>Your search for " & strDept & " produced" & _
" no results</b></td></tr>" & _
"<tr><td bgcolor=cccccc></td></tr>" & _
"<tr><td bgcolor=eeeeee class=text align=center><a href='index.asp'>Search Again</a> | " & _
"<a href='index.asp'>Return To Main Menu</a></td></tr>" & _
"</table>"
Else
Response.Write "<table width=450 align=center border=0>"
Do While Not objRS.EOF
Response.Write "<tr><td align=left class=text bgcolor=cccccc>" & UCase(objRS("subdepartment")) & "</td></tr>"
Set objCommand1 = Server.CreateObject("ADODB.Command")
objCommand1.ActiveConnection = strConnect
objCommand1.CommandText = "SELECT * FROM directory WHERE subdepartment = '" & objRS("subdepartment") & "' ORDER BY last"
objCommand1.CommandType = adCmdText
Set objRS1 = objCommand1.Execute
Set objCommand1 = Nothing
While Not objRS1.EOF
Response.Write "<tr><td align=left class=text>" & objRS1("first") & " " & objRS1("last") & "</td></tr>"
objRS1.MoveNext
WEND
objRS.MoveNext
Loop
Response.Write "<tr valign=top><td bgcolor=333333></td></tr>" & _
"<tr><td class=text align=center bgcolor=eeeeee><a href='departsearch.asp'>Search Again</a>" & _
" | <a href='index.asp'>Return To Directory Home</a></td></tr>" & _
"</table>"
End If
objRS1.Close
Set objRS1 = Nothing
objRS.Close
Set objRS = Nothing
%>
|
|
Reply By:
|
hcweb
|
Reply Date:
|
1/9/2004 3:50:28 PM
|
I believe I have solved my own problem. I added another table to the database as a department descriptor table that contains the department name with it's subdepartments. In the very first query, I specify the new table and my results are only one set of subdepartments. The inner loop fires off correctly and displays my multiple users.
Here's the new test code.
<%
Dim objCommand, objRS, objCommand1, objRS1, strDept
strDept = "Data Processing"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConnect
objCommand.CommandText = "SELECT subdepartment FROM fddepartments1 WHERE department = '" & strDept & "' ORDER BY subdepartment"
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
If objRS.EOF Then
Response.Write "<table width=600 border=0><tr>" & _
"<td class=text align=center><b>Your search for " & strDept & " produced" & _
" no results</b></td></tr>" & _
"<tr><td bgcolor=cccccc></td></tr>" & _
"<tr><td bgcolor=eeeeee class=text align=center><a href='index.asp'>Search Again</a> | " & _
"<a href='index.asp'>Return To Main Menu</a></td></tr>" & _
"</table>"
Else
Response.Write "<table width=450 align=center border=0>"
Do While Not objRS.EOF
Response.Write "<tr><td align=left class=text bgcolor=cccccc>" & UCase(objRS("subdepartment")) & "</td></tr>"
Set objCommand1 = Server.CreateObject("ADODB.Command")
objCommand1.ActiveConnection = strConnect
objCommand1.CommandText = "SELECT * FROM directory WHERE subdepartment = '" & objRS("subdepartment") & "'"
objCommand1.CommandType = adCmdText
Set objRS1 = objCommand1.Execute
Set objCommand1 = Nothing
While Not objRS1.EOF
Response.Write "<tr><td align=left class=text>" & objRS1("first") & " " & objRS1("last") & "</td></tr>"
objRS1.MoveNext
WEND
objRS.MoveNext
Loop
Response.Write "<tr valign=top><td bgcolor=333333></td></tr>" & _
"<tr><td class=text align=center bgcolor=eeeeee><a href='departsearch.asp'>Search Again</a>" & _
" | <a href='index.asp'>Return To Directory Home</a></td></tr>" & _
"</table>"
End If
objRS1.Close
Set objRS1 = Nothing
objRS.Close
Set objRS = Nothing
%>
|
