ASP Multiplication on data output

Question

davidjennings 0 Junior Poster in Training

11 Years Ago

Hi All, I am trying to the following:

The code is written in ASP, the output is from a database. I am trying to multiply the QTY and RATE#2 and display total with each order in the returned data. This relates to the last td's in the output.

I also want to data to 2 decimal places(currancy)

Anything else required please let me know

Thanks in advance

David

    <tr>
                        <td height="30"><a href="/dashboard-fullscreen/individual/contracts/view.asp?Allow=f875eba085941cc78509bd3482dc0294&amp;Contract=<%= rowCrossHire("CONTNO") %>" target="_blank" title="View <%= rowCrossHire("CONTNO") %>"><%= rowCrossHire("CONTNO") %></a></td>
                        <td height="30"><%= sonNo %></td>
                        <td height="30"><%= rowCrossHire("ACCTNAME") %></td>
                        <td height="30"><%= rowCrossHire("ITEMNO") %></td>
                        <td height="30"><%= itemDescription %></td>
                        <td align="center" height="30"><%= getItemStatus(rowCrossHire("STATUS")) %></td>
                        <td align="center" height="30"><%= rowCrossHire("NLCODE") %></td>
                        <td align="center" height="30"><%= rowCrossHire("QTY") %></td>
                        <td align="center" height="30"><%= rowCrossHire("QTYRETD") %></td>
                        <td align="center" height="30"><%= rowCrossHire("RATE#1") %></td>
                        **<td align="center" height="30"><%=("RATE#1") * ("QTY") %></td>**

                    </tr>

asp

3 Contributors
10 Replies
310 Views
4 Days Discussion Span
Latest Post 10 Years Ago Latest Post by davidjennings

LastMitch

11 Years Ago

I am trying to multiply the QTY and RATE#2 and display total with each order in the returned data. This relates to the last td's in the output.

@davidjennings

Where your ASP code? You expect someone to write out everything base on the div tags?

JorgeM 958 Problem Solver

11 Years Ago

Yes, SQL does allow you to perform these types of caculations in the query, assuming the data types you have in your table.

So you can easily test this in SQL even without running the query against a table.

select 2 * 5.25 as [total]

This query produces a result of 10.50

so assuming you have the correct data types... something like this should work.

SELECT CONTITEMS.CONTNO, CONTITEMS.ITEMDESC, CONTQTY * CONTRATE#1 AS [TOTAL], ....

Edited 11 Years Ago by JorgeM

JorgeM 958 Problem Solver

10 Years Ago

Instead of this.. <%= TOTAL %>, wouldnt you want to try this.. <%= rowCrossHire("TOTAL") %>, or if you prefer not to perform the mathematical operation in the SQL query on the table, you can create a view in SQL instead and when you query it, just access this field as "TOTAL".

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

davidjennings 0 Junior Poster in Training · Answer 1 · 2013-10-31T09:26:51+00:00

Hi Lastmitch, I did not include the whole code as I wasn't sure if it was all relevant. Let me further explain.

