Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 9th, 2003, 06:53 AM
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to make subtotal of a column

I am writing a report on asp i selected the data
from oracle table
"select challan date challan no, Order no, amount from table x
where challan date = (date input)"

the data are as follow :
challan no. Order no. amount(rs)
10 2 2000
11 3 1000
12 2 4000
13 5 3500
14 3 5000


Now my requirment is like this

challan no. order no. amount
10 2 2000
12 2 4000
subtotal (amount) 6000

11 3 1000
14 3 5000
subtotal (amount) 6000

13 5 3500
subtotal (amount 3500

Pl. write me how to program in asp to get desired output
input is only date

  #2 (permalink)  
Old September 9th, 2003, 07:53 AM
Friend of Wrox
Points: 3,558, Level: 25
Points: 3,558, Level: 25 Points: 3,558, Level: 25 Points: 3,558, Level: 25
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: California, USA
Posts: 996
Thanks: 2
Thanked 11 Times in 11 Posts
Send a message via Yahoo to melvik
Default

I guess u should put it in a dataset & then make a counter by urself.

Always:),
Hovik Melkomian.
  #3 (permalink)  
Old September 9th, 2003, 08:34 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

So you need to group by "order no." and then display a separate table for each "order no." with a corresponding subtotal. Normally I would provide a suggestion on how to do this in your SQL statement, but it would typically involve a stored procedure. And you asked for an ASP solution so here's some pseudo code...

Modify your query to order by "order no."

nLastOrderNo = firstRecord("order no.")
for each record
    if nLastOrderNo <> currentRecord("order no")
        output total row with nTotalAmount
        nTotalAmount = 0
    end if
    output 1 record
next record
output total row for final total in nTotalAmount

Hope that makes sense.

Peter
  #4 (permalink)  
Old September 11th, 2003, 07:00 AM
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

sorry planoie i tried your suggestion but could not get
through. Actualy there is no currentrecord concept in ASP
In any case we have to match first to next record then only
we can get subtotal of that column.
Pl. suggest something more


Thanks



  #5 (permalink)  
Old September 11th, 2003, 07:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen, , Denmark.
Posts: 440
Thanks: 0
Thanked 0 Times in 0 Posts
Default

OK, let me have a go!

What technology do you use!? You say "...there is no currentrecord concept in ASP", but it is an ASPX forum.

Jacob.
  #6 (permalink)  
Old September 11th, 2003, 08:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen, , Denmark.
Posts: 440
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, first of all you have to order the data when executing the SQL. Append somthing like...

ORDER BY 'orderNo'

...to you sql statement. Be careful with the quotes, and make sure that the orderNo name above corresponds to your table.

Then when you have got your recordset you can do like this (some would probably argue with the way output is done, but you can change that!)...

Dim subTotal
subTotal = 0
Dim currentOrderNo
currentOrderNo = -1
Dim tmp

If Not rSet.EOF Then
  Response.Write("<TABLE CELLSPACING='3' CELLPADDING='3' BORDER='1'>")
  While Not rSet.EOF
    If CInt(rSet("orderNo")) <> currentOrderNo Then
      Response.Write("<TR><TD>challan no.</TD><TD>order no.</TD><TD>amounts</TD></TR>")
      If currentOrderNo <> -1 Then
        tmp = "<TD COLSPAN='2'><B>sub total</B></TD>"
        tmp = tmp & "<TD ALIGN='right'><B>" & subTotal & "</B></TD>"
        Response.Write("<TR>" & tmp & "<TR>")
      End If
      subTotal = 0
    End If
    currentOrderNo = CInt(rSet("orderNo"))
    tmp = "<TD ALIGN='right'>" & rSet("challanNo") & "</TD>"
    tmp = tmp & "<TD ALIGN='right'>" & rSet("orderNo") & "</TD>"
    tmp = tmp & "<TD ALIGN='right'>" & rSet("amount") & "</TD>"
    Response.Write("<TR>" & tmp & "</TR>")
    subTotal = subTotal + CInt(rSet("amount"))
    rSet.MoveNext
Wend
Response.Write("</TABLE>")

End If

As you can see I have tried to output the data in a table, however the above code has NOT been tested since I do not have such database structure. Moreover I cannot remember the conversion to integer but I think is CInt.

Before you can use the code above you have to make sure that the names of the columns in your database corresponds to the ones I have used. I use orderNo, amount, challanNo. Furthermore no record can have challan no = -1.

Well, that was a lot of code without running it (no garanties )! I hope that you are able to use it. Let me know!?

I am no 100% sure but I think that you can actually do the work in pure SQl using the SUM function, however I cannot find my SQL book :)

Jacob.
  #7 (permalink)  
Old September 12th, 2003, 05:40 AM
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by jacob
 OK, let me have a go!

What technology do you use!? You say "...there is no currentrecord concept in ASP", but it is an ASPX forum.

Jacob.
hi jacob
thanks for your codes, but i have solved the problem
it was very simple. The problem was when it was run "exception occured" was coming and program was terminated. we just wrote on error resume next. We got a positive output
u can have a look which is in ASP. I want a help from u why exception
occured message comes. if I do give on error resume next.
:)
    str = "select td_totamt,td_wono from td_da order by td_wono "
        rs1.Open str,con
       while not rs1.eof
        fstwono=rs1("td_wono")
           str2 = "select sum(td_totamt),td_wono from td_da where td_wono='"&fstwono&"' group by td_wono"
        rs2.Open str2,con
        %>
        <TD><% Response.Write "rs1 PONO" & rs1("td_wono")%></TD>
        <TD><%Response.Write rs1("td_totamt")%></TD> </tr>
            <% sum1=sum1+cint(rs1("td_totamt"))
        rs1.MoveNext
        on error resume next
       if (fstwono)<>(rs1("td_wono"))then%>
      <tr><TD><%' Response.Write rs2("sum(td_totamt)")%></TD></tr>
      <tr><TD><% Response.Write sum1 %></TD></tr>
        <% sum1=0
        end if

        'rs2.MoveNext
        rs2.Close
    wend
        rs1.Close
     %>


  #8 (permalink)  
Old September 14th, 2003, 02:16 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen, , Denmark.
Posts: 440
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The code you posted fails?

If it does I would guess that it is because you cannot extract the sum from the recordset the way you do! You do this...

rs2("sum(td_totamt)")

I am however not sertain about this. I can see that you have made this line a comment, so does the page work when you do not include the line?! Youd o not include the movenext command either.

There is no space before 'then' in this line...

if (fstwono)<>(rs1("td_wono"))then%>

And to extract the sum value... Are you able to get values from the recordset with an index instead of a label?!

Jacob.

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add new subtotal column to GridView VB nomo ASP.NET 2.0 Basics 2 March 11th, 2014 01:36 AM
Subtotal yogeshyl SQL Language 1 July 15th, 2008 02:16 PM
How do you make a Data Grid Hyperlink Column pass KarmenC ASP.NET 1.x and 2.0 Application Design 5 January 13th, 2005 11:43 AM
how to make column of table 1 = to column of table gilgalbiblewheel Classic ASP Databases 4 October 11th, 2004 11:57 PM
SubTotal Field geoffeg BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 September 23rd, 2004 06:20 PM



All times are GMT -4. The time now is 12:01 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.