Processing a recordset

[WakePro]

Hi There.
This is What i am trying to do. I am working on a hotel booking system. I have to be able to place orders of 1 or more items that will be charged to the Room. I am using a ASP front end and a SQL database. I have the controller class, and the data access class done in vb for the .dll. What i need to know is when i pass a record set from the ASP page. What do i need to do in the Businesslogic/workflow layer? I want to open that recordset and get the values so i can pass them to the data access layer. How can i do this in a loop so i get all the records? Once the processing is done in sql, I return a Unit Cost so i can do some processing to return a total cost for that order. The values i am passing to sql are ItemId and Units. Should i do price totaling in SQL or in vb. If in VB how do i do this. Here is the code I am trying. I dont know if it is right.

Public Function PlaceBillableItemOrder(ByVal Order As ADODB.Recordset, ByVal RoomNumber As Integer, ByVal LastName As String) As Currency
Dim MyOrder As BookingBillables
Dim ItemId As Integer
Dim Units As Integer
Dim TempTotal
Set MyOrder = New BookingBillables
While Order.EOF = False
Set ItemId = Order.Fields(ItemId)
Set Units = Order.Units(Units)
Set PlaceBillableItemOrder = MyOrder.PlaceBillableItemOrder(RoomNumber, LastName, ItemId, Units)
Set TempTotal = TempTotal + PlaceBillableItemOrder
Order.MoveNext
wend
Set PlaceBillableItemOrder = TempTotal
Set MyOrder = Nothing
End Function

I need to return a total value for the order to the ASP page. Please help me if you can.

 

[d1e1r0r3]

Declare an extra variable -
Dim TotalCost as Currency.
Within the while loop use -
ItemId = Order.Fields(ItemId)
Units = Order.Fields(Units)
TotalCost = TotalCost + <however you arrive at the cost of each unit multiplied by volume ordered per itemID>
Order.MoveNext
Wend
Set PlaceBillableItemOrder = TotalCost

Hope this helps

 

[Kavius]

I am sitting with a friend of mine and we just discussed as to whether it is better to run the total on the database side or the code side. The answer, like everything, is it depends.

If you are storing the data in an object oriented fashion (which you aren't) there is a very good chance you already have the data in memory (reading from memory is faster than reading from database to memory then reading from memory).

If you are only getting this data once, there is probably no reason to store the data. In this case you still want to reduce the amount of data to transfer so you should put as much of the processing on the server as possible.

Mind you, this is all the short explanation and I have now been having a 4 hour discussion on memory usage. There is no simple answer to that question so here is the solution sql solution to the problem. Is it optimal for you? I don't know...

It might help to know two things: the structure of your order table (or at least the relevant columns) and the math for the calculation. I guess what I am asking for is the sql statement that generated the recordset.

Without that I can give you a best guess for a sql statement that would solve your problem...

select orderid, sum(units*cost)
from   orders
group  orderid

This will give you the cost of each order. If you have to display each item, then I would say you should sum to the item level and then do the rest of the totalling in the code.

Basically, sum as much as you can. But only do one query.
_______________________________________
Ignorance is a beautiful thing:
You don't know what you can't do...

 

[PraveenMenon]

Kavius,

>>(reading from memory is faster than reading from database to memory then reading from memory).

Please can i have more on the statement? May be u have some links as well for me..

Thanks. All the Best
Praveen Menon
pcmin@rediffmail.com