Weird Array-related issue.

I'm not sure what's going on, but the following code conks out at the 4th iteration (of 12) and I can't figure out why.

Short version: I am trying to set up a table map ahead of creating a SQLite database. The data for this is pulled from the following declarations:

Code:

---

        ReDim TABLE_ACTUAL_NAME(COUNT_TABLE)
        ReDim TABLE_COLUMN_LIST(COUNT_TABLE)
        ReDim TABLE_COLUMN_TYPE(COUNT_TABLE)
        ReDim TABLE_CREATE_CODE(COUNT_TABLE)
        ReDim TABLE_DESCRIPTION(COUNT_TABLE)
        ReDim TABLE_DISPLAYNAME(COUNT_TABLE)
        ReDim TABLE_INDEXCOLUMN(COUNT_TABLE)

        ' ---[ Table 00 - DO NOT USE/COUNT! ]--------------------------------------------
        TABLE_ACTUAL_NAME(0) = flEncapsulate(MSG_MSC_INVALID, "[")
        TABLE_COLUMN_LIST(0) = MSG_MSC_INVALID
        TABLE_COLUMN_TYPE(0) = "NULL"
        TABLE_DESCRIPTION(0) = MSG_MSC_INVALID
        TABLE_DISPLAYNAME(0) = MSG_MSC_INVALID & " Table"
        TABLE_INDEXCOLUMN(0) = MSG_MSC_INVALID

        ' ---[ Table 01 - FILETYPE          ]--------------------------------------------
        TABLE_ACTUAL_NAME(1) = "FileType"
        TABLE_COLUMN_LIST(1) = "Extension|Description|MIME|Usage"
        TABLE_COLUMN_TYPE(1) = "VC4|VC40|VC80|VC1"
        TABLE_DESCRIPTION(1) = "Supported File Formats"
        TABLE_DISPLAYNAME(1) = "File Types"
        TABLE_INDEXCOLUMN(1) = "Extension"

        ' ---[ Table 02 - FORMAT            ]--------------------------------------------
        TABLE_ACTUAL_NAME(2) = "Format"
        TABLE_COLUMN_LIST(2) = "ID|Lock|Name|MediaClass|Digital|FileType"
        TABLE_COLUMN_TYPE(2) = "PK|BL0|VC25|IN|BL0|VC4"
        TABLE_DESCRIPTION(2) = "Media Formats"
        TABLE_DISPLAYNAME(2) = "Formats"
        TABLE_INDEXCOLUMN(2) = "ID"

        ' ---[ Table 03 - GENRE            ]--------------------------------------------
        TABLE_ACTUAL_NAME(3) = "Genre"
        TABLE_COLUMN_LIST(3) = "ID|Lock|Name|Audio|Digital|Image|Other|Print|Video"
        TABLE_COLUMN_TYPE(3) = "PK|BL0|VC30|BL0|BL0|BL0|BL0|BL0|BL0"
        TABLE_DESCRIPTION(3) = "Genres"
        TABLE_DISPLAYNAME(3) = "Genres"
        TABLE_INDEXCOLUMN(3) = "ID"

        ' ---[ Table 04 - HONORIFIC        ]--------------------------------------------
        TABLE_ACTUAL_NAME(4) = "Honorific"
        TABLE_COLUMN_LIST(4) = "ID|Lock|Title"
        TABLE_COLUMN_TYPE(4) = "PK|BL0|VC20"
        TABLE_DESCRIPTION(4) = "Titles and Honorifics"
        TABLE_DISPLAYNAME(4) = "Titles"
        TABLE_INDEXCOLUMN(4) = "Title"

        ' ---[ Table 05 - ISO-3166          ]--------------------------------------------
        TABLE_ACTUAL_NAME(5) = "ISO-3166"
        TABLE_COLUMN_LIST(5) = "ID|Lock|Name|Code"
        TABLE_COLUMN_TYPE(5) = "PK|BL0|VC50|VC2"
        TABLE_DESCRIPTION(5) = "Countries of the World (per ISO-3166)"
        TABLE_DISPLAYNAME(5) = "Countries"
        TABLE_INDEXCOLUMN(5) = "Code"

        ' ---[ Table 06 - MEDIA            ]--------------------------------------------
        TABLE_ACTUAL_NAME(6) = "Media"
        TABLE_COLUMN_LIST(6) = "ID|Lock|Name"
        TABLE_COLUMN_TYPE(6) = "PK|BL0|VC20"
        TABLE_DESCRIPTION(6) = "Source Media"
        TABLE_DISPLAYNAME(6) = "Media"
        TABLE_INDEXCOLUMN(6) = "ID"

        ' ---[ Table 07 - PERIOD            ]--------------------------------------------
        TABLE_ACTUAL_NAME(7) = "Period"
        TABLE_COLUMN_LIST(7) = "ID|Lock|Name|Start|End|Print|Music|Visual"
        TABLE_COLUMN_TYPE(7) = "PK|BL0|VC15|VC10|VC10|BL0|BL0|BL0"
        TABLE_DESCRIPTION(7) = "Creative Periods"
        TABLE_DISPLAYNAME(7) = "Periods"
        TABLE_INDEXCOLUMN(7) = "ID"

        ' ---[ Table 08 - SCHOLAR          ]--------------------------------------------
        TABLE_ACTUAL_NAME(8) = "Scholar"
        TABLE_COLUMN_LIST(8) = "ID|Lock|CatalogName|Scholar|Token"
        TABLE_COLUMN_TYPE(8) = "PK|BL0|VC60|VC80|VC15"
        TABLE_DESCRIPTION(8) = "Scholarly Catalogs"
        TABLE_DISPLAYNAME(8) = "Scholars"
        TABLE_INDEXCOLUMN(8) = "ID"

        ' ---[ Table 09 - SPARS            ]--------------------------------------------
        TABLE_ACTUAL_NAME(9) = "SPARS"
        TABLE_COLUMN_LIST(9) = "ID|Lock|Code"
        TABLE_COLUMN_TYPE(9) = "PK|BL0|VC3"
        TABLE_DESCRIPTION(9) = "SPARS Codes"
        TABLE_DISPLAYNAME(9) = "SPARS Codes"
        TABLE_INDEXCOLUMN(9) = "ID"

        ' ---[ Table 10 - TALENT            ]--------------------------------------------
        TABLE_ACTUAL_NAME(10) = "Talent"
        TABLE_COLUMN_LIST(10) = "ID|Honorific|GivenName|Surname|Suffix|Alias|ABorn|DOB|POB|ADeath|DOD|POD|Actor|Arranger|Author|Composer|ThematicCatalog|Conductor|Dancer|Director|Instrumentalist|Instrument|Narrator|Visual|Vocalist|VocalRegister|Other|WebSite"
        TABLE_COLUMN_TYPE(10) = "PK|VC20|VC50|VC50|VC10|VC50|BL0|VC10|VC40|BL0|VC10|VC40|BL0|BL0|BL0|BL0|IN|BL0|BL0|BL0|BL0|VC50|BL0|BL0|BL0|VC30|VC40|IN"
        TABLE_DESCRIPTION(10) = "Artistic Talents"
        TABLE_DISPLAYNAME(10) = "Artists"
        TABLE_INDEXCOLUMN(10) = "ID"

        ' ---[ Table 11 - VOICE            ]--------------------------------------------
        TABLE_ACTUAL_NAME(11) = "Voice"
        TABLE_COLUMN_LIST(11) = "ID|Lock|Name"
        TABLE_COLUMN_TYPE(11) = "PK|BL0|VC30"
        TABLE_DESCRIPTION(11) = "Vocal ranges and registers"
        TABLE_DISPLAYNAME(11) = "Vocal Registers"
        TABLE_INDEXCOLUMN(11) = "ID"

        ' ---[ Table 12 - WEBSITE          ]--------------------------------------------
        TABLE_ACTUAL_NAME(12) = "Website"
        TABLE_COLUMN_LIST(12) = "ID|Lock|Name|URL|Secure"
        TABLE_COLUMN_TYPE(12) = "PK|BL0|VC60|VC250|BL0"
        TABLE_DESCRIPTION(12) = "Reference Web Sites"
        TABLE_DISPLAYNAME(12) = "Web Sites"
        TABLE_INDEXCOLUMN(12) = "ID"

