Solved

** SORT VBS ARRAY - URGENT ***

Posted on 2006-11-22
11
1,018 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:andrewmilner
  • 5
  • 3
  • 3
11 Comments
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 400 total points
ID: 18000259
>>Maxiumum points to the fully working code provided.<<

We are not so much a coding service providing finished products, but if you would like some advice with your problem, I have a suggestion:

1) create a disconnected record set object
2) populate it with your array data
3) use the .Sort() method to get what you want


Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Fields.Append "PartNo", adVarChar, 255
objRS.Fields.Append "Price", adcurrency
objRS.Open

for i=0 to UBound(arrRows,2)
      objRS.AddNew
      objRS.Fields("PartNo").Value =  arrRows(0,i)
      objRS.Fields("Price").Value = arrRows(1,i)
next
         
objRS.sort = "Price ASC"
objRS.MoveFirst

Response.Write("<Table Border=1 cellpadding=2 cellspacing=2>")
for i=1 to objRS.RecordCount
      Response.Write("<TR>")
      for j=0 to objRS.Fields.count-1
            Response.Write("<TD>" & objRS(j).Value & "</TD>")
      next
      Response.Write("</TR>")
      objRS.MoveNext
next
Response.Write("</Table>")

objRS.close
set objRS = Nothing


You can convert this back to an array by using

arrYourArray = objRS.GetRows()



FtB
0
 
LVL 22

Assisted Solution

by:WMIF
WMIF earned 100 total points
ID: 18000267
put this outside of the <% %> markers:
<script language=JScript runat=server>
    function SortVBArray(arrVBArray) {
        return arrVBArray.toArray().sort().join('\b');
    }
</script>

then put this somewhere on the page (top or bottom doesnt matter):
<%
    Function SortArray(arrInput)
        SortArray = Split(SortVBArray(arrInput), Chr(8))
    End Function
%>



then in your code:
If PromoFound  then
' Output Result of array

promoarray = SortArray(promoarray)
For i = 0 to UBOUND(PromoArray, 2)
     
     Partno = PromoArray(0,i)
     CurrentPrice = cdbl(PromoArray(1,i))
     
     response.write Partno & " - " & CurrentPrice & "<br>"
     
     
Next
     
     end if
0
 

Author Comment

by:andrewmilner
ID: 18000373
Wow, some good comments here.  Both look like practical solutions.

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.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 18001047
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
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18001209
>>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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 18003543
>>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



0
 

Author Comment

by:andrewmilner
ID: 18004419
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.enablestockmonitoring, 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").Value =  PromoArray(0,i)
     objRS.Fields("Description").Value = PromoArray(1,i)
       objRS.Fields("Price").Value = 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

%>
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 18005235
Take a look at the .GetRows() method. It will make your life much easier.

Good luck with the code,

FtB
0
 

Author Comment

by:andrewmilner
ID: 18005279
Thanks, you've been a big help.

Andrew.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 18005382
You are very welcome.

FtB
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18009665
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.enablestockmonitoring, 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"
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Passing special characters in http get request 7 39
format nvarchar field as mm/dd/yyyy 4 62
Time/Date Query 11 38
Generate report pulling data (links) from three tables 31 59
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now