Solved

Classic ASP - How to count total number of records from one query related to another query.

Posted on 2006-07-06
14
740 Views
Last Modified: 2012-06-27
Hello.  I am using Classic ASP with MS Access.  I'm not overly clear on how to explain my question, but I'll give it a shot.

I have a page that lists all the products we support in a tree-menu fashion, as below:
====================================================
CATEGORY_A
Product 1
Product 2
Product 3

CATEGORY_B
Product 1
Product 2
Product 3
====================================================

There is a Master query for the CATEGORY, and a Detail query for the PRODUCTS.
We have labs associated with various product numbers.  I have a separate table for labs with a foreign key related to the PRODUCTS primary key (the Product number).

I would like to be able to place a number is parentheses to the right of each product number, indicating the total number of labs for that product. [i.e. Product 1 (2 labs)].

I am not sure how to go about this.  I'll post the code, if anyone needs it.

Your help is appreciated,
Shane
0
Comment
Question by:lshane
  • 5
  • 5
  • 2
  • +2
14 Comments
 
LVL 6

Expert Comment

by:inviser
ID: 17052875
Yes, code would help.
0
 
LVL 13

Expert Comment

by:jrram
ID: 17052944
SELECT a.product_name, COUNT(b.product_id) AS noLabs
FROM PRODUCTS a, LABS b
WHERE a.product_id = b.product_id
UNION
SELECT a.product_name, 0 AS noLabs
FROM PRODUCT a
WHERE NOT EXISTS (SELECT 1 FROM LABS b WHERE a.product_id = b.product_id);
0
 

Author Comment

by:lshane
ID: 17052969
Great.  Thank you.  I apologize for the Dreamweaver mess:

==========================================================================
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/connPMDData_DSN.asp" -->
<%
Dim master1category_test
Dim master1category_test_numRows

Set master1category_test = Server.CreateObject("ADODB.Recordset")
master1category_test.ActiveConnection = MM_connPMDData_DSN_STRING
master1category_test.Source = "SELECT * FROM category_test ORDER BY catName"
master1category_test.CursorType = 0
master1category_test.CursorLocation = 2
master1category_test.LockType = 1
master1category_test.Open()

master1category_test_numRows = 0
%>
<%
Dim detail2prod_test__MMColParam
detail2prod_test__MMColParam = "12345"
If (Request("MM_EmptyValue") <> "") Then
  detail2prod_test__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim detail2prod_test
Dim detail2prod_test_numRows

Set detail2prod_test = Server.CreateObject("ADODB.Recordset")
detail2prod_test.ActiveConnection = MM_connPMDData_DSN_STRING
detail2prod_test.Source = "SELECT *  FROM prod_test  WHERE catID_prod = " + Replace(detail2prod_test__MMColParam, "'", "''") + "  ORDER BY prodNumb ASC"
detail2prod_test.CursorType = 0
detail2prod_test.CursorLocation = 2
detail2prod_test.LockType = 1
detail2prod_test.Open()

detail2prod_test_numRows = 0
%>
<%
Dim nested_sw
  nested_sw = false
%>
<%
Dim MM_paramName
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

Dim MM_keepNone
Dim MM_keepURL
Dim MM_keepForm
Dim MM_keepBoth

Dim MM_removeList
Dim MM_item
Dim MM_nextItem

' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then
  MM_removeList = MM_removeList & "&" & MM_paramName & "="
End If

MM_keepURL=""
MM_keepForm=""
MM_keepBoth=""
MM_keepNone=""

' add the URL parameters to the MM_keepURL string
For Each MM_item In Request.QueryString
  MM_nextItem = "&" & MM_item & "="
  If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
    MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
  End If
Next

' add the Form variables to the MM_keepForm string
For Each MM_item In Request.Form
  MM_nextItem = "&" & MM_item & "="
  If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
    MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
  End If
Next

' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
If (MM_keepBoth <> "") Then
  MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
End If
If (MM_keepURL <> "")  Then
  MM_keepURL  = Right(MM_keepURL, Len(MM_keepURL) - 1)
End If
If (MM_keepForm <> "") Then
  MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
End If

' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
  If (firstItem <> "") Then
    MM_joinChar = "&"
  Else
    MM_joinChar = ""
  End If
End Function
%>
<%
Dim Repeat_master1category_test__numRows
Dim Repeat_master1category_test__index

Repeat_master1category_test__numRows = -1
Repeat_master1category_test__index = 0
master1category_test_numRows = master1category_test_numRows + Repeat_master1category_test__numRows
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML xmlns:xhtml = "http://www.w3.org/1999/xhtml"><HEAD><TITLE>Preflite Model Number Selection</TITLE>
<META http-equiv=Content-Type content="text/html; charset=utf-8">
<META content="Your Keywords - Used for search robots" name=keyword><LINK
href="Preflite_Site_Files/convert_style.htm" type=text/css
rel=stylesheet><LINK href="Preflite_Site_Files/custom_styles.css"
type=text/css rel=stylesheet>
<SCRIPT language=javascript
src="Preflite_Site_Files/common_scripts.js">//</SCRIPT>