---

The part in question is where TABLE_COLUMN_LIST/TABLE_COLUMN_TYPE are processed; the variable, COUNT_TABLE is declared as 12:

Code:

---

  Public Sub GenerateTableCode()
      Dim __arrNames() As Array
      Dim __arrNamesExploded() As String
      Dim __arrTypes() As Array
      Dim __arrTypesExploded() As String
      Dim __strColumnDefinition As String
      Dim __strFullTableMap As String

      For __TableCount = 0 To COUNT_TABLE - 1
          __strFullTableMap = ""
          __arrNamesExploded = TABLE_COLUMN_LIST(__TableCount).Split("|"c)
          __arrTypesExploded = TABLE_COLUMN_TYPE(__TableCount).Split("|"c)

          ReDim __arrNames(UBound(__arrNamesExploded))
          ReDim __arrTypes(UBound(__arrTypesExploded))
          __arrNames(__TableCount) = flTableGenGetElements(__arrNamesExploded(__TableCount))
          __arrTypes(__TableCount) = flTableGenGetElements(__arrTypesExploded(__TableCount))

          For __ColumnCount = 0 To UBound(__arrNames)
              __strColumnDefinition = flEncapsulate(__arrNamesExploded(__ColumnCount), "[") & " " & flTableGenExpandType(__arrTypesExploded(__ColumnCount))
              If __ColumnCount < UBound(__arrNames) Then __strColumnDefinition = __strColumnDefinition & ","
              __strFullTableMap = __strFullTableMap & __strColumnDefinition
          Next
          TABLE_CREATE_CODE(__TableCount) = __strFullTableMap

      Next __TableCount

