Generating complete SKUs in Classic ASP

Question

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!

Answer 1

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