Wrox Programmer Forums
|
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 9th, 2004, 03:50 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default Query and loop records... HELP!

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
 
Old January 9th, 2004, 04:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Can you please post your code?

 
Old January 9th, 2004, 04:16 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default

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
%>
 
Old January 9th, 2004, 04:50 PM
Authorized User
 
Join Date: Jun 2003
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to hcweb
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop through records on Continuous Form paulcbyrum Access 1 May 13th, 2008 06:28 AM
ASP dynamic SQL query with for loop? hman Classic ASP Databases 3 December 13th, 2006 08:56 PM
Loop through records- stop if certain value met SoC Classic ASP Basics 2 July 28th, 2005 08:22 PM
Query returning No Records englandera Classic ASP Databases 2 November 22nd, 2004 10:54 AM
update records by the loop mateenmohd SQL Server 2000 2 July 16th, 2003 06:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.