led_zephrylin
asked on
Another VBScript/ASP problem
Hi, I can't figure this out for the life of me, and I was wondering of anyone can help. I've got 2 functions; 1 to get a list of all orders for a certain part number, and the second to add the sum of the quantities of all orders on a certain day. Here is the code:
function GetShipmentsIn()
dim shipIn
redim shipIn(-1,1)
set hubRS = createobject("adodb.record set")
hubRS.Open "SELECT dueDate, qty FROM ShipmentsIn WHERE partid="&partid&" AND BookedIn='NO'", hubcon
do while not hubRS.EOF
size = ubound(shipIn)
if size = -1 then
redim shipIn(size+1, 1)
else
redim preserve shipIn(size+1, 1)
end if
shipIn(ubound(shipIn), 0) = hubRS(0)
shipIn(ubound(shipIn), 1) = hubRS(1)
'Response.Write(shipIn(ubo und(shipIn ), 0))
hubRS.MoveNext
loop
set hubRS = nothing
GetShipmentsIn = shipIn
end function
function GetTodaysShipmentsIn(theDa te)
theDate = ReverseDate(theDate)
count = 0
for i=0 to ubound(shipments_in)
if shipments_in(ubound(shipme nts_in), 0) = theDate then
Response.Write("adding: "&shipments_in(ubound(ship ments_in), 1)&"<br />")
count = count + shipments_in(ubound(shipme nts_in), 1)
end if
next
GetTodaysShipmentsIn = count
end function
Now, this works fine (i think) when the methods are ran just the once i.e., 'arr = GetShipmentsIn()' followed by 'tot = GetTodaysShipmentsIn(now)' , but what I ideally want is to put the call to the second function in a for loop, which will calculate the sum of all the orders for each day of the week. When I put this call in a for loop, the browser times out. I could understand it if it was working with masses of data, but there are only 2 records in the database so it can't be pure worload that's timing-out the brower, can it?
Any suggestions welcome,
Cheers!
Darren.
function GetShipmentsIn()
dim shipIn
redim shipIn(-1,1)
set hubRS = createobject("adodb.record
hubRS.Open "SELECT dueDate, qty FROM ShipmentsIn WHERE partid="&partid&" AND BookedIn='NO'", hubcon
do while not hubRS.EOF
size = ubound(shipIn)
if size = -1 then
redim shipIn(size+1, 1)
else
redim preserve shipIn(size+1, 1)
end if
shipIn(ubound(shipIn), 0) = hubRS(0)
shipIn(ubound(shipIn), 1) = hubRS(1)
'Response.Write(shipIn(ubo
hubRS.MoveNext
loop
set hubRS = nothing
GetShipmentsIn = shipIn
end function
function GetTodaysShipmentsIn(theDa
theDate = ReverseDate(theDate)
count = 0
for i=0 to ubound(shipments_in)
if shipments_in(ubound(shipme
Response.Write("adding: "&shipments_in(ubound(ship
count = count + shipments_in(ubound(shipme
end if
next
GetTodaysShipmentsIn = count
end function
Now, this works fine (i think) when the methods are ran just the once i.e., 'arr = GetShipmentsIn()' followed by 'tot = GetTodaysShipmentsIn(now)'
Any suggestions welcome,
Cheers!
Darren.
Your first function could just become this
function GetShipmentsIn()
set hubRS = createobject("adodb.record set")
hubRS.Open "SELECT dueDate, qty FROM ShipmentsIn ....
GetShipmentsIn=hubRS.GetRo ws
hubRS.Close
set hubRS = nothing
end function
Then your shipin array contains all the data, much faster and cleaner.
function GetShipmentsIn()
set hubRS = createobject("adodb.record
hubRS.Open "SELECT dueDate, qty FROM ShipmentsIn ....
GetShipmentsIn=hubRS.GetRo
hubRS.Close
set hubRS = nothing
end function
Then your shipin array contains all the data, much faster and cleaner.
Shouldn't you be passing the array to GetTodaysShipmentsIn()?
Also, if I might make a recommendation...instead of rediming to get the size of the array, just use the hubRS.RecordCount to get the size of the recordset, then declare your array based on that size....just an idea to speed up that code...
... that should be...
GetShipmentsIn array contains all the records
GetShipmentsIn array contains all the records
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@dwaldner
I was thinking that too but RecordCount isn't available some (forward only) cursors.
I was thinking that too but RecordCount isn't available some (forward only) cursors.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Before your line:
set hubRS = nothing
Type this in
hubRS.Close
Cheers,
D