---

The flTableGenElements and flTableGenExpandType functions are:

Code:

---

    Public Function flTableGenGetElements(ByVal _strElementList As String) As String()
        Dim __arrList() As String
        __arrList = _strElementList.Split("|"c)
        Return __arrList
    End Function

    Public Function flTableGenExpandType(ByVal _strColumnCode As String) As String
        Dim __strColumnType As String

        Select Case Left(UCase(_strColumnCode), 2)
            Case "BB"
                __strColumnType = "BLOB"
            Case "BI"
                __strColumnType = "BIGINT"
            Case "BL"
                __strColumnType = IIf(Len(_strColumnCode) = 2, "BOOLEAN DEFAULT (0)", "BOOLEAN DEFAULT (" & Mid(_strColumnCode, 3) & ")")
            Case "BU"
                __strColumnType = "UNSIGNED BIG INT"
            Case "CH"
                __strColumnType = "CHARACTER(" & Mid(_strColumnCode, 3) & ")"
            Case "CL"
                __strColumnType = "CLOB"
            Case "DA"
                __strColumnType = "DATE"
            Case "DB"
                __strColumnType = "DOUBLE"
            Case "DE"
                __strColumnType = IIf(Len(_strColumnCode) = 2, "DECIMAL", "DECIMAL (" & Mid(_strColumnCode, 3) & ")")
            Case "DP"
                __strColumnType = "DOUBLE PRECISION"
            Case "DT"
                __strColumnType = "DATETIME"
            Case "FL"
                __strColumnType = "FLOAT"
            Case "I2"
                __strColumnType = "INT2"
            Case "I8"
                __strColumnType = "INT8"
            Case "IM"
                __strColumnType = "MEDIUMINT"
            Case "IN"
                __strColumnType = "INTEGER"
            Case "IS"
                __strColumnType = "SMALLINT"
            Case "IT"
                __strColumnType = "TINYINT"
            Case "NC"
                __strColumnType = "NATIVE CHARACTER(" & Mid(_strColumnCode, 3) & ")"
            Case "NO"
                __strColumnType = "NUMERIC"
            Case "NV"
                __strColumnType = "NVARCHAR(" & Mid(_strColumnCode, 3) & ")"
            Case "PK"
                __strColumnType = "INTEGER PRIMARY KEY AUTOINCREMENT"
            Case "RE"
                __strColumnType = "REAL"
            Case "TE", "TX"
                __strColumnType = "TEXT"
            Case "VA"
                __strColumnType = "VARYING CHARACTER(" & Mid(_strColumnCode, 3) & ")"
            Case "VC"
                __strColumnType = "VARCHAR(" & Mid(_strColumnCode, 3) & ")"
            Case Else
                __strColumnType = "NULL"
        End Select

        Return __strColumnType
    End Function