<SCRIPT language=javascript
src="Preflite_Site_Files/convert_lib.htm">//</SCRIPT>

<SCRIPT src="Preflite_Site_Files/site_data.js"
type=text/javascript>//</SCRIPT>

<SCRIPT type=text/javascript>
    _page.hideLeftNavigation = true;
    </SCRIPT>

<META content="Microsoft FrontPage 4.0" name=GENERATOR>
<link href="Preflite_Site_Files/my_styles.css" rel="stylesheet" type="text/css">
</HEAD>
<BODY>

<SCRIPT type=text/javascript>_page.startPage()</SCRIPT>

<TABLE id=p-ca cellSpacing=0 cellPadding=0 border=0>
  <TBODY>
  <TR id=p-carow1>
    <TD width=220><IMG height=1 alt="" src="Preflite_Site_Files/t.gif"
      width=220></TD>
    <TD width=17><IMG height=1 alt="" src="Preflite_Site_Files/t.gif"
      width=17></TD>
    <TD align="center" class="allprodhdrmain">Select A Model Number</TD>
    <TD width=17><IMG height=1 alt="" src="Preflite_Site_Files/t.gif"
      width=17></TD>
    <TD width=220><IMG height=1 alt="" src="Preflite_Site_Files/t.gif"
      width=220></TD></TR>
  <TR id=p-carow2 vAlign=top>
    <TD id=p-column1>
      <TABLE class=p-hl cellSpacing=0 cellPadding=0 border=0>
        <TBODY>
        <TR>
          <TD class=p-top>categories</TD>
        </TR>
        <TR>
          <TD class=p-content-000000 id=bgc-phblue_light>
            <H1> <a href="products.asp">Back To Products</a><br />
            </H1>
           <P class=p-paragraph id=full-width>&nbsp;</P></TD></TR>
        <TR>
          <TD class=p-bottom-d9e6ff></TD></TR></TBODY></TABLE>
      <TABLE class=p-hl cellSpacing=0 cellPadding=0 border=0>
        <TBODY>
        <TR>
          <TD class=p-top>About preflite</TD></TR>
        <TR>
          <TD class=p-content-000000 id=bgc-phblue_light>
            <H1></H1>
            <P class=p-paragraph id=full-width>Preflite is a training resource                  
            designed specifically with new-hires in mind.&nbsp; The site contains                  
            four main areas: Products, Processes, Tools, and Flite Plan.&nbsp; All the information is directly related to the Tier 2 PMD                  
            &quot;flight methods&quot;. </P></TD></TR>                  
        <TR>
          <TD class=p-bottom-d9e6ff></TD></TR></TBODY></TABLE></TD>
    <TD></TD>
    <TD class=article id=p-column2 width=496>
      <DIV class=p-paragraph style="width: 475; height: 35">
      <table width="450" border="0" align="center" cellpadding="0" cellspacing="0" class="tblbrdr1">
        <tr>
          <td valign="top"><table width="500" border=0 cellpadding="0" cellspacing="0">
          <%
While ((Repeat_master1category_test__numRows <> 0) AND (NOT master1category_test.EOF))
%>
          <%  If (master1category_test.Fields.Item("catID_cat").Value) <> "" Then %><tr>
                <td><br><div class="subcatseparatorbkg"><b>
                    <%=(master1category_test.Fields.Item("catName").Value)%></b></div></td>
          </tr><%END IF%>
            <%
  if len(nested_detail2prod_test)<=0 then
    Dim nested_detail2prod_test
    nested_detail2prod_test = detail2prod_test.Source
  end if
  nested_sw = false
  detail2prod_test.Close()
  detail2prod_test.Source = Replace(nested_detail2prod_test, "12345", master1category_test.Fields.Item("catID").Value)
  detail2prod_test.Open()
  While (NOT detail2prod_test.EOF)
    'Nested repeat
%>
            <tr>
                <td>&nbsp;&nbsp;&nbsp;&nbsp; <a href="product_details.asp?<%= MM_keepURL & MM_joinChar(MM_keepURL) & "prodNumb=" & detail2prod_test.Fields.Item("prodNumb").Value %>"><%=(detail2prod_test.Fields.Item("prodNumb").Value)%></a></td>
            </tr>
            <%
  ' Nested move next
  detail2prod_test.MoveNext()
Wend
%>
            <%
  Repeat_master1category_test__index=Repeat_master1category_test__index+1
  Repeat_master1category_test__numRows=Repeat_master1category_test__numRows-1
  master1category_test.MoveNext()
