0

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>
3
Contributors
10
Replies
45
Views
3 Years
Discussion Span
Last Post by davidjennings
Featured Replies
  • 1

    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 … Read More

0

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?

0

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

0

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

0

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

Edited by davidjennings

0

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)
0

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 by JorgeM

0

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

0

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>

1

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".

0

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

I will mark as solved

Thanks

David

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.