Rows 2 - 11 are output from a database so that is fixed
the output code from row 9 (QTY) needs to be multiplyed by the output code on line 11 (RATE#1).

The output value then needs to be displayed on line 12 for each record.

Sorry I am not that familier with ASP
If you still need the full code let me know
Thanks

D

davidjennings 0 Junior Poster in Training · Answer 2 · 2013-10-31T10:06:01+00:00

Hi LastMitch, I have also tried the 2 formats below :

<td align="center" height="30"><%=rowCrossHire("RATE#1") * rowCrossHire("QTY") %></td>
<td align="center" height="30"><% rateCalc = rowCrossHire("RATE#1") * rowCrossHire("QTY")</td>

Thanks

D

davidjennings 0 Junior Poster in Training · Answer 3 · 2013-11-01T09:43:56+00:00

Hi Lastmitch, I am now able to provide the full code for the page.
Thanks in advance

David

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../includes/functions/contract.asp"-->
<!--#include file="../includes/functions/date.asp"-->
<!--#include file="../includes/functions/insphire.asp"-->
<!--#include file="../includes/functions/integer.asp"-->
<!--#include file="../includes/functions/logaccess.asp"-->
<!--#include file="../includes/functions/text.asp"-->
<!--#include file="../includes/functions/whlonline.asp"-->
<%
    if request("Allow") = "f875eba085941cc78509bd3482dc0294" then
        ' security check passed
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

        <title>View Register</title>

        <style type="text/css" media="all">@import url(/styles/wide.css);</style>
        <style type="text/css" media="all">@import url(/styles/bg-grey.css);</style>
        <style type="text/css" media="all">@import url(/styles/styles-css3.css);</style>
        <style type="text/css" media="all">
            th { border-bottom: 1px solid #999999; border-right: 1px solid #999999; }
            tr.contract th { border-bottom: 1px solid #999999; border-right: 1px solid #999999; font-size: 1px; }
        </style>
    </head>

    <body>

        <div id="container"><!-- container - start -->

            <!--#include file="../includes/layout/header.asp"-->

            <div id="content"><!-- content - start -->

                <h2><a href="http://xxxxxx.co.uk/menu.php" title="Main Menu">Main Menu</a> &gt; <a href="http://xxxxx.co.uk/menu-options-new.php?CategoryID=58" title="Cross Hire">Cross Hire</a> &gt; View Register</h2>
                <p class="mbottom">This section allows you to view the cross hire register.</p>

                <table cellspacing="3" class="mbottom mtop" id="new" width="100%">
                    <tr>
                        <th colspan="11" height="30">Cross Hire Register</th>
                    </tr>
                    <tr>
                        <th height="30" width="9%">Contract No.</th>
                        <th height="30" width="7%">SON No.</th>
                        <th height="30" width="14%">Customer</th>
                        <th height="30" width="7%">Item No.</th>
                        <th height="30" width="15%">Description</th>
                        <th height="30" width="7%">Status</th>
                        <th height="30" width="7%">Nominal Code</th>
                        <th height="30" width="7%">QTY Hired</th>
                        <th height="30" width="7%">QTY Returned</th>
                        <th height="30" width="7%">Unit Cost</th>
                        <th height="30" width="7%">Total Cost</th>

                    </tr>

                    <%
                        crosshireSQL = "SELECT CONTITEMS.CONTNO, CONTITEMS.ITEMDESC, CONTITEMS.ITEMDESC#2, CONTITEMS.RATE#1, CONTITEMS.ITEMDESC#3, CONTITEMS.ITEMNO, CONTITEMS.NLCODE, CONTITEMS.QTY, CONTITEMS.QTYRETD, CONTITEMS.STATUS, CONTRACTS.ACCTNAME FROM CONTITEMS INNER JOIN CONTRACTS ON CONTRACTS.CONTNO = CONTITEMS.CONTNO WHERE CONTITEMS.NLCODE IN ('4001') AND CONTITEMS.STATUS = 1 ORDER BY CONTITEMS.CONTNO ASC, CONTITEMS.ITEMNO ASC"
                        set rowCrossHire = returnRecordSet(crosshireSQL)

                        currentContract = ""

                        if not rowCrossHire.eof then
                            ' cross hires found

                            do while not rowCrossHire.eof
                                ' loop through cross hires

                                if currentContract <> rowCrossHire("CONTNO") then
                                    ' new contract
                    %>
                    <tr class="contract">
                        <th colspan="11">&nbsp;</th>
                    </tr>
                    <%
                                    currentContract = rowCrossHire("CONTNO")
                                end if

                                sonNo = "Pre SONs"

                                sonSQL = "SELECT SONID, SONRevision FROM SONs WHERE SONContractNo LIKE '%" & rowCrossHire("CONTNO") & "%' ORDER BY SONID ASC LIMIT 1"
                                set rowSON = returnRecordSetOnline(sonSQL)

                                if not rowSON.eof then
                                    ' son found
                                    sonRevision = ""

                                    if cint(rowSON("SONRevision")) > 0 then
                                        ' son revision found
                                        sonRevision = chr((cint(rowSON("SONRevision")) + 96))
                                    end if

                                    sonNO = "<a href=""http://lxxxxsu.php?SONID=" & rowSON("SONID") & """ target=""_blank"" title=""View " & rowSON("SONID") & sonRevision & """>" & rowSON("SONID") & sonRevision & "</a>"
                                end if

                                itemDescription = rowCrossHire("ITEMDESC")

                                if trim(rowCrossHire("ITEMDESC#2")) <> "" then
                                    ' additional description
                                    itemDescription = itemDescription & ", " & rowCrossHire("ITEMDESC#2")
                                end if

                                if trim(rowCrossHire("ITEMDESC#3")) <> "" then
                                    ' additional description
                                    itemDescription = itemDescription & ", " & rowCrossHire("ITEMDESC#3")
                                end if
                    %>
                    <tr>
                        <td height="30"><a href="/dashboard-fullscreen/individual/contracts/view.asp?Allow=f875eba085941cc78509bd3482dc0294&amp;Contract=<%= rowCrossHire("CONTNO") %>" target="_blank" title="View <%= rowCrossHire("CONTNO") %>"><%= rowCrossHire("CONTNO") %></a></td>
                        <td height="30"><%= sonNo %></td>
                        <td height="30"><%= rowCrossHire("ACCTNAME") %></td>
                        <td height="30"><%= rowCrossHire("ITEMNO") %></td>
                        <td height="30"><%= itemDescription %></td>
                        <td align="center" height="30"><%= getItemStatus(rowCrossHire("STATUS")) %></td>
                        <td align="center" height="30"><%= rowCrossHire("NLCODE") %></td>
                        <td align="center" height="30"><%= rowCrossHire("QTY") %></td>
                        <td align="center" height="30"><%= rowCrossHire("QTYRETD") %></td>
                        <td align="center" height="30"><%= formatcurrency(rowCrossHire("RATE#1"), 2) %></td>
                        <td align="center" height="30"><%= rowCrossHire("RATE#1")  %></td>

                    </tr>
                    <%
                                rowCrossHire.movenext
                            loop
                        end if
                    %>
                    <tr class="contract">
                        <th colspan="11">&nbsp;</th>
                    </tr>
                </table>

            <!-- content - end --></div>

        <!-- container - end --></div>

    </body>
</html>
<%
    else
        ' security check failed
        dim currentDate, dbLog, ipAddress, logSQL

        currentDate = year(date) & "-" & month(date) & "-" & day(date) & " " & formatDateTime(now(), 3)
        ipAddress = request.serverVariables("HTTP_X_FORWARDED_FOR")

        if ipAddress = "" then
            ' get ip address
            ipAddress = request.serverVariables("REMOTE_ADDR")
        end if

        logSQL = "INSERT INTO LogIP (LogIPDate, LogIPAddress, LogIPPage) VALUES ('" & currentDate & "', '" & ipAddress & "', 'cross-hire/view.asp')"
        set dbLog = executeSQL2(logSQL)
    end if
%>

davidjennings 0 Junior Poster in Training · Answer 4 · 2013-11-01T16:06:02+00:00

Hi Just a further throught about doing this is this possible?

select "QTY" * "RATE#1" as a column in your SQL statement, alias it using "AS columnName", then reference columnName in your output

crosshireSQL = "SELECT CONTITEMS.CONTNO, CONTITEMS.ITEMDESC, CONTITEMS.ITEMDESC#2, CONTITEMS.RATE#1, CONTITEMS.ITEMDESC#3, CONTITEMS.ITEMNO, CONTITEMS.NLCODE, CONTITEMS.QTY, CONTITEMS.QTYRETD, CONTITEMS.STATUS, CONTRACTS.ACCTNAME FROM CONTITEMS INNER JOIN CONTRACTS ON CONTRACTS.CONTNO = CONTITEMS.CONTNO WHERE CONTITEMS.NLCODE IN ('4001') AND CONTITEMS.STATUS = 1 ORDER BY CONTITEMS.CONTNO ASC, CONTITEMS.ITEMNO ASC"
                        set rowCrossHire = returnRecordSet(crosshireSQL)

davidjennings 0 Junior Poster in Training · Answer 5 · 2013-11-01T23:30:22+00:00

Thanks Jorge for your reply
I have tested the select 2 * 5.25 as [total] in PHPMYADMIN, all good.I will build it into my SQL query, I am sure I can close the thread.
cheers
D

davidjennings 0 Junior Poster in Training · Answer 6 · 2013-11-04T09:42:36+00:00

Hi Jorge - I have tried the following:
Added the sql (CONTITEMS.RATE#1 * CONTITEMS.QTY AS TOTAL)
And added the output code as TOTAL in the final table. The SQL does not even produce a new column?

Thanks in advance

D

CONTRACTS.ACCTNAME, CONTITEMS.RATE#1 * CONTITEMS.QTY AS TOTAL FROM CONTITEMS INNER JOIN CONTRACTS ON CONTRACTS.CONTNO = CONTITEMS.CONTNO WHERE CONTITEMS.NLCODE IN ('4001') AND CONTITEMS.STATUS = 1 ORDER BY CONTITEMS.CONTNO ASC, CONTITEMS.ITEMNO ASC"

<td align="center" height="30"><%= rowCrossHire("QTY") %></td> <td align="center" height="30"><%= rowCrossHire("QTYRETD") %></td> <td align="center" height="30"><%= formatcurrency(rowCrossHire("RATE#1"), 2) %></td> <td align="center" height="30"><%= TOTAL %></td>

davidjennings 0 Junior Poster in Training · Answer 7 · 2013-11-04T15:11:54+00:00

Hi Jorge - Thanks for the update this works fine (<%= rowCrossHire("TOTAL") %>)

I will mark as solved

Thanks

David