vb.net array

I have an array created like this:
Dim StrArray() As String = Split(drAccessRecord(FieldDescription))

Ive extendedmy access record data by an additional column:
VehCatValue = drAccessRecord(VehCatClient)

for each word in the array I would like to save that vehcatvalue

not sure how to extend the array to do that or even if i have to do it that way.

Ive tried and in some cases it does the insert in others it doesnt, not sure why.

dtaccessRecord is all populated row wise so its that last section within the for loop i think.

the column in the sql server db is called VehCategory (nvarchar(max), null)

Public Sub CreateWords()
        Dim StrClientName As String
        Dim x As Boolean
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim index As Long
        Dim StrClientCodeWordPos As String
        Dim StrClientCode As String

        Dim StrWord As String
        Dim intWordLen As Int16
        Dim StrFull As String
        Dim IntWordPosition As Int16
        Dim IntNoOfWords As Int16

        Try
            StrClientName = ""

            'BackColor = Color.FromArgb(107, 115, 115)
            'Me.Refresh()

            If Form1.RadioButton1.Checked = True Then
                StrClientName = "Smmt"
            End If

            If Form1.RadioButton2.Checked = True Then
                StrClientName = "Abi"
            End If


            If Form1.RadioButton3.Checked = True Then
                StrClientName = "Cap"
            End If

            If Form1.RadioButton4.Checked = True Then
                StrClientName = "Glass"
            End If

            If Form1.RadioButton5.Checked = True Then
                StrClientName = "Tvi"
            End If

            con.ConnectionString = "Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "DELETE FROM [dbo].[TblWords] where ClientName= @ClientName"
            cmd.Parameters.Add(New SqlParameter("@ClientName", StrClientName))
            cmd.ExecuteNonQuery()
            con.Close()

            Dim dtRecordsFromAccess As DataTable
            Dim FieldNameClientCode As String
            Dim FieldDescription As String
            Dim VehCatClient As String
            Dim VehCatValue As String

            Select Case StrClientName
                Case "Smmt"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\SMMT\smmt master.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [MVRIS CODE], [Model Range] & ' ' & [variant] as ConCatDesc, [Vehicle Category Code] FROM smmt;"
                            'cmdSelect.CommandText = "SELECT [MVRIS CODE], [Model Range] & ' ' & [variant] as ConCatDesc, [Vehicle Category Code] FROM smmt;"

                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using
                    FieldNameClientCode = "MVRIS CODE"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "Vehicle Category Code"

                Case "Abi"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Abi\Abi Master.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [AbiCode], [MODEL_DESCRIPTION] as ConCatDesc, Abi_Cat FROM TClient ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using

                    FieldNameClientCode = "abiCode"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "Abi_Cat"

                Case "Cap"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Cap\Cap Master.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [CAPid_CAPcat], [LongModelDescription] & ' ' & [ShortDerivativeDescription] as ConCatDesc, CAP_cat FROM CAPDATA ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using

                    FieldNameClientCode = "CAPid_CAPcat"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "CAP_Cat"

                Case "Glass"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Glass\Glass Master 10.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [GLASSid_GLASScat], [Range_desc] & ' ' & [Trim_desc] as ConCatDesc, GLASS_cat FROM  [Glass Full Table] ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using

                    FieldNameClientCode = "GLASSid_GLASScat"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "GLASS_cat"

                Case "Tvi"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Thatcham\Tvi Master 4.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [DERIVATIVE_CODE], [Model]  & ' ' & [Trim] as ConCatDesc, TVIVehicleTyep FROM TVIDATA ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using
                    FieldNameClientCode = "DERIVATIVE_CODE"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "TVIVehicleTyep"

            End Select

            Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
                Using cmdInsert As New SqlClient.SqlCommand
                    cmdInsert.Connection = cnSql
                    cnSql.Open()
                    Dim y As Long
                    Dim Z As Double
                    Dim Counter As Long

                    y = dtRecordsFromAccess.Rows.Count
                    For Each drAccessRecord As DataRow In dtRecordsFromAccess.Rows
                        Dim StrArray() As String = Split(drAccessRecord(FieldDescription))
                        Form1.ProgressBar1.Step = 1
                        Form1.ProgressBar1.Minimum = 1
                        Form1.ProgressBar1.Maximum = y
                        For index = LBound(StrArray) To UBound(StrArray)

                            StrClientCodeWordPos = drAccessRecord(FieldNameClientCode) & "_" & RemoveUnwantedChr(StrArray(index)) & "_" & index + 1
                            StrClientCode = drAccessRecord(FieldNameClientCode)
                            StrFull = RemoveUnwantedChr(drAccessRecord(FieldDescription))
                            StrClientName = StrClientName
                            StrWord = RemoveUnwantedChr(StrArray(index))
                            intWordLen = Len(StrArray(index))
                            IntWordPosition = index + 1
                            IntNoOfWords = UBound(StrArray) + 1
                            VehCatValue = drAccessRecord(VehCatClient)
                            'Insert records in SQL database
                            cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords, VehCategory) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & ", '" & VehCatValue & "')"
                            'cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & "')"
                            cmdInsert.ExecuteNonQuery()

                        Next index

                        Form1.ProgressBar1.PerformStep()
                        Form1.Label3.Text = "# of Files Read = " & Math.Round((Form1.ProgressBar1.Value.ToString / y) * 100, 2) & "%"
                        Form1.Label3.Refresh()
                    Next
                End Using
            End Using

        Catch ex As Exception


        Finally

            con.Close()
        End Try
    End Sub

Select all Open in new window