andrewmilner
asked on
** SORT VBS ARRAY - URGENT ***
Hi Folks,
Could someone code a sort for the following array?, i'm struggling, looked at lots of examples but don't seem to be able to get anything working. Sure it's simple for someone who knows what they're doing.
Current Dynamic Array Output (could get a lot bigger)
PartNo Price
-------- ------------
641322 9.99
716658 12.99
586157 4.99
Code used to build and output.
CODE
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
while not clist.eof
' Start Mod Code for 3 for 2
' Build String of 3 for 2 objects
mySQL = "SELECT partno as partno from promotion_type where partno = '" & clist("partno") & "' and LEFT(name, 7) = '3 for 2'"
set PromoRS = template.execute(mySQL)
if PromoRS.eof then
PromoFound = False
else
PromoFound = True
PromoPartNo = PromoRS("partno")
PromoPrice = CList("properPrice")
PromoQuantity = Clist("quantity")
' Build the Array
Redim PRESERVE PromoArray(1,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoPrice
if PromoQuantity > 1 then ' then we need to loop so each qty is counted as an item
QtyToLoop = PromoQuantity - 1
QtyCounter = 1
do while not QtyCounter > QtyToLoop
z = z + 1
' Add the item in again as we may want to give this away twice if cheapest
Redim PRESERVE PromoArray(1,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoPrice
QtyCounter = QtyCounter + 1
loop
end if
z = z + 1
end if
Set PromoRS = nothing
PromoCount = z
' SOME MORE IRRELEVENT CODE THEN LOOP
CODE TO PRINT ARRAY
-------------------------- ----------
If PromoFound then
' Output Result of array
For i = 0 to UBOUND(PromoArray, 2)
Partno = PromoArray(0,i)
CurrentPrice = cdbl(PromoArray(1,i))
response.write Partno & " - " & CurrentPrice & "<br>"
Next
end if
Ultimatley the code will be used to offer free items depending upon the cheapest but I should be able to code that if I could just sort the array by price, smallest first.
Hope you can help.
Maxiumum points to the fully working code provided.
Thanks in advance,
Andrew.
Could someone code a sort for the following array?, i'm struggling, looked at lots of examples but don't seem to be able to get anything working. Sure it's simple for someone who knows what they're doing.
Current Dynamic Array Output (could get a lot bigger)
PartNo Price
-------- ------------
641322 9.99
716658 12.99
586157 4.99
Code used to build and output.
CODE
--------------------------
while not clist.eof
' Start Mod Code for 3 for 2
' Build String of 3 for 2 objects
mySQL = "SELECT partno as partno from promotion_type where partno = '" & clist("partno") & "' and LEFT(name, 7) = '3 for 2'"
set PromoRS = template.execute(mySQL)
if PromoRS.eof then
PromoFound = False
else
PromoFound = True
PromoPartNo = PromoRS("partno")
PromoPrice = CList("properPrice")
PromoQuantity = Clist("quantity")
' Build the Array
Redim PRESERVE PromoArray(1,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoPrice
if PromoQuantity > 1 then ' then we need to loop so each qty is counted as an item
QtyToLoop = PromoQuantity - 1
QtyCounter = 1
do while not QtyCounter > QtyToLoop
z = z + 1
' Add the item in again as we may want to give this away twice if cheapest
Redim PRESERVE PromoArray(1,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoPrice
QtyCounter = QtyCounter + 1
loop
end if
z = z + 1
end if
Set PromoRS = nothing
PromoCount = z
' SOME MORE IRRELEVENT CODE THEN LOOP
CODE TO PRINT ARRAY
--------------------------
If PromoFound then
' Output Result of array
For i = 0 to UBOUND(PromoArray, 2)
Partno = PromoArray(0,i)
CurrentPrice = cdbl(PromoArray(1,i))
response.write Partno & " - " & CurrentPrice & "<br>"
Next
end if
Ultimatley the code will be used to offer free items depending upon the cheapest but I should be able to code that if I could just sort the array by price, smallest first.
Hope you can help.
Maxiumum points to the fully working code provided.
Thanks in advance,
Andrew.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I like the JScript solution except for one thing--it limits your sort possibilities. When you use the recordset method, you can sort on as many fields as you like. For example:
objRS.sort = "Price DESC, Product ASC"
That way, you will have an ordered list when there is a tie on the primary sort key.
On the other hand, the JScript model is less expensive in terms of resources, and if you only need to sort a single dimensional array, it is a handy solution.
FtB
objRS.sort = "Price DESC, Product ASC"
That way, you will have an ordered list when there is a tie on the primary sort key.
On the other hand, the JScript model is less expensive in terms of resources, and if you only need to sort a single dimensional array, it is a handy solution.
FtB
>>it limits your sort possibilities.
i definately agree.
one thing i have been thinking though about this question, is that we can probably optimize the sql query from 2 seperate calls to 1. with that done, you could do the sort in that same query and you wouldnt have to worry about resorting an array. are you interested in this andrew? if so, lets see the first sql query for clist.
i definately agree.
one thing i have been thinking though about this question, is that we can probably optimize the sql query from 2 seperate calls to 1. with that done, you could do the sort in that same query and you wouldnt have to worry about resorting an array. are you interested in this andrew? if so, lets see the first sql query for clist.
>>you could do the sort in that same query and you wouldnt have to worry about resorting an array
Now if that is possible, that is by far the best approach--anything that can be done from the SQL directly will be the most efficient.
As an aside, there is no need for this:
if PromoRS.eof then
PromoFound = False
else
PromoFound = True
PromoPartNo = PromoRS("partno")
PromoPrice = CList("properPrice")
PromoQuantity = Clist("quantity")
' Build the Array
Redim PRESERVE PromoArray(1,z)
PromoArray(0,z) = PromoPartNo
All you need to do instead is this:
PromoArray = PromoRS.GetRows()
PromoRS.Close()
Set PromoRS = Nothing
Now if you combine what WMIF has said about sorting your query along with the use of the .GetRows() method, you will be set. Is there no way to make a join between the clist query and the promotion query?
FtB
Now if that is possible, that is by far the best approach--anything that can be done from the SQL directly will be the most efficient.
As an aside, there is no need for this:
if PromoRS.eof then
PromoFound = False
else
PromoFound = True
PromoPartNo = PromoRS("partno")
PromoPrice = CList("properPrice")
PromoQuantity = Clist("quantity")
' Build the Array
Redim PRESERVE PromoArray(1,z)
PromoArray(0,z) = PromoPartNo
All you need to do instead is this:
PromoArray = PromoRS.GetRows()
PromoRS.Close()
Set PromoRS = Nothing
Now if you combine what WMIF has said about sorting your query along with the use of the .GetRows() method, you will be set. Is there no way to make a join between the clist query and the promotion query?
FtB
ASKER
Hi Guys,
I have managed to get a working solution by going back to the point of original SQL and then using FtB's method to create another RS.
It's probably not the best way of doing it, maybe I dont even need the array anymore as the original SQL can be sorted?
Here's the code I'm working from now. If it can be improved resource wise then I'd appreciate any comments you can give.
I'm going to award majority to FtB as that is the solution I went with, but partial points to WMIF as this also should be a usable function.
The current code is below. Any comments have my thumbs up.
Cheers Guys.
Andrew
CODE
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -----
<%
' Irrelevent code above here
Set cmdTemp3 = Server.CreateObject("ADODB .Command")
cmdTemp3.CommandType = 1
Set cmdTemp3.ActiveConnection = template
Set CList = Server.CreateObject("ADODB .Recordset ")
cmdTemp3.CommandText = "SELECT distinct orderitems.*, (orderitems.couponprice / orderitems.quantity) as couponprice, (orderitems.price) as properprice, (orderitems.vat + orderitems.coupontax) as propervat, stock.minimumorderqty, stock.maximumorderqty, stock.stocklevel, stock.stocksuspendbelow, stock.enablestockmonitorin g, records.thumbimage AS miniimage FROM OrderItems, stock, records, stocktorecords WHERE (OrderItems.SessionID = '" & ThisSession & "') and stock.partno = orderitems.partno and stock.partno = stocktorecords.partno and records.recid = stocktorecords.recid and dropped = 0 and external = 0 and orderitems.quantity > 0 and records.recid in (select top 1 ssr.recid from stocktorecords ssr where partno = orderitems.partno) UNION Select orderitems.*,0,0,0,0,0,0,0 ,0,'' from orderitems where (OrderItems.SessionID = '" & ThisSession & "') and dropped = 0 and external = 1 order by orderitems.theorder, orderitems.cartdate, orderitems.price"
CList.Open cmdTemp3, , 0, 1
Clist.movefirst
z = 0 ' counter for Promo Array
' Define Array for counting 3 for 2 items
Dim PromoArray()
'myArray(col,row)
do while not CList.eof
'## START CUSTOM PROMO CODE
' we need to first establish how many items are in the cart that are in the 3 for 2 promo
' RESET ALL Promo QTY's in OrderItems
ResSQL = "Update OrderItems set PromoCount = 0 where SessionID = '" & ThisSession & "' and partno = '" & CList("PartNo") & "'"
set PromoRS = template.execute(ResSQL)
' Build String of 3 for 2 objects
mySQL = "SELECT partno as partno from promotion_type where partno = '" & clist("partno") & "' and LEFT(name, 7) = '3 for 2'"
set PromoRS = template.execute(mySQL)
if PromoRS.eof then
PromoFound = False
else
PromoFound = True
PromoPartNo = PromoRS("partno")
PromoPrice = CList("properPrice")
PromoQuantity = Clist("quantity")
PromoDesc = Clist("Description")
' Build the Array
Redim PRESERVE PromoArray(2,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoDesc
PromoArray(2,z) = PromoPrice
if PromoQuantity > 1 then ' then we need to loop so each qty is counted as an item
QtyToLoop = PromoQuantity - 1
QtyCounter = 1
do while not QtyCounter > QtyToLoop
z = z + 1
' Add the item in again as we may want to give this away twice if cheapest
Redim PRESERVE PromoArray(2,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoDesc
PromoArray(2,z) = PromoPrice
QtyCounter = QtyCounter + 1
loop
end if
z = z + 1
end if
Set PromoRS = nothing
PromoCount = z
CList.movenext
loop
CList.close
' Now Mark the items in OrderItems that are going to be free
FreeCount = z \ 3
FreeCount = fix(FreeCount)
If PromoFound and FreeCount > 0 then
' Convert Array to RS to sort
Set objRS = Server.CreateObject("ADODB .Recordset ")
objRS.Fields.Append "PartNo", adVarChar, 11
objRS.Fields.Append "Description", adVarChar, 30
objRS.Fields.Append "Price", adcurrency
objRS.Open
for i=0 to UBound(PromoArray,2)
objRS.AddNew
objRS.Fields("PartNo").Val ue = PromoArray(0,i)
objRS.Fields("Description" ).Value = PromoArray(1,i)
objRS.Fields("Price").Valu e = PromoArray(2,i)
next
objRS.sort = "Price ASC"
objRS.MoveFirst
UpdateCounter = 0
for i=0 to UBOUND(PromoArray, 2)
if UpdateCounter < FreeCount then
UpdateSQL = "Update OrderItems set PromoCount = PromoCount + 1 where SessionID = '" & ThisSession & "' and partno = '" & objRS("PartNo") & "'"
template.execute (UpdateSQL)
end if
objRS.MoveNext
UpdateCounter = UpdateCounter + 1
next
objRS.close
set objRS = Nothing
end if
CList.Open cmdTemp3, , 0, 1
if CList.eof then
emptycart = "YES"
end if
%>
I have managed to get a working solution by going back to the point of original SQL and then using FtB's method to create another RS.
It's probably not the best way of doing it, maybe I dont even need the array anymore as the original SQL can be sorted?
Here's the code I'm working from now. If it can be improved resource wise then I'd appreciate any comments you can give.
I'm going to award majority to FtB as that is the solution I went with, but partial points to WMIF as this also should be a usable function.
The current code is below. Any comments have my thumbs up.
Cheers Guys.
Andrew
CODE
--------------------------
<%
' Irrelevent code above here
Set cmdTemp3 = Server.CreateObject("ADODB
cmdTemp3.CommandType = 1
Set cmdTemp3.ActiveConnection = template
Set CList = Server.CreateObject("ADODB
cmdTemp3.CommandText = "SELECT distinct orderitems.*, (orderitems.couponprice / orderitems.quantity) as couponprice, (orderitems.price) as properprice, (orderitems.vat + orderitems.coupontax) as propervat, stock.minimumorderqty, stock.maximumorderqty, stock.stocklevel, stock.stocksuspendbelow, stock.enablestockmonitorin
CList.Open cmdTemp3, , 0, 1
Clist.movefirst
z = 0 ' counter for Promo Array
' Define Array for counting 3 for 2 items
Dim PromoArray()
'myArray(col,row)
do while not CList.eof
'## START CUSTOM PROMO CODE
' we need to first establish how many items are in the cart that are in the 3 for 2 promo
' RESET ALL Promo QTY's in OrderItems
ResSQL = "Update OrderItems set PromoCount = 0 where SessionID = '" & ThisSession & "' and partno = '" & CList("PartNo") & "'"
set PromoRS = template.execute(ResSQL)
' Build String of 3 for 2 objects
mySQL = "SELECT partno as partno from promotion_type where partno = '" & clist("partno") & "' and LEFT(name, 7) = '3 for 2'"
set PromoRS = template.execute(mySQL)
if PromoRS.eof then
PromoFound = False
else
PromoFound = True
PromoPartNo = PromoRS("partno")
PromoPrice = CList("properPrice")
PromoQuantity = Clist("quantity")
PromoDesc = Clist("Description")
' Build the Array
Redim PRESERVE PromoArray(2,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoDesc
PromoArray(2,z) = PromoPrice
if PromoQuantity > 1 then ' then we need to loop so each qty is counted as an item
QtyToLoop = PromoQuantity - 1
QtyCounter = 1
do while not QtyCounter > QtyToLoop
z = z + 1
' Add the item in again as we may want to give this away twice if cheapest
Redim PRESERVE PromoArray(2,z)
PromoArray(0,z) = PromoPartNo
PromoArray(1,z) = PromoDesc
PromoArray(2,z) = PromoPrice
QtyCounter = QtyCounter + 1
loop
end if
z = z + 1
end if
Set PromoRS = nothing
PromoCount = z
CList.movenext
loop
CList.close
' Now Mark the items in OrderItems that are going to be free
FreeCount = z \ 3
FreeCount = fix(FreeCount)
If PromoFound and FreeCount > 0 then
' Convert Array to RS to sort
Set objRS = Server.CreateObject("ADODB
objRS.Fields.Append "PartNo", adVarChar, 11
objRS.Fields.Append "Description", adVarChar, 30
objRS.Fields.Append "Price", adcurrency
objRS.Open
for i=0 to UBound(PromoArray,2)
objRS.AddNew
objRS.Fields("PartNo").Val
objRS.Fields("Description"
objRS.Fields("Price").Valu
next
objRS.sort = "Price ASC"
objRS.MoveFirst
UpdateCounter = 0
for i=0 to UBOUND(PromoArray, 2)
if UpdateCounter < FreeCount then
UpdateSQL = "Update OrderItems set PromoCount = PromoCount + 1 where SessionID = '" & ThisSession & "' and partno = '" & objRS("PartNo") & "'"
template.execute (UpdateSQL)
end if
objRS.MoveNext
UpdateCounter = UpdateCounter + 1
next
objRS.close
set objRS = Nothing
end if
CList.Open cmdTemp3, , 0, 1
if CList.eof then
emptycart = "YES"
end if
%>
Take a look at the .GetRows() method. It will make your life much easier.
Good luck with the code,
FtB
Good luck with the code,
FtB
ASKER
Thanks, you've been a big help.
Andrew.
Andrew.
You are very welcome.
FtB
FtB
sounds like you may want a list of all the products and only want to note those that have that deal. if so, we can do this with a quick subquery on the original. i have spaced apart the query to show what i changed. to use it, just compress it. then you would access the threefortwo field and check for 'true' to see that the part has the promotion.
cmdTemp3.CommandText = "SELECT distinct orderitems.*, (orderitems.couponprice / orderitems.quantity) as couponprice, (orderitems.price) as properprice, (orderitems.vat + orderitems.coupontax) as propervat, stock.minimumorderqty, stock.maximumorderqty, stock.stocklevel, stock.stocksuspendbelow, stock.enablestockmonitorin g, records.thumbimage AS miniimage,
(select 'true' from promotion_type where promotion_type.partno = orderitems.partno and LEFT(name, 7) = '3 for 2') as threefortwo
FROM OrderItems, stock, records, stocktorecords WHERE (OrderItems.SessionID = '" & ThisSession & "') and stock.partno = orderitems.partno and stock.partno = stocktorecords.partno and records.recid = stocktorecords.recid and dropped = 0 and external = 0 and orderitems.quantity > 0 and records.recid in (select top 1 ssr.recid from stocktorecords ssr where partno = orderitems.partno) UNION Select orderitems.*,0,0,0,0,0,0,0 ,0,'' from orderitems where (OrderItems.SessionID = '" & ThisSession & "') and dropped = 0 and external = 1 order by orderitems.theorder, orderitems.cartdate, orderitems.price"
cmdTemp3.CommandText = "SELECT distinct orderitems.*, (orderitems.couponprice / orderitems.quantity) as couponprice, (orderitems.price) as properprice, (orderitems.vat + orderitems.coupontax) as propervat, stock.minimumorderqty, stock.maximumorderqty, stock.stocklevel, stock.stocksuspendbelow, stock.enablestockmonitorin
(select 'true' from promotion_type where promotion_type.partno = orderitems.partno and LEFT(name, 7) = '3 for 2') as threefortwo
FROM OrderItems, stock, records, stocktorecords WHERE (OrderItems.SessionID = '" & ThisSession & "') and stock.partno = orderitems.partno and stock.partno = stocktorecords.partno and records.recid = stocktorecords.recid and dropped = 0 and external = 0 and orderitems.quantity > 0 and records.recid in (select top 1 ssr.recid from stocktorecords ssr where partno = orderitems.partno) UNION Select orderitems.*,0,0,0,0,0,0,0
ASKER
FTB - I didn't know about disconnected RS's and so that should open up an entire new can for me. Thanks very much for that.
I'll give them both a try in the morning and award points and close once tested. May have to split as they both look very usable and handy.
Thanks again.
Andrew.