Scripting Dictionary ASP Classic

0
votes

I am building a scripting dictionary in ASP classic from two recordsets, but for some reason almost every item (of about 70) that I attempt to add to the dictionary says it exists and I end up with only 3 items, which also when I attempt to loop through the dictionary after building it, I get an exception.

Dim OrdDict
Set OrdDict = Server.CreateObject("Scripting.Dictionary")

OrdinateSQL = "SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tblDocTypeTopic';"
Set rsTopicOrdinals = Server.CreateObject("ADODB.RecordSet")
rsTopicOrdinals.Open OrdinateSQL, connUTL, adOpenKeyset

If rsTopicOrdinals.EOF = False Then
    Do Until rsTopicOrdinals.EOF
        'ordinal positions are "1" indexed, but the array we're building is zero, so subtract one
        Response.Write rsTopicOrdinals("COLUMN_NAME") & ": "
        If OrdDict.Exists(rsTopicOrdinals("COLUMN_NAME")) Then
            response.write "EXISTS<BR>"
            OrdDict.Item(rsTopicOrdinals("COLUMN_NAME")) = rsTopicOrdinals("ORDINAL_POSITION") - 1
        Else
            response.write "NEW<BR>"
            OrdDict.Add rsTopicOrdinals("COLUMN_NAME"), rsTopicOrdinals("ORDINAL_POSITION") - 1
        End If
        rsTopicOrdinals.MoveNext
    Loop
End If
rsTopicOrdinals.Close
Set rsTopicOrdinals = Nothing

OrdinateSQL = "SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tblDocTypeSubTopic';"
Set rsSubTopicOrdinals = Server.CreateObject("ADODB.RecordSet")
rsSubTopicOrdinals.Open OrdinateSQL, connUTL, adOpenKeyset

If rsSubTopicOrdinals.EOF = False Then
    Do Until rsSubTopicOrdinals.EOF
        'ordinal positions are "1" indexed, add the number of columns in tblDocTypeTopic
        'use a unique name for DisplayOrder and Disabled as the exist in both tables
        Response.Write rsSubTopicOrdinals("COLUMN_NAME") & ": "
        Select Case rsSubTopicOrdinals("COLUMN_NAME")
            Case "DisplayOrder"
                OrdDict.Add "SubTopicDisplayOrder", rsSubTopicOrdinals("ORDINAL_POSITION") + TopicColCount
            Case "Disabled"
                OrdDict.Add "SubTopicDisabled", rsSubTopicOrdinals("ORDINAL_POSITION") + TopicColCount
            Case "DocTypeTopicID"
            Case Else
                If OrdDict.Exists(rsSubTopicOrdinals("COLUMN_NAME")) Then
                    response.write "EXISTS<BR>"
                    OrdDict.Item(rsSubTopicOrdinals("COLUMN_NAME")) = rsSubTopicOrdinals("ORDINAL_POSITION") + TopicColCount
                Else
                    response.write "NEW<BR>"
                    OrdDict.Add rsSubTopicOrdinals("COLUMN_NAME"), rsSubTopicOrdinals("ORDINAL_POSITION") + TopicColCount
                End If
        End Select
        rsSubTopicOrdinals.MoveNext
    Loop
End If
rsSubTopicOrdinals.Close
Set rsSubTopicOrdinals = Nothing

And here's the output:

DocTypeTopicID: NEW
DocTypeID: EXISTS
TopicTitle: EXISTS
DisplayOrder: EXISTS
TopicTitleFontSize: EXISTS
TopicTitleFontFormat: EXISTS
ShortTopicTitle: EXISTS
Disabled: EXISTS
HideTopicOnPublish: EXISTS
DocTypeSubTopicID: NEW
DocTypeTopicID: SubTopicTitle: EXISTS
DisplayOrder: DataType: EXISTS
SubTopicOptions: EXISTS
DefaultValue: EXISTS
Required: EXISTS
Custom: EXISTS
CustomModule: EXISTS
AllowBringForward: EXISTS
AllowForwarding: EXISTS
AllowForwardingChecked: EXISTS
ResponseFontSize: EXISTS
TitleFontSize: EXISTS
TitleFontFormat: EXISTS
OITitle: EXISTS
OITitleFontSize: EXISTS
OITitleFontFormat: EXISTS
OIFontSize: EXISTS
OIFontFormat: EXISTS
OIResponseTitle: EXISTS
OIResponseTitleFontSize: EXISTS
OIResponseTitleFontFormat: EXISTS
OIResponseFontSize: EXISTS
OIResponseFontFormat: EXISTS
OINoResponseDisplay: EXISTS
OIShowModifiedDate: EXISTS
OIShowChangedFlag: EXISTS
OIChangedFlag: EXISTS
OINoInstructionDisplay: EXISTS
DocTypeGridTemplate: EXISTS
DocTypeGridID: EXISTS
GridColumns: EXISTS
GridRows: EXISTS
GridMaxSize: EXISTS
RollupShowSubTopicTitle: EXISTS
TimeBack: EXISTS
SubTopicInt1: EXISTS
SubTopicInt2: EXISTS
SubTopicInt3: EXISTS
SubTopicBit1: EXISTS
SubTopicBit2: EXISTS
SubTopicBit3: EXISTS
SubTopicText1: EXISTS
SubTopicText2: EXISTS
SubTopicText3: EXISTS
AutoPopulateOmit: EXISTS
GridRowType: EXISTS
Disabled: SubTopicInt4: EXISTS
SubTopicInt5: EXISTS
SubTopicInt6: EXISTS
SubTopicBit4: EXISTS
SubTopicBit5: EXISTS
SubTopicBit6: EXISTS
SubTopicText4: EXISTS
SubTopicText5: EXISTS
SubTopicText6: EXISTS
HideSubTopicOnPublish: EXISTS
GridSortColumn: EXISTS
GridSortColumnOrder: EXISTS
TextEditor: EXISTS
CustomListTypeID: EXISTS
CustomListID: EXISTS

And when I loop through it:

OrdKeys = OrdDict.Keys
For i = 0 To OrdDict.Count - 1
    Response.Write OrdKeys(i) & ": " & OrdDict.Item(OrdKeys(i))
Next

I get:

error '80020009'

Can anyone see the flaw with my code? It's gotta be something obvious that I'm just not seeing.

2
dictionaryvbscriptasp-classic

2 Answers

1
votes

I was thinking I'd just delete the question since I figured out the problem and it's pretty trivial in my opinion...but just in case someone else hits this issue, here's what I had to do:

OrdDict.Add CStr(rsTopicOrdinals("COLUMN_NAME")), rsTopicOrdinals("ORDINAL_POSITION") - 1

I just wrapped the dictionary keys in a CStr and it worked. Whatever :/

1
votes

The problem you're facing is that you're fetching the location of the result, instead of the value. So when you're getting the item it looks at the position of the RS, but since you have moved to the next the position will give you the value of the new record instead of the original one. I am by no means an expert at exactly how it works, but I hope I get the point across.

What you need to do, and something which will save you some gray hairs down the line if you make a habit out of it, is to tell the dictionary to add the value of the result like this:

OrdDict.Add rsTopicOrdinals("COLUMN_NAME").value, rsTopicOrdinals("ORDINAL_POSITION").value - 1

What happens when you added CStr is that you created a new variable based on the location of the result, and that's why that worked for you.