View Single Post
  #6 (permalink)  
Old September 11th, 2003, 08:22 AM
jacob jacob is offline
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.