Wend
%>

          </table></td>
        </tr>
</table>
      <P class=p-paragraph id=full-width>&nbsp;</P>
      </DIV>        
    </TD>
    <TD></TD>
    <TD id=p-column3>
      <SCRIPT language=javascript>
function submitLoginForm(){  
  var obj=document.loginform;
  var bolSubmit=true;
  if(obj.username.value==""){
    alert('Please supply a username.');
    obj.username.focus();
    bolSubmit=false;
  }
  if(obj.password.value=="" && bolSubmit){
    alert('Please supply a password.');
    obj.username.focus();
    bolSubmit=false;
  }  
  if(bolSubmit){    
    try {
    if (obj.remember.checked)
    {
      writecookie("username", obj.username.value);
      writecookie("password", obj.password.value);
    }
    } catch(e){}
    document.loginform.action="/scripts/login/validate_user.asp?url=/my_page/index.asp&id=LOGIN&secured=true";
    document.loginform.submit();
  }
  return false;
}

function showForgotPassword()
{
  document.forms.forgotPassword.username.value = document.forms.loginform.username.value;
  document.getElementById("loginform").style.display = "none";
  document.getElementById("forgotPassword").style.display = "block";
}
function hideForgotPassword()
{
  document.getElementById("loginform").style.display = "block";
  document.getElementById("forgotPassword").style.display = "none";
}

</SCRIPT>

      <TABLE class=p-tool cellSpacing=0 cellPadding=0 border=0>
        <TBODY>
        <TR>
          <TD class=p-top-99bdff>Administrative Login</TD></TR>        
        <TR>
          <TD class=p-content>
            <FORM id=loginform style="MARGIN: 0px" name=loginform
            onsubmit="return submitLoginForm()"
            action=/scripts/login/validate_user.asp method=post>
            <TABLE cellSpacing=0 cellPadding=1>
              <TBODY>
              <TR vAlign=top>
                <TD>Username (your e-mail address):<BR><INPUT id=username
                  onblur="if ((this.value.length > 0) &amp;&amp; (this.value.indexOf('@') == -1)) this.value += '@philips.com'"
                  style="WIDTH: 190px" name=username> </TD></TR>
              <TR vAlign=top>
                <TD>Password:<BR><INPUT id=password style="WIDTH: 190px"
                  type=password name=password> </TD></TR>
              <TR vAlign=top>
                <TD> <BR><INPUT type=image
                  src="Preflite_Site_Files/but_submit.gif" align=left
                  border=0 name="I1"> </TD></TR>
              <TR vAlign=top>
                <TD>&nbsp; </TD>
                <TD></TD></TR></TBODY></TABLE></FORM>
</TD></TR>
        <TR>
          <TD class=p-bottom></TD></TR></TBODY></TABLE>
      <TABLE class=p-tool cellSpacing=0 cellPadding=0 border=0>
        <TBODY>
        <TR>
          <TD class=p-top-d9d9d9>Quick Links</TD></TR>                  
        <TR>
          <TD class=p-content>
            <UL>
              <LI><A href="http://www.philips.com/">Philips Internet
Homepage</A>
              <LI><A href="http://pww.philips.com/">Philips Intranet
Homepage</A>
              <LI><A
              href="http://pww.intouch.philips.com/about_philips/people/index.asp">Yellow  
              Pages</A>
            </UL></TD></TR>
        <TR>
          <TD class=p-bottom></TD></TR></TBODY></TABLE>
      <TABLE class=p-tool cellSpacing=0 cellPadding=0 border=0>
        <TBODY>
        <TR>
          <TD class=p-top-000000>Quick Downloads</TD></TR>
        <TR>
          <TD class=p-content>
            <TABLE cellSpacing=0 cellPadding=0 border=0>
              <TBODY>
              <TR>
                <TD width=26> <IMG alt=""
                  src="Preflite_Site_Files/adobe.gif"> </TD>
                <TD><A href="docs/PMD_Product_Training List.xls" target="_blank">Product Completion List </A></TD></TR>
              <TR>
                <TD width=26> <IMG alt=""
                  src="Preflite_Site_Files/adobe.gif"> </TD>
                <TD><A href="docs/Orientation_Checklist.XLS" target="_blank">Orientation List</A> </TD></TR>
              <TR>
                <TD width=26><IMG alt=""
                  src="Preflite_Site_Files/adobe.gif"> </TD>
                <TD><A href="docs/Product_Champion List.xls" target="_blank">Product Champion List</A> </TD></TR></TBODY></TABLE></TD></TR>
        <TR>