---

The flEncapsulate function encloses the passed string with whatver token is passed to it (L/R applied as necessary, such as with the square brackets in this implementation).

My issue is that in the 4th iteration of the loop, I get an "System.IndexOutOfRangeException: 'Index was outside the bounds of the array.'" error. The array sizes correspond to the extrapolated sizes, etc. I have "played swapsies" between datasets (eg. switched table 4 with one of the other 11) to no avail. It always fails at the 4th iteration.

What I am looking to ending up with is:

__arrNames holding an exploded list of column names for the current iteration; it gets reset for the next iteration.
__arrTypes holding an exploded list of column types for the current iteration; it gets reset for the next iteration.
TABLE_CREATE_CODE(__TableCount) holding a construct derived from the two exploded lists (i.e. [ID] INTEGER PRIMARY KEY AUTOINCREMENT,[Lock] BOOLEAN DEFAULT (0),[Name] VARCHAR(25),[MediaClass] INTEGER,[Digital] BOOLEAN DEFAULT (0),[FileType] VARCHAR(4) in the case of Table 02), and I am ending up with this for TABLE_CREATE_CODE(0-3), but then the error occurs.

Any idea why, and what I need to do to fix it?

Quote:

---

I'm not sure what's going on, but the following code conks out at the 4th iteration (of 12) and I can't figure out why.

---

Nitpicking: It's the 5th iteration of 13 tables in total --> It's at "Index" 4 (Table "Honorifics")
-->

Quote:

---

and I am ending up with this for TABLE_CREATE_CODE(0-3), but then the error occurs.

---

As to your issue: It's called "debugging" for a reason.
Your Error says "Index out of bound".
Well, then, You ARE out of bounds.
Go step by step through it, and look at your debugging-output, watch-window, whatever you use...

FWIW, i've always been laughed at, why i keep my SQL-Code outside the Frontend.
In your case: Use a third-party SQLite-Client (e.g. DB Browser for SQLite), create your table in there, in such a client you actually can copy the SQL-Create-Statement for a Table despite having only used the visual tools,
put that statement in a Textfile (e.g. "Create_Table_Honorific.sql"), on first start read that file, throw it at your SQlite-Connection, Execute, done.

Ne need to jump through burning hoops in Frontend-code

Quote:

---

Originally Posted by Zvoni

Nitpicking: It's the 5th iteration of 13 tables in total --> It's at "Index" 4 (Table "Honorifics")

---

I know where it is, not WHY it is!

Quote:

---

Originally Posted by Zvoni

As to your issue: It's called "debugging" for a reason.

---

No need to be snarky! Statements like this are what gives support forums a bad reputation. ...but whatever.

Quote:

---

Originally Posted by Zvoni

