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!)...
subTotal = 0
currentOrderNo = -1
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>")
subTotal = 0
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"))
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 :)