R – Generating complete SKUs in Classic ASP

asp-classicvbscript

Hi I have products that are made up of a couple of options. Each Option has a SKU Code. You can only select one option from each SKU Group and the options have to be concatenated in the order of the SKUGroup.

So for example i would have a list of options in a table in the DB that looked like

OptID   PID     SKU Price   SKUGroup

156727  93941   C   171.00  1
156728  93941   BN  171.00  1
156729  93941   PN  171.00  1
156718  93940   W   115.20  2
156719  93940   CA  115.20  2
156720  93940   BA  115.20  2
156721  93940   BNA 115.20  2
156722  93940   BN  115.20  2
156723  93940   BS  115.20  2
156716  93939   CHR 121.50  3
156717  93939   NK  138.00  3

And a few finished product SKUs would look something like:

C-W-CHR 407.70
C-W-NK  424.20
C-CA-CHR    407.20
C-CA-NK 424.20

I am trying to make a script that will create a listing of every possible combination of SKU and the price of the combined options.

I need this done in Classic ASP (vbscript) and I'm not that familiar with it. So I'm looking for all the help I can get.

Thanks!

Best Answer

I would start by connecting to the database and creating three recordsets.

Set connection = CreateObject("ADODB.Connection")
connection.Open ConnectionString 
Set rsOption1 = CreateObject("ADODB.recordset")
Set rsOption2 = CreateObject("ADODB.recordset")
Set rsOption3 = CreateObject("ADODB.recordset")
rsOption1.Open "SELECT * FROM TableName WHERE SKUGroup = 1", connection, 3,3
rsOption2.Open "SELECT * FROM TableName WHERE SKUGroup = 2", connection, 3,3
rsOption3.Open "SELECT * FROM TableName WHERE SKUGroup = 3", connection, 3,3

Then you can use nested loops to get the combinations. Something like this (Untested, this probably will not work as is, but it gives you an idea of how to do this) (Also this assumes that you have to select at least one option from each group)

for i = 0 to rsOption1.RecordCount
    rsOption1.Move i, 1
    for j = 0 to rsOption2.RecordCount
        rsOption2.Move j, 1
        for k = 0 to rsOption3.RecordCount
            rsOption3.Move k, 1
            'Write rsOption1.Fields(2).Value & "-" & rsOption2.Fields(2).Value & _
            '"-" & rsOption3.Fields(2).Value & "     " & _
            'FormatCurrency((rsOption1.Fields(3).Value + rsOption2.Fields(3).Value + rsOption3.Fields(3).Value))
        Next
    Next
Next
Related Topic