FWIW, i've always been laughed at, why i keep my SQL-Code outside the Frontend.
In your case: Use a third-party SQLite-Client (e.g. DB Browser for SQLite), create your table in there, in such a client you actually can copy the SQL-Create-Statement for a Table despite having only used the visual tools,
put that statement in a Textfile (e.g. "Create_Table_Honorific.sql"), on first start read that file, throw it at your SQlite-Connection, Execute, done.

Ne need to jump through burning hoops in Frontend-code

---

...which misses the entire point of doing what I'm doing!

In the first place, there are reasons why the database gets created on the fly. Sure, creating it externally to have it available has its benefits, but that isn't always the case. I posted because I'm stuck and need help with the specific issue.

Quote:

---

Originally Posted by Erlkoenig

I know where it is, not WHY it is!

---

It's not about you, but the others here, who read your description.
I was looking at your Index "3" ("Genre"), while the following Index is at fault

Quote:

---

No need to be snarky! Statements like this are what gives support forums a bad reputation. ...but whatever.

---

Funny how you quote the "offending" part, but totally ignore the important part following it

Quote:

---

Your Error says "Index out of bound".
Well, then, You ARE out of bounds.
Go step by step through it, and look at your debugging-output, watch-window, whatever you use...

---

Quote:

---

...which misses the entire point of doing what I'm doing!

In the first place, there are reasons why the database gets created on the fly. Sure, creating it externally to have it available has its benefits, but that isn't always the case. I posted because I'm stuck and need help with the specific issue.

---

And you missed the point i was trying to tell you: i never said, to KEEP that externally created Database, but to use it as a "cheatcode".
You arrive in your Frontend-code at the position you want to create the Database on the Fly: Load the Textfile.......

EDIT: Or if you really don't want to lug around several textfiles, i'd include them as ressourcestrings, and then load them from there.
Because, as far as i can see it, those are "static" Strings

EDIT: FWIW
This

Code:

---

For __TableCount = 0 To COUNT_TABLE - 1

---

ignores the last table "Website"

As has been said, step through your code. We know some index is out of bounds, but debugging will show you where that is either set wrong initially, or being changed somewhere you didn’t intend it to…

The underlying issue is the use of __TableCount when getting __arrTypesExploded. The first three iterations work, but only by accident.

A better solution would be to create a class to represent the various arrays and collections to store the properties that represent collections. For that matter, you're using an awful lot of legacy Visual Basic here when in reality it'd be better if you took a more .NET approach. For example, assume you have these classes:

Code:

---

Public Class DatabaseDefinition

    Public ReadOnly Property Tables As IReadOnlyList(Of DatabaseTableDefinition)
   
    Public Sub New(_tables As IEnumerable(Of DatabaseTableDefinition))
        If (_tables Is Nothing) Then
            Throw New ArgumentNullException(NameOf(_tables))
        End If

        Tables = _tables.ToList().AsReadOnly()
    End Sub
   
End Class

Public Class DatabaseTableDefinition

    Public ReadOnly Property TableName As String
    Public ReadOnly Property DisplayName As String
    Public ReadOnly Property Description As String
    Public ReadOnly Property ColumnDefinitions As IReadOnlyList(Of DatabaseColumnDefinition)
    Public ReadOnly Property IndexColumnName As String
   
    Public Sub New(_tableName As String, _displayName As String, _description As String, _columnDefinitions As IEnumerable(Of DatabaseColumnDefinition), _indexColumnName As String)
        If (String.IsNullOrWhiteSpace(_tableName)) Then
            Throw New ArgumentNullException(NameOf(tableName))
        End If
        If (String.IsNullOrWhiteSpace(_displayName)) Then
            Throw New ArgumentNullException(NameOf(displayName))
        End If
        If (String.IsNullOrWhiteSpace(_description)) Then
            Throw New ArgumentNullException(NameOf(description))
        End If
        If (_columnDefinitions Is Nothing OrElse Not _columnDefinitions.Any()) Then
            Throw New ArgumentNullException(NameOf(columnDefinitions))
        End If
        If (Not _columnDefinitions.Any(Function(c) c.ColumnName.Equals(_indexColumnName, StringComparison.OrdinalIgnoreCase))) Then
            Throw New ArgumentOutOfRangeException(NameOf(_indexColumnName))
        End If
       
        TableName = _tableName
        DisplayName = _displayName
        Description = _description
        ColumnDefinitions = _columnDefinitions.OrderBy(Function(column) column.OrdinalIndex).ToList()
        IndexColumnName = _indexColumnName
    End Sub