<TD class=p-bottom></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<SCRIPT type=text/javascript>_page.endPage()</SCRIPT>
<A class=p-robots
href="http://pww.intouch.philips.com/site_data"></A></BODY></HTML>
<%
master1category_test.Close()
Set master1category_test = Nothing
%>
<%
detail2prod_test.Close()
Set detail2prod_test = Nothing
%>
==========================================================================
0
 
LVL 1

Expert Comment

by:bschwarting
ID: 17053824
so do you just want code to count up the number of entries in the database and write it to an HTML page?
0
 

Author Comment

by:lshane
ID: 17053981
Well, I actually just want it to Total the number of labs associated with each product number.

The tree loops through all categories and displays all products associated with that category, and then moves on to the next category, displaying all those associated products, etc.  I would also like to somehow loop through the LABS table and Total the number of labs associated with each product number.

Example:
=================================================
CATEGORY_A
Product 1 (2 labs)
Product 2 (5 labs)
Product 3 (0 labs)

CATEGORY_B
Product 1 (3 labs)
Product 2 (0 labs)
Product 3 (7 labs)
=================================================

The number of labs in parentheses beside each product would be a Total of all labs associated with that particular product number in the db.

Here's my db table config:

CATEGORY_TEST {catID, catID_cat, catName}

PROD_TEST {prodNumb, catID_prod, prodName, whoUses, howUsed, imagePath, svcmnlPath, ifuPath, tdsPath, subcatID, pcID}

LABS {labID, labTitle, labDesc, labNeeds, pgNum, labProdID, labType, manualType, attachPath}


0
 
LVL 1

Expert Comment

by:bschwarting
ID: 17054037
see if this helps:

<%

Dim conn
Set conn = Server.CreateObject("ADODB.recordset")
conn.open "SELECT * FROM TABLENAME;", "DSN=blank"

conn.MoveFirst
Dim personalcounter
personalcounter = 0
do while not conn.eof
      personalcounter = personalcounter + 1
      
IF conn.EOF Then
      Response.Redirect ("../nodata.html")
end if      

conn.movenext
Loop

Response.Write  "Product 1 = " & "<b><font color=red>" & personalcounter & "</b></font>"
Set conn = Nothing

%>
0
 

Author Comment

by:lshane
ID: 17054146
Thanks, bschwarting.  I'll give that a try in the next little while.

I wanted to give one more example.  I suppose it's like what is on this site (EE).  If a user (myself) has open questions, it has our name and then the number of "Open" questions in parentheses to the right of our names.

That's pretty much what I'm wanting to do here except with associated Labs to Product Numbers.  Is your suggestion something along those lines?

(Thanks so much for your help).
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Accepted Solution

by:
bschwarting earned 500 total points
ID: 17054259
yep, that should work then.  

it will query the database, run through the loop code (until EOF is reached, while adding 1 to the personal counter each time)

just make sure it's properly placed, or your personalcounter will stay at zero.

#####################################
conn.MoveFirst
Dim personalcounter
personalcounter = 0
do while not conn.eof
     personalcounter = personalcounter + 1

YOUR CODE HERE --- example ---ID = conn("ID")
YOUR CODE HERE --- example ---ID = conn("ID")
YOUR CODE HERE --- example ---ID = conn("ID")
YOUR CODE HERE --- example ---ID = conn("ID")
     
IF conn.EOF Then
     Response.Redirect ("../nodata.html")
end if    

conn.movenext
Loop
#####################################

you might have to rename the personalcounter to personalcounter1, personalcounter2, etc... for each instance, depending on your code setup to get mulitple counters for each product.
0
 

Author Comment

by:lshane
ID: 17054294
Cool.  I'll have to try it a little later this evening (5:24pm EST currently).  I'll let you know what I find.

Thank you,
Shane
0
 
LVL 28

Expert Comment

by:sybe
ID: 17057379
Better use the .Filter property of the recordset.

This is just a basic example of how you can use it to count a selection of the current recordset that meets certain conditions.

<%
sSQL = "SELECT * FROM tablename WHERE condition"
Set oRS = Server.CreateObject(ADODB.RecordSet")
oRS.Open sSQL, connection, 1, 1
oRS.Filter = "field1 = 'value1' AND field2 = 'value2'"
iCount = oRS.RecordCount
Response.write "(" & iCount & " records meet the specified )"

oRS.Filter = ""

%>
0
 
LVL 28

Expert Comment

by:sybe
ID: 17057385
0
 
LVL 1

Expert Comment

by:bschwarting
ID: 17063568
any luck on getting this to work?
0
 

Author Comment

by:lshane
ID: 17063741
Hi, bschwarting.  I apologize, but I have not had the opportunity to try this, yet.  I'll need another couple of days.  To verify, though, would the proper placement be in the "<Head>" section of the page?
0
 
LVL 1

Expert Comment

by:bschwarting
ID: 17063788
no, it goes anywhere in the asp code portion <% %>
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now