VB6 map string to integer for headers

Question

I'm trying to parse a CSV File into a VB6 application in order to update multiple records on a table on SQL with existing single record updating code already in the form. The CSV Files will have a header row whixh can be used to validate the information going into the correct place in the ADODB recordset. In C++ you can use a map to say like

map<String s, int x> column 
 column<"First Name", -1>
 column<"Last Name",-1>

Then create a counter across the comma delimited values where if the third value is Last Name then the code could be written to change column<"Last Name",-1> to column<"Last Name",3> and if x != -1 in any of the maps the file is valid for use, I would then loop through the remaining records and parse into a container using something similar to

strLastName = Array<column[3]>

to assign the record values to the correct variables. I am still very new to VB6, how can I accomplish something similar in VB6 and what containers should be used? So far I have

Public Sub GetImportValues()
On Error GoTo GetImportValues_Error:
Dim intFileNum As Integer

Open Path For Input As #intFileNum

Do Until EOF(intFileNum)

Line Input #intFileNum, vbCrLf

FunctionThatSavesInformationToSQL

Loop

Close #intFileNum



GetImportValues_Exit:
    Exit Sub

GetImportValues_Error:
    Err.Source = "frmMemberAdd.GetImportValues" & " | " & Err.Source
    Err.Raise Err.Number, Err.Source, Err.Description
End Sub

with a dialog box returning the path as a string using App.path in a separate Function

*****************************************************Slight change to answer The collection was on track for what I had asked but I did have to change it to dictionary because you cannot return items on a collection which kept me from comparing the items and changing the keys but dictionary can. Make sure if you use dictionary you switch the item and key.

Answer 1

If I understand your question correctly, you're trying to create a map (Dictionary<string, int> in C#). In VB6, you can use Collection for this purpose - it's roughly equivalent to C#'s Dictionary<string, object>. It uses String keys and stores all values as Variant. For example:

Dim oColl As Collection

Set oColl = New Collection
oColl.Add -1, "ColumnName"

Dim nColumnIndex As Long

'Get column index for column name.
nColumnIndex = oColl.Item("ColumnName")

If nColumnIndex = -1 Then
    nColumnIndex = ...

    'When you want to update a column index in the collection, you
    'first have to remove the item and then add it back with the right
    'index.
    oColl.Remove "ColumnName"
    oColl.Add nColumnIndex, "ColumnName"
End If

Edit 1:

One word of warning regarding VB6: you'll see many samples doing this:

Dim oObj As New SomeClass

It's ok to do this in VB.Net but don't ever do this in VB6. Declare and instantiate the object on separate statements because the single-statement form generates code where oObj is checked for Nothing and set to an instance before each use. This slows down your code (unnecessary checks) and creates hard-to-find bugs if you're using an instance that's supposed to be gone.

Always do this instead:

Dim oObj As SomeClass
Set oObj = New SomeClass
...

'Clean up the object when you're done with it. Remember, there's
'no garbage collection in COM / VB6, you have to manage object
'lifetimes.
Set oObj = Nothing

Also, use Long instead of Integer as much as you can - Long is a 32-bit integer, while Integer is only 16-bits. VB6 type names can be misleading frequently. Here's an old answer of mine with a bit more detail (not strictly related to your question but useful).

Alternatively, you can create a simplified wrapper around the .NET Dictionary class and expose it as a COM object: this would allow you to call it from VB6. This would likely be (somewhat) slower than Collection and it'd require the .NET Framework for your VB6 project to run.

Edit 2:

As @CMaster commented, Dictionary is available from the Microsoft Scripting Runtime library - you need to add a reference to it to use it (this is why I prefer Collection - it has no dependency). This answer has details about how to use it.