End Class

Public Class DatabaseColumnDefinition

    Public ReadOnly Property ColumnName As String
    Public ReadOnly Property ColumnType As String
    Public ReadOnly Property OrdinalIndex As Integer
   
    Public Sub New(_columnName As String, _columnType As String, _ordinalIndex As Integer)
        If (String.IsNullOrWhiteSpace(_columnName)) Then
            Throw New ArgumentNullException(NameOf(_columnName))
        End If
        If (String.IsNullOrWhiteSpace(_columnType)) Then
            Throw New ArgumentNullException(NameOf(_columnType))
        End If
        If (_ordinalIndex < 0) Then
            Throw New ArgumentOutOfRangeException(NameOf(_ordinalIndex))
        End If
        ColumnName = _columnName
        ColumnType = _columnType
        OrdinalIndex = _ordinalIndex
    End Sub

End Class

---

You could replace your mapping code with a factory:

Code:

---

Public Module DatabaseDefinitionFactory

    Public Function CreateDefault() As DatabaseDefinition
        Return New DatabaseDefinition({
            CreateFileTypeTable(),
            CreateFormatTable(),
            CreateGenreTable(),
            CreateHonorificTable(),
            CreateIso3166Table(),
            CreateMediaTable(),
            CreatePeriodTable(),
            CreateScholarTable(),
            CreateSparsTable(),
            CreateTalentTable(),
            CreateVoiceTable(),
            CreateWebsiteTable()
        })
    End Function

    Private Function CreateFileTypeTable() As DatabaseTableDefinition
        Return New DatabaseTableDefinition(
            "FileType",
            "File Types",
            "Supported File Formats",
            {
                New DatabaseColumnDefinition("Extension", "VC4", 0),
                New DatabaseColumnDefinition("Description", "VC40", 1),
                New DatabaseColumnDefinition("MIME", "VC80", 2),
                New DatabaseColumnDefinition("Usage", "VC1", 3)
            },
            "Extension")
    End Function
       
    Private Function CreateFormatTable() As DatabaseTableDefinition
        ' etc... removed for brevity
    End Function

    Private Function CreateGenreTable() As DatabaseTableDefinition
    End Function

    Private Function CreateHonorificTable() As DatabaseTableDefinition
    End Function

    Private Function CreateIso3166Table() As DatabaseTableDefinition
    End Function

    Private Function CreateMediaTable() As DatabaseTableDefinition
    End Function

    Private Function CreatePeriodTable() As DatabaseTableDefinition
    End Function

    Private Function CreateScholarTable() As DatabaseTableDefinition
    End Function

    Private Function CreateSparsTable() As DatabaseTableDefinition
    End Function

    Private Function CreateTalentTable() As DatabaseTableDefinition
    End Function

    Private Function CreateVoiceTable() As DatabaseTableDefinition
    End Function

    Private Function CreateWebsiteTable() As DatabaseTableDefinition
    End Function

End Module

---

Now the table generation become much simpler:

Code:

---

