CODEWITHSUNDEEP
asp-classicvbscriptado
Leroy Mikenzi
Leroy Mikenzi

Reputation: 810

how to sum values of a column using vbscript

I'm trying to get the sum of values of a column, for that I'm using SUM() in my select statement.

<%
            sql = "select SUM(OpcCalEstQuantity) as qt_total from [Sheet1$] where cInt(JobEstimateNumber) = '"&cint(request.QueryString("no"))&"' and MccDescription = 'CTP Plate Making & Plates' and MaoOperationDescription = 'Plate Making'"
            rs1.open sql, con, 1, 2
            do while not rs1.eof
            %>
            <td style="padding:3px; text-align:right;"><%=rs1("qt_total")%></td>
            <%
                rs1.movenext
                loop
                rs1.close
            %>

but I get this error while displaying it on browser.

Microsoft JET Database Engine error '80040e14'

Invalid use of Null

So I thought the workaround wold be to use vbscript to count the values. But there is no such function to count values from a column.

Upvotes: 0

Views: 6576

Answers (2)

Erik Oosterwaal
Erik Oosterwaal

Reputation: 4374

The coalesce is a good suggestion if you'd like to solve this in SQL.
If you want to solve it purely in vbscript/asp, you will have to loop and count the total amount yourself, try this:

<%
    sql = "select OpcCalEstQuantity from [Sheet1$] where cInt(JobEstimateNumber) = '"&cint(request.QueryString("no"))&"' and MccDescription = 'CTP Plate Making & Plates' and MaoOperationDescription = 'Plate Making'"
    rs1.open sql, con, 1, 2
%>

<%  dim total : total = 0
    do while not rs1.eof 
        if NOT(isNull(rs1("OpcCalEstQuantity")) OR rs1("OpcCalEstQuantity")="") then total = total + cDbl(rs1("OpcCalEstQuantity"))
        rs1.movenext
    loop
    rs1.close
%>
<td style="padding:3px; text-align:right;"><%=total%></td>

Hope this helps,
Erik

Upvotes: 1

AutomatedChaos
AutomatedChaos

Reputation: 7500

I am not very into SQL and MS Jet Engines, but I think the column you want to SUM contains some NULL values. To get rid of them and if your database is supporting it, you can use the coalesce function like:

sql = "select SUM(COALESCE(OpcCalEstQuantity, 0)) as qt_total from ......"

Upvotes: 1

Related Questions