Help with program flow please.

Help with program flow please.

Post by Chuck Roc » Sat, 13 Jun 1998 04:00:00

I am editing pages created from Site Server 3, I'm also using SQL Server 6.5

I have a department page that I want to give the total products per
department. The problem I'm having is the page runs a query on a table to
list all of the departments based on product id. I want to execute two more
queries for each department in the loop. That's because the information I
need to display is in the products table, but to get the row from the
product table I need the sku number of the product. The sku number is in
another table associated with the product id's.

The original code is:

Function showItems(dept_id)
        sqlText =
        Set rsSubDepts = MSCS.Execute (sqlText, nSubDepts, adCmdText)

        REM -- each level is either a list of subdepts or products
        if Not rsSubDepts.EOF then
            REM -- show subdepts
&nbsp;<p><font SIZE="2">Select a Department:</font> <%
            %> <ul>
            set dept_idField = rsSubDepts("dept_id")
            set dept_nameField = rsSubDepts("dept_name")
            do while Not rsSubDepts.EOF
        HREF="<%= baseURL("dept.asp") & mscsPage.URLShopperArgs("dept_id" ,
dept_idField.value) %>"><%= dept_nameField.value %></a>
            %>      </li>

the sub_departments query in the the first line queries the dept table. This
gives an id, name, and description.

The loop displays the results in a bulleted list, for each row in the query.
For each dept I need to query the dept_prod table to get the multiple sku
#'s that have the same dept_id value. In that query I need the amount of
sku's with that dept_id value(I assume I would use the COUNT function in the
SQL statement). The kicker is I also need the average price of all the
products in that department too. That would mean an array(I guess) to hold
the amounts, and the number of sku's so that I can calculate an average.

I don't know how to query more than one table at a time, and I would really
like to use the avg function in a SQL statement to calculate that average
cost for me.

This wouldn't be that hard if the product table kept the dept_id values for
each product!!!

As you can probably tell I don't know much of what I'm doing.
Any help would be appreciated.


1. Need help with IDC program flow control syntax

I have a database of documents (on MS SQL 6.5), and I need to implement
an HTML form which allows the user to select a document according
to project code. For this purpose a drop down menu to choose the
document, and textbox for the project code is used (see HTML below).

<input type="text" size="5" maxlength="5" name="project_code">
           <select name="formtype" size="1">
                <option selected value="FM160">FM160</option>
                <option value="FM010">FM010</option>
                <option value="FM020">FM020</option>
                <option value="FM030">FM030</option>
                <option value="FM170">FM170</option>
        This will then execute an IDC file. However, each document
requires a separate query to extract it from the database, and a
separate HTX file to display the results.
        My initial attempt for the idc file looks like this:
+if %formtype% = FM010
+  {query for FM010 goes here}
+if %formtype% = FM160
+ {query for FM160 goes here}
        and so on.....
        Alas, this only results in error messages to the effect that FM010 etc
are invalid column names. What would be the correct syntax? Also, what
flow logic should I use in the htx file to display the document in its
format? Or is there some way of choosing between different htx templates
the idc file?
        All advice is desperately welcomed :)

2. Q:RowChange in a DBGrid

3. please help me understand how to build a database - Flow.txt (0/1)

4. Complex stored procedure subquery in a View?

5. Program flow control problem

6. How do I combine RTF/HTML with xp_sendmail?

7. Program Flow and command objects

8. FOXPRO2 - Programming question (Menu & Flow)

9. Problem updating Table Fields (Program Flow)

10. Program Flow Question??????

11. How to continue program flow with MESSAGEBOX() !