Public Module SqliteTableScriptGenerator

    Public Function GenerateCreateDatabaseSql(database As DatabaseDefinition) As IReadOnlyList(Of String)
        If (database Is Nothing) Then
            Throw New ArgumentNullException(NameOf(database))
        End If

        Return database.Tables.
            Select(Function(table) GenerateCreateTableSql(table)).
            ToList().
            AsReadOnly()
    End Function

    Private Function GenerateCreateTableSql(table As DatabaseTableDefinition) As String
        If (table Is Nothing) Then
            Throw New ArgumentNullException(NameOf(table))
        End If

        Dim columnSql = table.ColumnDefinitions.
            Select(Function(column)
                      Dim columnName As String = EscapeIdentifier(column.ColumnName)
                      Dim columnType As String = ExpandColumnType(column.ColumnType)
                      Return $"{columnName} {columnType}"
                  End Function)

        Dim tableName As String = EscapeIdentifier(table.TableName)
        Dim joinedColumnsSql As String = String.Join(", ", columnSql)
        Return $"CREATE TABLE IF NOT EXISTS {tableName} ({joinedColumnsSql});"
    End Function

    Private Function EscapeIdentifier(identifier As String) As String
        If (String.IsNullOrWhiteSpace(identifier)) Then
            Throw New ArgumentNullException(NameOf(identifier))
        End If

        Dim doubleBrackedIdentifier As String = identifier.Replace("]", "]]")
        Return $"[{doubleBrackedIdentifier}]"
    End Function

    Private Function ExpandColumnType(columnTypeCode As String) As String
        If (String.IsNullOrWhiteSpace(columnTypeCode)) Then
            Throw New ArgumentNullException(NameOf(columnTypeCode))
        End If

        Dim normalizedCode As String = columnTypeCode.ToUpperInvariant()
        Dim prefix As String = normalizedCode.Substring(0, Math.Min(2, normalizedCode.Length))
        Dim suffix As String = If(normalizedCode.Length > 2, normalizedCode.Substring(2), String.Empty)

        Select Case prefix
            Case "BB"
                Return "BLOB"
            Case "BI"
                Return "BIGINT"
            Case "BL"
                Return If(String.IsNullOrWhiteSpace(suffix), "BOOLEAN DEFAULT (0)", $"BOOLEAN DEFAULT ({suffix})")
            Case "BU"
                Return "UNSIGNED BIG INT"
            Case "CH"
                Return $"CHARACTER({suffix})"
            Case "CL"
                Return "CLOB"
            Case "DA"
                Return "DATE"
            Case "DB"
                Return "DOUBLE"
            Case "DE"
                Return If(String.IsNullOrWhiteSpace(suffix), "DECIMAL", $"DECIMAL({suffix})")
            Case "DP"
                Return "DOUBLE PRECISION"
            Case "DT"
                Return "DATETIME"
            Case "FL"
                Return "FLOAT"
            Case "I2"
                Return "INT2"
            Case "I8"
                Return "INT8"
            Case "IM"
                Return "MEDIUMINT"
            Case "IN"
                Return "INTEGER"
            Case "IS"
                Return "SMALLINT"
            Case "IT"
                Return "TINYINT"
            Case "NC"
                Return $"NATIVE CHARACTER({suffix})"
            Case "NO"
                Return "NUMERIC"
            Case "NV"
                Return $"NVARCHAR({suffix})"
            Case "PK"
                Return "INTEGER PRIMARY KEY AUTOINCREMENT"
            Case "RE"
                Return "REAL"
            Case "TE", "TX"
                Return "TEXT"
            Case "VA"
                Return $"VARYING CHARACTER({suffix})"
            Case "VC"
                Return $"VARCHAR({suffix})"
            Case Else
                Throw New NotSupportedException($"Unsupported column type code: {columnTypeCode}")
        End Select
    End Function

End Module

---

By taking this approach, not only do you get rid of all the legacy Visual Basic code, but it makes each method handle a very specific responsibility and eliminates the risk of running running into an IndexOutOfRangeException.

Here's an example of it in action: https://dotnetfiddle.net/36RDf0