We help IT Professionals succeed at work.

visual studio vb.net windows onkeyup

174 Views
Last Modified: 2017-04-09
I have a form on the third tab resides a datagrid (left side) with searchbox and some options this part all works i can search and the rows in the grid get highlighted. on the right side of the form is a listbox with a search textbox. The search on this doesnt work although the listbox does get populated. It also wont let me select any of the items in the list despite multisimple being on. the listbox uses onekeyupbulk. I cannot see why the search wont work.

ex
form1 vb
Imports System
Imports System.Text.RegularExpressions
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Runtime.CompilerServices
Imports System.ComponentModel

Public Class Form1
    Dim AllListBox As New List(Of ListBox)()
    Private dtWords As DataTable




    Private Sub OnKeyUpBulk(sender As Object, e As KeyEventArgs)
        Dim index = -1
        If TypeOf sender Is TextBox Then
            Dim tb = DirectCast(sender, TextBox)
            If tb.Equals(TxtBoxSearchBulk) Then
                If e.KeyCode.Equals(Keys.Enter) Then
                    index = ListBulk.FindString(tb.Text)
                    If index <> -1 Then
                        ListBulk.SetSelected(index, True)
                    End If
                    tb.Clear()
                    e.Handled = True
                End If
            End If
        ElseIf TypeOf sender Is ListBox Then
            Dim lb = DirectCast(sender, ListBox)
            If lb.Equals(ListBulk) Then
                If e.KeyCode.Equals(Keys.Enter) Then
                    index = lb.FindString(TxtBoxSearchBulk.Text)
                    If index <> -1 Then
                        lb.SetSelected(index, True)
                    End If
                    TxtBoxSearchBulk.Clear()
                    e.Handled = True
                ElseIf e.KeyCode.Equals(Keys.Back) Then
                    If TxtBoxSearchBulk.Text.Length > 0 Then
                        TxtBoxSearchBulk.Text = TxtBoxSearchBulk.Text.Substring(0, TxtBoxSearchBulk.Text.Length - 1)
                    End If
                Else
                    TxtBoxSearchBulk.Text = TxtBoxSearchBulk.Text + Convert.ToChar(e.KeyCode)
                End If
            End If
        End If
    End Sub

    Public Sub FindAllListBox(ByVal ctrl As Control)

        For Each c As Control In ctrl.Controls
            If TypeOf c Is ListBox Then
                AllListBox.Add(c)
            Else
                If c.Controls.Count > 0 Then
                    FindAllListBox(c)
                End If
            End If
        Next

    End Sub
    Public Sub TagBulkWords()

        Dim StrVehCat
        Dim StrVehSupCat As String
        Dim connectionString As String = "Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True"
        Dim StrProcName As String
        StrProcName = ""
        StrVehCat = ""
        StrVehSupCat = ""
        If OpShortDescCarsAll.Checked = True Or OpShortDescBikesAll.Checked = True Or OpShortDescLcvAll.Checked = True Or OpShortDescOthersAll.Checked = True Then
            If OpBulkTag.Checked = True Then
                StrProcName = "usp_InsertBulkTagWordsVehCat"
            Else
                StrProcName = "usp_DeleteBulkTagWordsVehCat"
            End If


            If OpShortDescCarsAll.Checked = True Then
                StrVehCat = "Car"
            End If
            If OpShortDescBikesAll.Checked = True Then
                StrVehCat = "Motorcycle"
            End If
            If OpShortDescLcvAll.Checked = True Then
                StrVehCat = "Lcv"
            End If
            If OpShortDescOthersAll.Checked = True Then
                StrVehCat = "Others"
            End If

        End If

        If OpShortDescCarsLcvOthersAll.Checked = True Or OpShortDescBikesQuadsAll.Checked = True Then
            If OpBulkTag.Checked = True Then
                StrProcName = "usp_InsertBulkTagWordsSuper"
            Else
                StrProcName = "usp_DeleteBulkTagWordsSuper"
            End If


            If OpShortDescCarsLcvOthersAll.Checked = True Then
                StrVehSupCat = "CarLcvOthers"
            End If

            If OpShortDescBikesQuadsAll.Checked = True Then
                StrVehSupCat = "BikesQuads"
            End If

        End If

        If OpShortDescAllAll.Checked = True Then
            If OpBulkTag.Checked = True Then
                StrProcName = "usp_InsertBulkTagWords"
            Else
                StrProcName = "usp_DeleteBulkTagWords"
            End If
        End If

        Using conTagBulk As New SqlConnection(connectionString)

            For Each Item In ListBulk.SelectedItems
                If Not String.IsNullOrWhiteSpace(Item) Then
                    Using cmdTagBulk As New SqlCommand(StrProcName, conTagBulk)
                        cmdTagBulk.CommandType = CommandType.StoredProcedure
                        conTagBulk.Open()
                        cmdTagBulk.Parameters.AddWithValue("@Word", Item.ToString())
                        If OpShortDescCarsAll.Checked = True Or OpShortDescBikesAll.Checked = True Or OpShortDescLcvAll.Checked = True Or OpShortDescOthersAll.Checked = True Then
                            cmdTagBulk.Parameters.AddWithValue("@VCategory", StrVehCat)
                        ElseIf OpShortDescCarsLcvOthersAll.Checked = True Or OpShortDescBikesQuadsAll.Checked = True Then
                            cmdTagBulk.Parameters.AddWithValue("@VCatSuper", StrVehSupCat)
                        ElseIf OpShortDescAllAll.Checked = True Then

                        End If
                        cmdTagBulk.ExecuteNonQuery()
                        conTagBulk.Close()
                    End Using
                Else
                    'do nothing whitespace
                End If
            Next
        End Using


        Call DataGroupCallBulk()

        If OpBulkTag.Checked = True Then
            MessageBox.Show("Finished Bulk Tagging.")
        Else
            MessageBox.Show("Finished Bulk Un-Tagging.")
        End If
    End Sub


    Public Function GetDataBulk() As DataTable
        Dim StrProcName As String
        Dim StrVehCat As String
        StrProcName = ""
        StrVehCat = ""

        If OpShortDescCarsAll.Checked = True Or OpShortDescBikesAll.Checked = True Or OpShortDescLcvAll.Checked = True Or OpShortDescOthersAll.Checked = True Then
            If OpBulkTag.Checked = True Then
                StrProcName = "usp_GetBulkWordsVehCat"
            Else
                StrProcName = "usp_GetBulkTagWordsVehCat"
            End If
            If OpShortDescCarsAll.Checked = True Then
                StrVehCat = "Car"
            End If
            If OpShortDescBikesAll.Checked = True Then
                StrVehCat = "Motorcycle"
            End If
            If OpShortDescLcvAll.Checked = True Then
                StrVehCat = "Lcv"
            End If
            If OpShortDescOthersAll.Checked = True Then
                StrVehCat = "Others"
            End If

        End If

        If OpShortDescCarsLcvOthersAll.Checked = True Or OpShortDescBikesQuadsAll.Checked = True Then
            If OpBulkTag.Checked = True Then
                StrProcName = "usp_GetBulkWordsSuper"
            Else
                StrProcName = "usp_GetBulkTagWordsSuper"
            End If
            If OpShortDescCarsLcvOthersAll.Checked = True Then
                StrVehCat = "CarLcvOthers"
            End If

            If OpShortDescBikesQuadsAll.Checked = True Then
                StrVehCat = "BikesQuads"
            End If

        End If

        If OpShortDescAllAll.Checked = True Then
            If OpBulkTag.Checked = True Then
                StrProcName = "usp_GetBulkWords"
            Else
                StrProcName = "usp_GetBulkTagWords"
            End If
        End If

        dtWords = New DataTable
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString

        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()


                If OpShortDescCarsAll.Checked = True Or OpShortDescBikesAll.Checked = True Or OpShortDescLcvAll.Checked = True Or OpShortDescOthersAll.Checked = True Then
                    cmd.Parameters.AddWithValue("@VCategory", StrVehCat)

                ElseIf OpShortDescCarsLcvOthersAll.Checked = True Or OpShortDescBikesQuadsAll.Checked = True Then
                    cmd.Parameters.AddWithValue("@VCatSuper", StrVehCat)

                ElseIf OpShortDescAllAll.Checked = True Then

                End If



                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtWords.Load(reader)
            End Using
        End Using
        Return dtWords
    End Function

    Public Function GetBulkDataStrings() As DataTable
        Dim StrProcName As String
        Dim StrVehCat As String
        Dim Index As Int16
        StrProcName = ""
        StrVehCat = ""

        Index = 0
        If OpShortDescCarsAll.Checked = True Or OpShortDescBikesAll.Checked = True Or OpShortDescLcvAll.Checked = True Or OpShortDescOthersAll.Checked = True Then

            If OpShortDescCarsAll.Checked = True Then
                StrVehCat = "Car"
            End If
            If OpShortDescBikesAll.Checked = True Then
                StrVehCat = "Motorcycle"
            End If
            If OpShortDescLcvAll.Checked = True Then
                StrVehCat = "Lcv"
            End If
            If OpShortDescOthersAll.Checked = True Then
                StrVehCat = "Others"
            End If

            If OPBulkDescView1.Checked Then
                StrProcName = "usp_GetBulkDescStringsVehCat"
            End If

            If OPBulkDescView2.Checked Then
                StrProcName = "usp_GetBulkDescStringsVehCatView2"
            End If

            If OPBulkDescView3.Checked Then
                StrProcName = "usp_GetBulkDescStringsVehCatView3"
            End If

            If OPBulkDescView4.Checked Then
                StrProcName = "usp_GetBulkDescStringsVehCatView4"
            End If



        End If

        If OpShortDescCarsLcvOthersAll.Checked = True Or OpShortDescBikesQuadsAll.Checked = True Then
            If OpShortDescCarsLcvOthersAll.Checked = True Then
                StrVehCat = "CarLcvOthers"
            End If

            If OpShortDescBikesQuadsAll.Checked = True Then
                StrVehCat = "BikesQuads"
            End If

            If OPBulkDescView1.Checked Then
                StrProcName = "usp_GetBulkDescStringsSuper"
            End If

            If OPBulkDescView2.Checked Then
                StrProcName = "usp_GetBulkDescStringsSuperView2"
            End If

            If OPBulkDescView3.Checked Then
                StrProcName = "usp_GetBulkDescStringsSuperView3"
            End If

            If OPBulkDescView4.Checked Then
                StrProcName = "usp_GetBulkDescStringsSuperView4"
            End If

        End If

        If OpShortDescAllAll.Checked = True Then

            If OPBulkDescView1.Checked = True Then
                StrProcName = "usp_GetBulkDescStrings"
            End If

            If OPBulkDescView2.Checked = True Then
                StrProcName = "usp_GetBulkDescStringsView2"
            End If

            If OPBulkDescView3.Checked = True Then
                StrProcName = "usp_GetBulkDescStringsView3"
            End If

            If OPBulkDescView4.Checked = True Then
                StrProcName = "usp_GetBulkDescStringsView4"
            End If


        End If
        If OpBulkAllStrShort.Checked = True Then

            Index = 1
        Else
            Index = 0

        End If

        dtWords = New DataTable
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString

        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandTimeout = 0
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()

                cmd.Parameters.AddWithValue("@paramWithoutJoin", Index)
                If OpShortDescCarsAll.Checked = True Or OpShortDescBikesAll.Checked = True Or OpShortDescLcvAll.Checked = True Or OpShortDescOthersAll.Checked = True Then
                    cmd.Parameters.AddWithValue("@VCategory", StrVehCat)

                ElseIf OpShortDescCarsLcvOthersAll.Checked = True Or OpShortDescBikesQuadsAll.Checked = True Then
                    cmd.Parameters.AddWithValue("@VCatSuper", StrVehCat)

                ElseIf OpShortDescAllAll.Checked = True Then

                End If



                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtWords.Load(reader)
            End Using
        End Using
        Return dtWords
    End Function


    Public Sub CreateAllStrs()
        'LBLBulkWait.Visible = True
        Dim StrProcName As String
        StrProcName = "usp_CreateStrStringsPlusTidyup"

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString

        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandTimeout = 0
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                cmd.ExecuteScalar()
            End Using
        End Using
        'LBLBulkWait.Visible = False
    End Sub

    Public Sub checkstrstrings()

        Dim Str1() As String
        Dim Str2() As String
        Dim dtStrShortGrouped As DataTable
        Dim dtStrShortGroupedWithPK As DataTable
        Dim index As Long
        'not currently used flip this function over to SQL server for speed enhancement
        index = 1

        'get data
        dtStrShortGrouped = GetShortStringsToProcess(1)
        dtStrShortGroupedWithPK = GetShortStringsToProcess(2)
        'get short descriptive strings by iterating trhough group from SP
        For Each drRecord As DataRow In dtStrShortGrouped.Rows
            Str1 = drRecord("StrShort").ToLower().Split(" "c)
            For Each drRecordPK As DataRow In dtStrShortGroupedWithPK.Rows
                If index > 1 Then
                    Str2 = drRecordPK("StrShort").ToLower().Split(" "c)

                    If Str1.All(Function(w1) Str2.Contains(w1)) AndAlso Str1.Length = Str2.Length Then

                        Console.WriteLine("All words win w1 are in w2")
                        Console.WriteLine(drRecord("StrShort").ToLower().Split(" "c))
                        Console.WriteLine(drRecordPK("StrShort").ToLower().Split(" "c))
                        MessageBox.Show("same strings")
                    Else

                        Console.WriteLine("All words win w1 are NOT in w2")

                    End If


                End If
                index = index + 1
            Next

        Next
        MessageBox.Show("finished")

    End Sub

    Public Function GetShortStringsToProcess(ByVal StrView As Int16) As DataTable
        Dim StrProcName As String

        If StrView = 1 Then
            StrProcName = "usp_GetallShortDescGrouped"
        Else
            StrProcName = "usp_GetallShortDescGroupedWithPK"

        End If


        dtWords = New DataTable
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString

        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandTimeout = 0
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()

                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtWords.Load(reader)
            End Using
        End Using
        Return dtWords
    End Function
    Private Sub PanelBTN_Click(sender As Object, e As EventArgs) Handles PanelBTN.Click
        Call CreateWords()
        Call UpdateVehicleCat()
    End Sub
    Private IsLoading As Boolean = False
    Public Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        IsLoading = True
        Dim ctrl As Control = TabControl1


        OPBulkDescView1.Checked = True

        OpBulkAllStrShort.Checked = True

        OPBulkDescView1.Checked = True

        OpShortDescAllAll.Checked = True

        OpBulkTag.Checked = True

   

        IsLoading = False
        'only needed if start method 1

    End Sub


    Private Sub OpBulkTag_Click(sender As Object, e As EventArgs) Handles OpBulkTag.CheckedChanged

        ListBulk.DataSource = GetDataBulk().AsEnumerable().Select(Function(r) r.Field(Of String)("Word")).ToList()

    End Sub
    Private Sub OpUnTagBulk_Click(sender As Object, e As EventArgs) Handles OpUnTagBulk.CheckedChanged

        ListBulk.DataSource = GetDataBulk().AsEnumerable().Select(Function(r) r.Field(Of String)("Word")).ToList()

    End Sub

    Private Sub PanelBtnBulkTag_Click(sender As Object, e As EventArgs) Handles PanelBtnBulkTag.Click
        Call TagBulkWords()

    End Sub


    Private Sub BtnClearBulk_Click(sender As Object, e As EventArgs) Handles ListBulk.Click, BtnClearBulk.Click
        ListBulk.ClearSelected()
    End Sub
    Public Sub DataGroupCallStringsBulk()

        DataGridViewStringsBulk.DataSource = GetBulkDataStrings()
        DataGridViewStringsBulk.Refresh()
        LBLDescCountAll.Text = "Short Desc Words: " & DataGridViewStringsBulk.RowCount.ToString()
        Dim columnsBulk As New List(Of String)(From column As DataColumn In CType(DataGridViewStringsBulk.DataSource, DataTable).Columns Select column.ColumnName)
        columnsBulk.Insert(0, "- All Columns -")
        CBBulkSearchDesc.DataSource = columnsBulk
    End Sub
    Public Sub DataGroupCallBulk()
        ListBulk.DataSource = GetDataBulk().AsEnumerable().Select(Function(r) r.Field(Of String)("Word")).ToList()
        LblBulkCount.Text = "Words to Tag: " & ListBulk.Items.Count
    End Sub

    Private Sub OpShortDescCarsAll_CheckedChanged(sender As Object, e As EventArgs) Handles OpShortDescCarsAll.CheckedChanged
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescBikesAll_CheckedChanged(sender As Object, e As EventArgs) Handles OpShortDescBikesAll.CheckedChanged
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescLcvAll_CheckedChanged(sender As Object, e As EventArgs) Handles OpShortDescLcvAll.CheckedChanged
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescOthersAll_CheckedChanged(sender As Object, e As EventArgs) Handles OpShortDescOthersAll.CheckedChanged
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescCarsLcvOthersAll_CheckedChanged(sender As Object, e As EventArgs) Handles OpShortDescCarsLcvOthersAll.CheckedChanged
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescBikesQuadsAll_CheckedChanged(sender As Object, e As EventArgs) Handles OpShortDescBikesQuadsAll.CheckedChanged
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub
    Private Sub OpShortDescAllAll_CheckedChanged(sender As Object, e As EventArgs) Handles OpShortDescAllAll.CheckedChanged
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescCars_CheckedChanged(sender As Object, e As EventArgs)
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescBikes_CheckedChanged(sender As Object, e As EventArgs)
        Call DataGroupCallStringsBulk()
        Call DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescLcv_CheckedChanged(sender As Object, e As EventArgs)
        Call DataGroupCallStringsBulk()
        DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescOthers_CheckedChanged(sender As Object, e As EventArgs)
        Call DataGroupCallStringsBulk()
        DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescCarsLcvOthers_CheckedChanged(sender As Object, e As EventArgs)
        Call DataGroupCallStringsBulk()
        DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescBikesQuads_CheckedChanged(sender As Object, e As EventArgs)
        Call DataGroupCallStringsBulk()
        DataGroupCallBulk()
    End Sub

    Private Sub OpShortDescAll_CheckedChanged(sender As Object, e As EventArgs)
        Call DataGroupCallStringsBulk()
        DataGroupCallBulk()
    End Sub


    Private Sub Panel1_Click(sender As Object, e As EventArgs) Handles Panel1.Click
        MessageBox.Show("Short Strings will be created, click to continue, this process takes about 4 minutes, so wait for the next message after clicking ok")
        LBLShortWork.Visible = True
        Call CreateAllStrs()
        LBLShortWork.Visible = False
        MessageBox.Show("Short Strings Created")

    End Sub


    Private Sub OPBulkDescView1_CheckedChanged(sender As Object, e As EventArgs) Handles OPBulkDescView1.CheckedChanged
        If IsLoading Then Return
        Call DataGroupCallStringsBulk()

    End Sub

    Private Sub OPBulkDescView2_CheckedChanged(sender As Object, e As EventArgs) Handles OPBulkDescView2.CheckedChanged
        If IsLoading Then Return
        Call DataGroupCallStringsBulk()
    End Sub

    Private Sub OPBulkDescView3_CheckedChanged(sender As Object, e As EventArgs) Handles OPBulkDescView3.CheckedChanged
        If IsLoading Then Return
        Call DataGroupCallStringsBulk()
    End Sub

    Private Sub OPBulkDescView4_CheckedChanged(sender As Object, e As EventArgs) Handles OPBulkDescView4.CheckedChanged
        If IsLoading Then Return
        Call DataGroupCallStringsBulk()
    End Sub

    Private Sub OpBulkAllStrShort_CheckedChanged(sender As Object, e As EventArgs) Handles OpBulkAllStrShort.CheckedChanged
        If IsLoading Then Return
        Call DataGroupCallStringsBulk()
    End Sub

    Private Sub OpBulkAllStrShortCWCreated_CheckedChanged(sender As Object, e As EventArgs) Handles OpBulkAllStrShortCWCreated.CheckedChanged
        If IsLoading Then Return
        Call DataGroupCallStringsBulk()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs)
        Call checkstrstrings()
    End Sub

    Private Shadows Sub OnKeyUp(sender As Object, e As KeyEventArgs) Handles TxtBulkShortDesc.KeyUp
        Dim tb = DirectCast(sender, TextBox)
        If tb.Equals(TxtBulkShortDesc) Then
            If e.KeyCode.Equals(Keys.Enter) Then
                DataGridViewStringsBulk.SearchAndSelect(tb.Text, CBBulkSearchDesc.SelectedItem, True)
                tb.Clear()
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub BtnBulkShortDescClear_Click(sender As Object, e As EventArgs) Handles BtnBulkShortDescClear.Click
        DataGridViewStringsBulk.ClearSelection()
    End Sub

    Private Sub TabPage6_Leave(sender As Object, e As EventArgs) Handles TabPage6.Leave
        DataGridViewStringsBulk.Visible = False
        ListBulk.Visible = False

    End Sub

    Private Sub TabPage6_Enter(sender As Object, e As EventArgs) Handles TabPage6.Enter
        DataGridViewStringsBulk.Visible = True

        Call DataGroupCallStringsBulk()
        DataGridViewStringsBulk.ClearSelection()

        ListBulk.Visible = True
        Call DataGroupCallBulk()
        ListBulk.ClearSelected()

    End Sub

    Private Sub PanelLinkShort_Click(sender As Object, e As EventArgs) Handles PanelLinkShort.Click
        Dim deletions As New List(Of String)
        Dim StrCommonSet As Boolean
        Dim StrTemp As String
        StrTemp = ""

        StrCommonSet = False
        CBList.Items.Clear()

        If OPBulkDescView4.Checked = True Then

            For Each row As DataGridViewRow In DataGridViewStringsBulk.SelectedRows

                CBList.Items.Add(row.Cells("Strshort").Value)
            Next

        Else

        End If
    End Sub

    Private Sub CBList_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CBList.SelectedIndexChanged
        Dim deletions As New List(Of String)
        Dim StrCommonSet As Boolean
        Dim StrTemp As String
        StrTemp = ""

        StrCommonSet = False

        If OPBulkDescView4.Checked = True Then
            Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
                cnSql.Open()
                For Each row As DataGridViewRow In DataGridViewStringsBulk.SelectedRows
                    If Not Convert.IsDBNull(row.Cells("StrCommon").Value) Then

                        If Not deletions.Contains(row.Cells("StrCommon").Value, StringComparer.OrdinalIgnoreCase) Then
                            deletions.Add(row.Cells("StrCommon").Value)
                        End If

                    End If
                Next

                For Each term In deletions
                    Using cmdInsert As New SqlCommand("usp_DeleteShortDescLinkWords", cnSql)
                        cmdInsert.CommandType = CommandType.StoredProcedure
                        cmdInsert.CommandTimeout = 0
                        cmdInsert.Parameters.AddWithValue("@Word", term)
                        cmdInsert.ExecuteScalar()
                    End Using
                Next

                For Each row As DataGridViewRow In DataGridViewStringsBulk.SelectedRows

                    If StrCommonSet = False Then
                        StrTemp = CBList.Text
                        StrCommonSet = True
                    End If

                    Using cmdInsert As New SqlClient.SqlCommand
                        cmdInsert.Connection = cnSql
                        cmdInsert.CommandTimeout = 0
                        cmdInsert.CommandText = "INSERT INTO TblShortDescLink (StrShort, StrCommon) VALUES ('" & row.Cells("Strshort").Value & "','" & StrTemp & "'" & ")"
                        cmdInsert.ExecuteNonQuery()
                    End Using

                Next

                Using cmdCommonInsert As New SqlCommand("usp_UpdateStrCommon", cnSql)
                    cmdCommonInsert.CommandType = CommandType.StoredProcedure
                    cmdCommonInsert.CommandTimeout = 0
                    cmdCommonInsert.ExecuteNonQuery()
                End Using

            End Using

            MessageBox.Show("Linking finished")
        Else
            MessageBox.Show("StrShort must be checked in order to link succesfully")
        End If
    End Sub

End Class
Module Extensions
    <Extension()>
    Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As DataTable = New DataTable()

        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
                table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
            Else
                table.Columns.Add([property].Name, [property].PropertyType)
            End If
        Next

        Dim values(properties.Count - 1) As Object
        For Each item As T In source
            For i As Integer = 0 To properties.Count - 1
                values(i) = properties(i).GetValue(item)
            Next
            table.Rows.Add(values)
        Next

        Return table
    End Function

    <Extension()>
    Public Sub SearchAndSelect(grid As DataGridView, term As String, selector As String, Optional clearSelected As Boolean = False)
        If grid IsNot Nothing AndAlso grid.Rows.Count < 1 Then Return
        If String.IsNullOrWhiteSpace(term) Then Return
        grid.BeginEdit(True)
        Dim counter = 0
        If clearSelected Then
            For Each row In grid.SelectedRows.Cast(Of DataGridViewRow)()
                row.Selected = False
            Next
        End If

        For Each row In grid.Rows.Cast(Of DataGridViewRow)()
            If selector <> "- All Columns -" Then
                If row.Cells(selector).Value IsNot Nothing AndAlso row.Cells(selector).Value.ToString().IndexOf(term, StringComparison.OrdinalIgnoreCase) <> -1 Then
                    row.Selected = True
                    counter += 1
                    Continue For
                End If
            Else
                For Each column In grid.Columns.Cast(Of DataGridViewColumn)()
                    If row.Cells(column.Name).Value IsNot Nothing AndAlso row.Cells(column.Name).Value.ToString().IndexOf(term, StringComparison.OrdinalIgnoreCase) <> -1 Then
                        row.Selected = True
                        counter += 1
                        Exit For
                    End If
                Next
            End If
        Next
        grid.EndEdit()

        If counter = 0 Then MessageBox.Show(String.Format("No rows were found that matched - {0}", term))
    End Sub
End Module

Open in new window


Form1 designer code
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()>
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()>
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()>
    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container()
        Me.GroupBox1 = New System.Windows.Forms.GroupBox()
        Me.RadioButton5 = New System.Windows.Forms.RadioButton()
        Me.RadioButton4 = New System.Windows.Forms.RadioButton()
        Me.RadioButton3 = New System.Windows.Forms.RadioButton()
        Me.RadioButton2 = New System.Windows.Forms.RadioButton()
        Me.RadioButton1 = New System.Windows.Forms.RadioButton()
        Me.PanelBTN = New System.Windows.Forms.Panel()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.ProgressBar1 = New System.Windows.Forms.ProgressBar()
        Me.Label3 = New System.Windows.Forms.Label()
        Me.TabControl1 = New System.Windows.Forms.TabControl()
        Me.TabPage1 = New System.Windows.Forms.TabPage()
        Me.TabPage4 = New System.Windows.Forms.TabPage()
        Me.LBLShortWork = New System.Windows.Forms.Label()
        Me.Label11 = New System.Windows.Forms.Label()
        Me.Panel1 = New System.Windows.Forms.Panel()
        Me.TabPage6 = New System.Windows.Forms.TabPage()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.PanelBtnBulkTag = New System.Windows.Forms.Panel()
        Me.LblBulkCount = New System.Windows.Forms.Label()
        Me.BtnClearBulk = New System.Windows.Forms.Button()
        Me.Label4 = New System.Windows.Forms.Label()
        Me.TxtBoxSearchBulk = New System.Windows.Forms.TextBox()
        Me.ListBulk = New System.Windows.Forms.ListBox()
        Me.GroupBox2 = New System.Windows.Forms.GroupBox()
        Me.OpUnTagBulk = New System.Windows.Forms.RadioButton()
        Me.OpBulkTag = New System.Windows.Forms.RadioButton()
        Me.CBList = New System.Windows.Forms.ComboBox()
        Me.Label13 = New System.Windows.Forms.Label()
        Me.PanelLinkShort = New System.Windows.Forms.Panel()
        Me.BtnBulkShortDescClear = New System.Windows.Forms.Button()
        Me.CBBulkSearchDesc = New System.Windows.Forms.ComboBox()
        Me.TxtBulkShortDesc = New System.Windows.Forms.TextBox()
        Me.GroupBox12 = New System.Windows.Forms.GroupBox()
        Me.OpBulkAllStrShortCWCreated = New System.Windows.Forms.RadioButton()
        Me.OpBulkAllStrShort = New System.Windows.Forms.RadioButton()
        Me.GroupBox9 = New System.Windows.Forms.GroupBox()
        Me.ProgressBar2 = New System.Windows.Forms.ProgressBar()
        Me.OPBulkDescView4 = New System.Windows.Forms.RadioButton()
        Me.OPBulkDescView3 = New System.Windows.Forms.RadioButton()
        Me.OPBulkDescView2 = New System.Windows.Forms.RadioButton()
        Me.OPBulkDescView1 = New System.Windows.Forms.RadioButton()
        Me.LBLDescCountAll = New System.Windows.Forms.Label()
        Me.DataGridViewStringsBulk = New System.Windows.Forms.DataGridView()
        Me.GroupBox5 = New System.Windows.Forms.GroupBox()
        Me.OpShortDescAllAll = New System.Windows.Forms.RadioButton()
        Me.OpShortDescBikesQuadsAll = New System.Windows.Forms.RadioButton()
        Me.OpShortDescCarsLcvOthersAll = New System.Windows.Forms.RadioButton()
        Me.OpShortDescOthersAll = New System.Windows.Forms.RadioButton()
        Me.OpShortDescLcvAll = New System.Windows.Forms.RadioButton()
        Me.OpShortDescBikesAll = New System.Windows.Forms.RadioButton()
        Me.OpShortDescCarsAll = New System.Windows.Forms.RadioButton()
        Me.UspGetMatrixBindingSource = New System.Windows.Forms.BindingSource(Me.components)
        Me.DictionaryDataSet = New WindowsApplication1.DictionaryDataSet()
        Me.UspGetMatrixBindingSource2 = New System.Windows.Forms.BindingSource(Me.components)
        Me.DictionaryDataSet1 = New WindowsApplication1.DictionaryDataSet()
        Me.UspGetMatrixBindingSource1 = New System.Windows.Forms.BindingSource(Me.components)
        Me.Usp_GetMatrixTableAdapter1 = New WindowsApplication1.DictionaryDataSetTableAdapters.usp_GetMatrixTableAdapter()
        Me.UspGetMatrixBindingSource3 = New System.Windows.Forms.BindingSource(Me.components)
        Me.GroupBox1.SuspendLayout()
        Me.TabControl1.SuspendLayout()
        Me.TabPage1.SuspendLayout()
        Me.TabPage4.SuspendLayout()
        Me.TabPage6.SuspendLayout()
        Me.GroupBox2.SuspendLayout()
        Me.GroupBox12.SuspendLayout()
        Me.GroupBox9.SuspendLayout()
        CType(Me.DataGridViewStringsBulk, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.GroupBox5.SuspendLayout()
        CType(Me.UspGetMatrixBindingSource, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DictionaryDataSet, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.UspGetMatrixBindingSource2, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.DictionaryDataSet1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.UspGetMatrixBindingSource1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.UspGetMatrixBindingSource3, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'GroupBox1
        '
        Me.GroupBox1.Controls.Add(Me.RadioButton5)
        Me.GroupBox1.Controls.Add(Me.RadioButton4)
        Me.GroupBox1.Controls.Add(Me.RadioButton3)
        Me.GroupBox1.Controls.Add(Me.RadioButton2)
        Me.GroupBox1.Controls.Add(Me.RadioButton1)
        Me.GroupBox1.Location = New System.Drawing.Point(6, 6)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(101, 150)
        Me.GroupBox1.TabIndex = 1
        Me.GroupBox1.TabStop = False
        Me.GroupBox1.Text = "Clients"
        '
        'RadioButton5
        '
        Me.RadioButton5.AccessibleName = "OPTvi"
        Me.RadioButton5.AutoSize = True
        Me.RadioButton5.Location = New System.Drawing.Point(17, 118)
        Me.RadioButton5.Name = "RadioButton5"
        Me.RadioButton5.Size = New System.Drawing.Size(40, 17)
        Me.RadioButton5.TabIndex = 4
        Me.RadioButton5.TabStop = True
        Me.RadioButton5.Text = "Tvi"
        Me.RadioButton5.UseVisualStyleBackColor = True
        '
        'RadioButton4
        '
        Me.RadioButton4.AccessibleName = "OPGlass"
        Me.RadioButton4.AutoSize = True
        Me.RadioButton4.Location = New System.Drawing.Point(17, 94)
        Me.RadioButton4.Name = "RadioButton4"
        Me.RadioButton4.Size = New System.Drawing.Size(51, 17)
        Me.RadioButton4.TabIndex = 3
        Me.RadioButton4.TabStop = True
        Me.RadioButton4.Text = "Glass"
        Me.RadioButton4.UseVisualStyleBackColor = True
        '
        'RadioButton3
        '
        Me.RadioButton3.AccessibleName = "OPCap"
        Me.RadioButton3.AutoSize = True
        Me.RadioButton3.Location = New System.Drawing.Point(17, 70)
        Me.RadioButton3.Name = "RadioButton3"
        Me.RadioButton3.Size = New System.Drawing.Size(44, 17)
        Me.RadioButton3.TabIndex = 2
        Me.RadioButton3.TabStop = True
        Me.RadioButton3.Text = "Cap"
        Me.RadioButton3.UseVisualStyleBackColor = True
        '
        'RadioButton2
        '
        Me.RadioButton2.AccessibleName = "OpAbi"
        Me.RadioButton2.AutoSize = True
        Me.RadioButton2.Location = New System.Drawing.Point(17, 47)
        Me.RadioButton2.Name = "RadioButton2"
        Me.RadioButton2.Size = New System.Drawing.Size(40, 17)
        Me.RadioButton2.TabIndex = 1
        Me.RadioButton2.TabStop = True
        Me.RadioButton2.Text = "Abi"
        Me.RadioButton2.UseVisualStyleBackColor = True
        '
        'RadioButton1
        '
        Me.RadioButton1.AccessibleName = "OpSMMT"
        Me.RadioButton1.AutoSize = True
        Me.RadioButton1.Location = New System.Drawing.Point(17, 24)
        Me.RadioButton1.Name = "RadioButton1"
        Me.RadioButton1.Size = New System.Drawing.Size(57, 17)
        Me.RadioButton1.TabIndex = 0
        Me.RadioButton1.TabStop = True
        Me.RadioButton1.Text = "SMMT"
        Me.RadioButton1.UseVisualStyleBackColor = True
        '
        'PanelBTN
        '
        Me.PanelBTN.AutoSizeMode = System.Windows.Forms.AutoSizeMode.GrowAndShrink
        Me.PanelBTN.BackColor = System.Drawing.Color.Black
        Me.PanelBTN.BackgroundImage = Global.WindowsApplication1.My.Resources.Resources.PMVButton
        Me.PanelBTN.BackgroundImageLayout = System.Windows.Forms.ImageLayout.Zoom
        Me.PanelBTN.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.PanelBTN.Location = New System.Drawing.Point(263, 60)
        Me.PanelBTN.Name = "PanelBTN"
        Me.PanelBTN.Size = New System.Drawing.Size(87, 90)
        Me.PanelBTN.TabIndex = 3
        '
        'Label1
        '
        Me.Label1.FlatStyle = System.Windows.Forms.FlatStyle.Flat
        Me.Label1.ForeColor = System.Drawing.SystemColors.Desktop
        Me.Label1.Location = New System.Drawing.Point(260, 4)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(90, 53)
        Me.Label1.TabIndex = 4
        Me.Label1.Text = "Click to create Dictionary of words for selected client"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.TopCenter
        '
        'ProgressBar1
        '
        Me.ProgressBar1.Location = New System.Drawing.Point(6, 156)
        Me.ProgressBar1.Name = "ProgressBar1"
        Me.ProgressBar1.Size = New System.Drawing.Size(344, 22)
        Me.ProgressBar1.TabIndex = 5
        '
        'Label3
        '
        Me.Label3.AutoSize = True
        Me.Label3.Location = New System.Drawing.Point(6, 181)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(21, 13)
        Me.Label3.TabIndex = 7
        Me.Label3.Text = "0%"
        '
        'TabControl1
        '
        Me.TabControl1.Controls.Add(Me.TabPage1)
        Me.TabControl1.Controls.Add(Me.TabPage4)
        Me.TabControl1.Controls.Add(Me.TabPage6)
        Me.TabControl1.Location = New System.Drawing.Point(12, 6)
        Me.TabControl1.Name = "TabControl1"
        Me.TabControl1.SelectedIndex = 0
        Me.TabControl1.Size = New System.Drawing.Size(1467, 675)
        Me.TabControl1.TabIndex = 2
        '
        'TabPage1
        '
        Me.TabPage1.Controls.Add(Me.PanelBTN)
        Me.TabPage1.Controls.Add(Me.Label3)
        Me.TabPage1.Controls.Add(Me.Label1)
        Me.TabPage1.Controls.Add(Me.ProgressBar1)
        Me.TabPage1.Controls.Add(Me.GroupBox1)
        Me.TabPage1.Location = New System.Drawing.Point(4, 22)
        Me.TabPage1.Name = "TabPage1"
        Me.TabPage1.Padding = New System.Windows.Forms.Padding(3)
        Me.TabPage1.Size = New System.Drawing.Size(1459, 649)
        Me.TabPage1.TabIndex = 0
        Me.TabPage1.Text = "Get Words"
        Me.TabPage1.UseVisualStyleBackColor = True
        '
        'TabPage4
        '
        Me.TabPage4.Controls.Add(Me.LBLShortWork)
        Me.TabPage4.Controls.Add(Me.Label11)
        Me.TabPage4.Controls.Add(Me.Panel1)
        Me.TabPage4.Location = New System.Drawing.Point(4, 22)
        Me.TabPage4.Name = "TabPage4"
        Me.TabPage4.Padding = New System.Windows.Forms.Padding(3)
        Me.TabPage4.Size = New System.Drawing.Size(1459, 649)
        Me.TabPage4.TabIndex = 6
        Me.TabPage4.Text = "Creare Short Strings"
        Me.TabPage4.UseVisualStyleBackColor = True
        '
        'LBLShortWork
        '
        Me.LBLShortWork.AutoSize = True
        Me.LBLShortWork.Location = New System.Drawing.Point(404, 147)
        Me.LBLShortWork.Name = "LBLShortWork"
        Me.LBLShortWork.Size = New System.Drawing.Size(124, 13)
        Me.LBLShortWork.TabIndex = 11
        Me.LBLShortWork.Text = "Working....Dont Interrupt"
        '
        'Label11
        '
        Me.Label11.Location = New System.Drawing.Point(22, 32)
        Me.Label11.Name = "Label11"
        Me.Label11.Size = New System.Drawing.Size(100, 115)
        Me.Label11.TabIndex = 10
        Me.Label11.Text = "Once you have finished your word tagging or untagging operation please click this" &
    " button to create the Short Descriptive Strings"
        '
        'Panel1
        '
        Me.Panel1.BackgroundImage = Global.WindowsApplication1.My.Resources.Resources.PMVButton
        Me.Panel1.BackgroundImageLayout = System.Windows.Forms.ImageLayout.Zoom
        Me.Panel1.Location = New System.Drawing.Point(128, 42)
        Me.Panel1.Name = "Panel1"
        Me.Panel1.Size = New System.Drawing.Size(85, 90)
        Me.Panel1.TabIndex = 9
        '
        'TabPage6
        '
        Me.TabPage6.Controls.Add(Me.Label2)
        Me.TabPage6.Controls.Add(Me.PanelBtnBulkTag)
        Me.TabPage6.Controls.Add(Me.LblBulkCount)
        Me.TabPage6.Controls.Add(Me.BtnClearBulk)
        Me.TabPage6.Controls.Add(Me.Label4)
        Me.TabPage6.Controls.Add(Me.TxtBoxSearchBulk)
        Me.TabPage6.Controls.Add(Me.ListBulk)
        Me.TabPage6.Controls.Add(Me.GroupBox2)
        Me.TabPage6.Controls.Add(Me.CBList)
        Me.TabPage6.Controls.Add(Me.Label13)
        Me.TabPage6.Controls.Add(Me.PanelLinkShort)
        Me.TabPage6.Controls.Add(Me.BtnBulkShortDescClear)
        Me.TabPage6.Controls.Add(Me.CBBulkSearchDesc)
        Me.TabPage6.Controls.Add(Me.TxtBulkShortDesc)
        Me.TabPage6.Controls.Add(Me.GroupBox12)
        Me.TabPage6.Controls.Add(Me.GroupBox9)
        Me.TabPage6.Controls.Add(Me.LBLDescCountAll)
        Me.TabPage6.Controls.Add(Me.DataGridViewStringsBulk)
        Me.TabPage6.Controls.Add(Me.GroupBox5)
        Me.TabPage6.Location = New System.Drawing.Point(4, 22)
        Me.TabPage6.Name = "TabPage6"
        Me.TabPage6.Padding = New System.Windows.Forms.Padding(3)
        Me.TabPage6.Size = New System.Drawing.Size(1459, 649)
        Me.TabPage6.TabIndex = 4
        Me.TabPage6.Text = "Short Desc All"
        Me.TabPage6.UseVisualStyleBackColor = True
        '
        'Label2
        '
        Me.Label2.AutoSize = True
        Me.Label2.Location = New System.Drawing.Point(1205, 143)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(60, 13)
        Me.Label2.TabIndex = 103
        Me.Label2.Text = "Tag Words"
        '
        'PanelBtnBulkTag
        '
        Me.PanelBtnBulkTag.BackgroundImage = Global.WindowsApplication1.My.Resources.Resources.PMVButton
        Me.PanelBtnBulkTag.BackgroundImageLayout = System.Windows.Forms.ImageLayout.Zoom
        Me.PanelBtnBulkTag.Location = New System.Drawing.Point(1208, 96)
        Me.PanelBtnBulkTag.Name = "PanelBtnBulkTag"
        Me.PanelBtnBulkTag.Size = New System.Drawing.Size(50, 44)
        Me.PanelBtnBulkTag.TabIndex = 102
        '
        'LblBulkCount
        '
        Me.LblBulkCount.AutoSize = True
        Me.LblBulkCount.Location = New System.Drawing.Point(1108, 18)
        Me.LblBulkCount.Name = "LblBulkCount"
        Me.LblBulkCount.Size = New System.Drawing.Size(79, 13)
        Me.LblBulkCount.TabIndex = 101
        Me.LblBulkCount.Text = "Count of listbox"
        Me.LblBulkCount.TextAlign = System.Drawing.ContentAlignment.BottomLeft
        '
        'BtnClearBulk
        '
        Me.BtnClearBulk.Location = New System.Drawing.Point(1219, 325)
        Me.BtnClearBulk.Name = "BtnClearBulk"
        Me.BtnClearBulk.Size = New System.Drawing.Size(90, 47)
        Me.BtnClearBulk.TabIndex = 100
        Me.BtnClearBulk.Text = "Clear All Selected"
        Me.BtnClearBulk.UseVisualStyleBackColor = True
        '
        'Label4
        '
        Me.Label4.AutoSize = True
        Me.Label4.Location = New System.Drawing.Point(970, 2)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(68, 13)
        Me.Label4.TabIndex = 99
        Me.Label4.Text = "Search Term"
        '
        'TxtBoxSearchBulk
        '
        Me.TxtBoxSearchBulk.Location = New System.Drawing.Point(973, 15)
        Me.TxtBoxSearchBulk.Name = "TxtBoxSearchBulk"
        Me.TxtBoxSearchBulk.Size = New System.Drawing.Size(129, 20)
        Me.TxtBoxSearchBulk.TabIndex = 98
        '
        'ListBulk
        '
        Me.ListBulk.FormattingEnabled = True
        Me.ListBulk.Location = New System.Drawing.Point(961, 43)
        Me.ListBulk.Name = "ListBulk"
        Me.ListBulk.SelectionMode = System.Windows.Forms.SelectionMode.MultiSimple
        Me.ListBulk.Size = New System.Drawing.Size(226, 329)
        Me.ListBulk.TabIndex = 97
        '
        'GroupBox2
        '
        Me.GroupBox2.Controls.Add(Me.OpUnTagBulk)
        Me.GroupBox2.Controls.Add(Me.OpBulkTag)
        Me.GroupBox2.Location = New System.Drawing.Point(1208, 43)
        Me.GroupBox2.Name = "GroupBox2"
        Me.GroupBox2.Size = New System.Drawing.Size(114, 47)
        Me.GroupBox2.TabIndex = 96
        Me.GroupBox2.TabStop = False
        Me.GroupBox2.Text = "Function"
        '
        'OpUnTagBulk
        '
        Me.OpUnTagBulk.AutoSize = True
        Me.OpUnTagBulk.Location = New System.Drawing.Point(6, 30)
        Me.OpUnTagBulk.Name = "OpUnTagBulk"
        Me.OpUnTagBulk.Size = New System.Drawing.Size(95, 17)
        Me.OpUnTagBulk.TabIndex = 23
        Me.OpUnTagBulk.TabStop = True
        Me.OpUnTagBulk.Text = "Un-Tag Words"
        Me.OpUnTagBulk.UseVisualStyleBackColor = True
        '
        'OpBulkTag
        '
        Me.OpBulkTag.AutoSize = True
        Me.OpBulkTag.Location = New System.Drawing.Point(6, 14)
        Me.OpBulkTag.Name = "OpBulkTag"
        Me.OpBulkTag.Size = New System.Drawing.Size(78, 17)
        Me.OpBulkTag.TabIndex = 1
        Me.OpBulkTag.TabStop = True
        Me.OpBulkTag.Text = "Tag Words"
        Me.OpBulkTag.UseVisualStyleBackColor = True
        '
        'CBList
        '
        Me.CBList.FormattingEnabled = True
        Me.CBList.Location = New System.Drawing.Point(3, 583)
        Me.CBList.Name = "CBList"
        Me.CBList.Size = New System.Drawing.Size(499, 21)
        Me.CBList.TabIndex = 95
        '
        'Label13
        '
        Me.Label13.AutoSize = True
        Me.Label13.Location = New System.Drawing.Point(6, 567)
        Me.Label13.Name = "Label13"
        Me.Label13.Size = New System.Drawing.Size(157, 13)
        Me.Label13.TabIndex = 93
        Me.Label13.Text = "Click Button to Populate Combo"
        '
        'PanelLinkShort
        '
        Me.PanelLinkShort.BackgroundImage = Global.WindowsApplication1.My.Resources.Resources.PMVButton
        Me.PanelLinkShort.BackgroundImageLayout = System.Windows.Forms.ImageLayout.Zoom
        Me.PanelLinkShort.Location = New System.Drawing.Point(508, 573)
        Me.PanelLinkShort.Name = "PanelLinkShort"
        Me.PanelLinkShort.Size = New System.Drawing.Size(44, 44)
        Me.PanelLinkShort.TabIndex = 92
        '
        'BtnBulkShortDescClear
        '
        Me.BtnBulkShortDescClear.Location = New System.Drawing.Point(580, 570)
        Me.BtnBulkShortDescClear.Name = "BtnBulkShortDescClear"
        Me.BtnBulkShortDescClear.Size = New System.Drawing.Size(96, 44)
        Me.BtnBulkShortDescClear.TabIndex = 82
        Me.BtnBulkShortDescClear.Text = "Clear All Selected"
        Me.BtnBulkShortDescClear.UseVisualStyleBackColor = True
        '
        'CBBulkSearchDesc
        '
        Me.CBBulkSearchDesc.FormattingEnabled = True
        Me.CBBulkSearchDesc.Location = New System.Drawing.Point(162, 13)
        Me.CBBulkSearchDesc.Name = "CBBulkSearchDesc"
        Me.CBBulkSearchDesc.Size = New System.Drawing.Size(153, 21)
        Me.CBBulkSearchDesc.TabIndex = 81
        '
        'TxtBulkShortDesc
        '
        Me.TxtBulkShortDesc.Location = New System.Drawing.Point(6, 13)
        Me.TxtBulkShortDesc.Name = "TxtBulkShortDesc"
        Me.TxtBulkShortDesc.Size = New System.Drawing.Size(150, 20)
        Me.TxtBulkShortDesc.TabIndex = 80
        '
        'GroupBox12
        '
        Me.GroupBox12.Controls.Add(Me.OpBulkAllStrShortCWCreated)
        Me.GroupBox12.Controls.Add(Me.OpBulkAllStrShort)
        Me.GroupBox12.Location = New System.Drawing.Point(759, 131)
        Me.GroupBox12.Name = "GroupBox12"
        Me.GroupBox12.Size = New System.Drawing.Size(164, 54)
        Me.GroupBox12.TabIndex = 79
        Me.GroupBox12.TabStop = False
        Me.GroupBox12.Text = "Depth"
        '
        'OpBulkAllStrShortCWCreated
        '
        Me.OpBulkAllStrShortCWCreated.AutoSize = True
        Me.OpBulkAllStrShortCWCreated.Location = New System.Drawing.Point(6, 30)
        Me.OpBulkAllStrShortCWCreated.Name = "OpBulkAllStrShortCWCreated"
        Me.OpBulkAllStrShortCWCreated.Size = New System.Drawing.Size(154, 17)
        Me.OpBulkAllStrShortCWCreated.TabIndex = 24
        Me.OpBulkAllStrShortCWCreated.TabStop = True
        Me.OpBulkAllStrShortCWCreated.Text = "Str Short Desc CW created"
        Me.OpBulkAllStrShortCWCreated.UseVisualStyleBackColor = True
        '
        'OpBulkAllStrShort
        '
        Me.OpBulkAllStrShort.AutoSize = True
        Me.OpBulkAllStrShort.Location = New System.Drawing.Point(6, 13)
        Me.OpBulkAllStrShort.Name = "OpBulkAllStrShort"
        Me.OpBulkAllStrShort.Size = New System.Drawing.Size(112, 17)
        Me.OpBulkAllStrShort.TabIndex = 23
        Me.OpBulkAllStrShort.TabStop = True
        Me.OpBulkAllStrShort.Text = "All StrShort groups"
        Me.OpBulkAllStrShort.UseVisualStyleBackColor = True
        '
        'GroupBox9
        '
        Me.GroupBox9.Controls.Add(Me.ProgressBar2)
        Me.GroupBox9.Controls.Add(Me.OPBulkDescView4)
        Me.GroupBox9.Controls.Add(Me.OPBulkDescView3)
        Me.GroupBox9.Controls.Add(Me.OPBulkDescView2)
        Me.GroupBox9.Controls.Add(Me.OPBulkDescView1)
        Me.GroupBox9.Location = New System.Drawing.Point(759, 43)
        Me.GroupBox9.Name = "GroupBox9"
        Me.GroupBox9.Size = New System.Drawing.Size(194, 82)
        Me.GroupBox9.TabIndex = 77
        Me.GroupBox9.TabStop = False
        Me.GroupBox9.Text = "View Type"
        '
        'ProgressBar2
        '
        Me.ProgressBar2.Location = New System.Drawing.Point(-242, -5)
        Me.ProgressBar2.Name = "ProgressBar2"
        Me.ProgressBar2.Size = New System.Drawing.Size(226, 22)
        Me.ProgressBar2.TabIndex = 106
        '
        'OPBulkDescView4
        '
        Me.OPBulkDescView4.AutoSize = True
        Me.OPBulkDescView4.Location = New System.Drawing.Point(6, 59)
        Me.OPBulkDescView4.Name = "OPBulkDescView4"
        Me.OPBulkDescView4.Size = New System.Drawing.Size(63, 17)
        Me.OPBulkDescView4.TabIndex = 28
        Me.OPBulkDescView4.TabStop = True
        Me.OPBulkDescView4.Text = "StrShort"
        Me.OPBulkDescView4.UseVisualStyleBackColor = True
        '
        'OPBulkDescView3
        '
        Me.OPBulkDescView3.AutoSize = True
        Me.OPBulkDescView3.Location = New System.Drawing.Point(6, 44)
        Me.OPBulkDescView3.Name = "OPBulkDescView3"
        Me.OPBulkDescView3.Size = New System.Drawing.Size(101, 17)
        Me.OPBulkDescView3.TabIndex = 25
        Me.OPBulkDescView3.TabStop = True
        Me.OPBulkDescView3.Text = "StrShort, StrTag"
        Me.OPBulkDescView3.UseVisualStyleBackColor = True
        '
        'OPBulkDescView2
        '
        Me.OPBulkDescView2.AutoSize = True
        Me.OPBulkDescView2.Location = New System.Drawing.Point(6, 29)
        Me.OPBulkDescView2.Name = "OPBulkDescView2"
        Me.OPBulkDescView2.Size = New System.Drawing.Size(133, 17)
        Me.OPBulkDescView2.TabIndex = 24
        Me.OPBulkDescView2.TabStop = True
        Me.OPBulkDescView2.Text = "StrShort, StrTag, Client"
        Me.OPBulkDescView2.UseVisualStyleBackColor = True
        '
        'OPBulkDescView1
        '
        Me.OPBulkDescView1.AutoSize = True
        Me.OPBulkDescView1.Location = New System.Drawing.Point(6, 14)
        Me.OPBulkDescView1.Name = "OPBulkDescView1"
        Me.OPBulkDescView1.Size = New System.Drawing.Size(185, 17)
        Me.OPBulkDescView1.TabIndex = 23
        Me.OPBulkDescView1.TabStop = True
        Me.OPBulkDescView1.Text = "StrShort, StrTag, Client, Veh Type"
        Me.OPBulkDescView1.UseVisualStyleBackColor = True
        '
        'LBLDescCountAll
        '
        Me.LBLDescCountAll.AutoSize = True
        Me.LBLDescCountAll.ImageAlign = System.Drawing.ContentAlignment.BottomLeft
        Me.LBLDescCountAll.Location = New System.Drawing.Point(329, 13)
        Me.LBLDescCountAll.Name = "LBLDescCountAll"
        Me.LBLDescCountAll.Size = New System.Drawing.Size(35, 13)
        Me.LBLDescCountAll.TabIndex = 74
        Me.LBLDescCountAll.Text = "Count"
        Me.LBLDescCountAll.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        '
        'DataGridViewStringsBulk
        '
        Me.DataGridViewStringsBulk.AllowUserToAddRows = False
        Me.DataGridViewStringsBulk.AllowUserToDeleteRows = False
        Me.DataGridViewStringsBulk.BackgroundColor = System.Drawing.Color.White
        Me.DataGridViewStringsBulk.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridViewStringsBulk.GridColor = System.Drawing.Color.White
        Me.DataGridViewStringsBulk.Location = New System.Drawing.Point(6, 43)
        Me.DataGridViewStringsBulk.Name = "DataGridViewStringsBulk"
        Me.DataGridViewStringsBulk.ReadOnly = True
        Me.DataGridViewStringsBulk.RowHeadersVisible = False
        Me.DataGridViewStringsBulk.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
        Me.DataGridViewStringsBulk.Size = New System.Drawing.Size(747, 521)
        Me.DataGridViewStringsBulk.TabIndex = 73
        '
        'GroupBox5
        '
        Me.GroupBox5.Controls.Add(Me.OpShortDescAllAll)
        Me.GroupBox5.Controls.Add(Me.OpShortDescBikesQuadsAll)
        Me.GroupBox5.Controls.Add(Me.OpShortDescCarsLcvOthersAll)
        Me.GroupBox5.Controls.Add(Me.OpShortDescOthersAll)
        Me.GroupBox5.Controls.Add(Me.OpShortDescLcvAll)
        Me.GroupBox5.Controls.Add(Me.OpShortDescBikesAll)
        Me.GroupBox5.Controls.Add(Me.OpShortDescCarsAll)
        Me.GroupBox5.Location = New System.Drawing.Point(759, 200)
        Me.GroupBox5.Name = "GroupBox5"
        Me.GroupBox5.Size = New System.Drawing.Size(107, 136)
        Me.GroupBox5.TabIndex = 72
        Me.GroupBox5.TabStop = False
        Me.GroupBox5.Text = "Vehicle Category"
        '
        'OpShortDescAllAll
        '
        Me.OpShortDescAllAll.AutoSize = True
        Me.OpShortDescAllAll.Location = New System.Drawing.Point(6, 105)
        Me.OpShortDescAllAll.Name = "OpShortDescAllAll"
        Me.OpShortDescAllAll.Size = New System.Drawing.Size(36, 17)
        Me.OpShortDescAllAll.TabIndex = 28
        Me.OpShortDescAllAll.TabStop = True
        Me.OpShortDescAllAll.Text = "All"
        Me.OpShortDescAllAll.UseVisualStyleBackColor = True
        '
        'OpShortDescBikesQuadsAll
        '
        Me.OpShortDescBikesQuadsAll.AutoSize = True
        Me.OpShortDescBikesQuadsAll.Location = New System.Drawing.Point(6, 90)
        Me.OpShortDescBikesQuadsAll.Name = "OpShortDescBikesQuadsAll"
        Me.OpShortDescBikesQuadsAll.Size = New System.Drawing.Size(87, 17)
        Me.OpShortDescBikesQuadsAll.TabIndex = 27
        Me.OpShortDescBikesQuadsAll.TabStop = True
        Me.OpShortDescBikesQuadsAll.Text = "Bikes/Quads"
        Me.OpShortDescBikesQuadsAll.UseVisualStyleBackColor = True
        '
        'OpShortDescCarsLcvOthersAll
        '
        Me.OpShortDescCarsLcvOthersAll.AutoSize = True
        Me.OpShortDescCarsLcvOthersAll.Location = New System.Drawing.Point(6, 75)
        Me.OpShortDescCarsLcvOthersAll.Name = "OpShortDescCarsLcvOthersAll"
        Me.OpShortDescCarsLcvOthersAll.Size = New System.Drawing.Size(95, 17)
        Me.OpShortDescCarsLcvOthersAll.TabIndex = 26
        Me.OpShortDescCarsLcvOthersAll.TabStop = True
        Me.OpShortDescCarsLcvOthersAll.Text = "Car/Lcv/Other"
        Me.OpShortDescCarsLcvOthersAll.UseVisualStyleBackColor = True
        '
        'OpShortDescOthersAll
        '
        Me.OpShortDescOthersAll.AutoSize = True
        Me.OpShortDescOthersAll.Location = New System.Drawing.Point(6, 60)
        Me.OpShortDescOthersAll.Name = "OpShortDescOthersAll"
        Me.OpShortDescOthersAll.Size = New System.Drawing.Size(56, 17)
        Me.OpShortDescOthersAll.TabIndex = 25
        Me.OpShortDescOthersAll.TabStop = True
        Me.OpShortDescOthersAll.Text = "Others"
        Me.OpShortDescOthersAll.UseVisualStyleBackColor = True
        '
        'OpShortDescLcvAll
        '
        Me.OpShortDescLcvAll.AutoSize = True
        Me.OpShortDescLcvAll.Location = New System.Drawing.Point(6, 45)
        Me.OpShortDescLcvAll.Name = "OpShortDescLcvAll"
        Me.OpShortDescLcvAll.Size = New System.Drawing.Size(43, 17)
        Me.OpShortDescLcvAll.TabIndex = 24
        Me.OpShortDescLcvAll.TabStop = True
        Me.OpShortDescLcvAll.Text = "Lcv"
        Me.OpShortDescLcvAll.UseVisualStyleBackColor = True
        '
        'OpShortDescBikesAll
        '
        Me.OpShortDescBikesAll.AutoSize = True
        Me.OpShortDescBikesAll.Location = New System.Drawing.Point(6, 30)
        Me.OpShortDescBikesAll.Name = "OpShortDescBikesAll"
        Me.OpShortDescBikesAll.Size = New System.Drawing.Size(83, 17)
        Me.OpShortDescBikesAll.TabIndex = 23
        Me.OpShortDescBikesAll.TabStop = True
        Me.OpShortDescBikesAll.Text = "MotorCycles"
        Me.OpShortDescBikesAll.UseVisualStyleBackColor = True
        '
        'OpShortDescCarsAll
        '
        Me.OpShortDescCarsAll.AutoSize = True
        Me.OpShortDescCarsAll.Location = New System.Drawing.Point(6, 15)
        Me.OpShortDescCarsAll.Name = "OpShortDescCarsAll"
        Me.OpShortDescCarsAll.Size = New System.Drawing.Size(46, 17)
        Me.OpShortDescCarsAll.TabIndex = 1
        Me.OpShortDescCarsAll.TabStop = True
        Me.OpShortDescCarsAll.Text = "Cars"
        Me.OpShortDescCarsAll.UseVisualStyleBackColor = True
        '
        ''UspGetMatrixBindingSource
        ''
        'Me.UspGetMatrixBindingSource.DataMember = "usp_GetMatrix"
        'Me.UspGetMatrixBindingSource.DataSource = Me.DictionaryDataSet
        '
        'DictionaryDataSet
        '
        Me.DictionaryDataSet.DataSetName = "DictionaryDataSet"
        Me.DictionaryDataSet.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema
        '
        ''UspGetMatrixBindingSource2
        ''
        'Me.UspGetMatrixBindingSource2.DataMember = "usp_GetMatrix"
        'Me.UspGetMatrixBindingSource2.DataSource = Me.DictionaryDataSet1
        '
        'DictionaryDataSet1
        '
        Me.DictionaryDataSet1.DataSetName = "DictionaryDataSet1"
        Me.DictionaryDataSet1.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema
        '
        ''UspGetMatrixBindingSource1
        ''
        'Me.UspGetMatrixBindingSource1.DataMember = "usp_GetMatrix"
        'Me.UspGetMatrixBindingSource1.DataSource = Me.DictionaryDataSet1
        ''
        ''Usp_GetMatrixTableAdapter1
        ''
        'Me.Usp_GetMatrixTableAdapter1.ClearBeforeFill = True
        ''
        ''UspGetMatrixBindingSource3
        ''
        'Me.UspGetMatrixBindingSource3.DataMember = "usp_GetMatrix"
        'Me.UspGetMatrixBindingSource3.DataSource = Me.DictionaryDataSet1
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.BackColor = System.Drawing.Color.White
        Me.ClientSize = New System.Drawing.Size(1491, 686)
        Me.Controls.Add(Me.TabControl1)
        Me.Name = "Form1"
        Me.Text = "Break Out Words Access to SQL"
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox1.PerformLayout()
        Me.TabControl1.ResumeLayout(False)
        Me.TabPage1.ResumeLayout(False)
        Me.TabPage1.PerformLayout()
        Me.TabPage4.ResumeLayout(False)
        Me.TabPage4.PerformLayout()
        Me.TabPage6.ResumeLayout(False)
        Me.TabPage6.PerformLayout()
        Me.GroupBox2.ResumeLayout(False)
        Me.GroupBox2.PerformLayout()
        Me.GroupBox12.ResumeLayout(False)
        Me.GroupBox12.PerformLayout()
        Me.GroupBox9.ResumeLayout(False)
        Me.GroupBox9.PerformLayout()
        CType(Me.DataGridViewStringsBulk, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox5.ResumeLayout(False)
        Me.GroupBox5.PerformLayout()
        'CType(Me.UspGetMatrixBindingSource, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DictionaryDataSet, System.ComponentModel.ISupportInitialize).EndInit()
        'CType(Me.UspGetMatrixBindingSource2, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.DictionaryDataSet1, System.ComponentModel.ISupportInitialize).EndInit()
        'CType(Me.UspGetMatrixBindingSource1, System.ComponentModel.ISupportInitialize).EndInit()
        'CType(Me.UspGetMatrixBindingSource3, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub
    Friend WithEvents GroupBox1 As GroupBox
    Friend WithEvents RadioButton4 As RadioButton
    Friend WithEvents RadioButton3 As RadioButton
    Friend WithEvents RadioButton2 As RadioButton
    Friend WithEvents RadioButton1 As RadioButton
    Friend WithEvents RadioButton5 As RadioButton
    Friend WithEvents PanelBTN As Panel
    Friend WithEvents Label1 As Label
    Friend WithEvents ProgressBar1 As ProgressBar
    Friend WithEvents Label3 As Label
    Friend WithEvents TabControl1 As TabControl
    Friend WithEvents TabPage1 As TabPage
    Friend WithEvents OpShortDescBikesAll As RadioButton
    Friend WithEvents OpShortDescCarsAll As RadioButton
    Friend WithEvents OpShortDescLcvAll As RadioButton
    Friend WithEvents OpShortDescOthersAll As RadioButton
    Friend WithEvents OpShortDescBikesQuadsAll As RadioButton
    Friend WithEvents OpShortDescCarsLcvOthersAll As RadioButton
    Friend WithEvents OpShortDescAllAll As RadioButton
    Friend WithEvents UspGetMatrixBindingSource As BindingSource
    Friend WithEvents DictionaryDataSet As DictionaryDataSet
    Friend WithEvents TabPage6 As TabPage
    Friend WithEvents GroupBox5 As GroupBox

    Friend WithEvents UspGetMatrixBindingSource2 As BindingSource
    Friend WithEvents DictionaryDataSet1 As DictionaryDataSet
    Friend WithEvents UspGetMatrixBindingSource1 As BindingSource
    Friend WithEvents Usp_GetMatrixTableAdapter1 As DictionaryDataSetTableAdapters.usp_GetMatrixTableAdapter
    Friend WithEvents UspGetMatrixBindingSource3 As BindingSource
    Friend WithEvents DataGridViewStringsBulk As DataGridView
    Friend WithEvents LBLDescCountAll As Label
    Friend WithEvents TabPage4 As TabPage
    Friend WithEvents Label11 As Label
    Friend WithEvents Panel1 As Panel
    Friend WithEvents LBLShortWork As Label
    Friend WithEvents GroupBox9 As GroupBox
    Friend WithEvents OPBulkDescView4 As RadioButton
    Friend WithEvents OPBulkDescView3 As RadioButton
    Friend WithEvents OPBulkDescView2 As RadioButton
    Friend WithEvents OPBulkDescView1 As RadioButton
    Friend WithEvents GroupBox12 As GroupBox
    Friend WithEvents OpBulkAllStrShortCWCreated As RadioButton
    Friend WithEvents OpBulkAllStrShort As RadioButton
    Friend WithEvents TxtBulkShortDesc As TextBox
    Friend WithEvents BtnBulkShortDescClear As Button
    Friend WithEvents Label13 As Label
    Friend WithEvents PanelLinkShort As Panel
    Friend WithEvents CBList As ComboBox
    Friend WithEvents GroupBox2 As GroupBox
    Friend WithEvents OpUnTagBulk As RadioButton
    Friend WithEvents OpBulkTag As RadioButton
    Friend WithEvents LblBulkCount As Label
    Friend WithEvents BtnClearBulk As Button
    Friend WithEvents Label4 As Label
    Friend WithEvents TxtBoxSearchBulk As TextBox
    Friend WithEvents ListBulk As ListBox
    Friend WithEvents Label2 As Label
    Friend WithEvents PanelBtnBulkTag As Panel
    Friend WithEvents ProgressBar2 As ProgressBar
    Friend WithEvents CBBulkSearchDesc As ComboBox

End Class

Open in new window


module code
Imports System
'Imports System.String
Imports System.Text.RegularExpressions
Imports System.Data
Imports System.Data.SqlClient
'Imports System.Data.OleDb
Module Module1
    Public Function RemoveUnwantedChr(ByVal StrIn As String) As String

        Dim ResultString As String
        ResultString = ""
        Try
            Dim RegexObj As New Regex("[^a-zA-Z0-9 ]", RegexOptions.IgnoreCase)
            ResultString = RegexObj.Replace(StrIn, "")
            Return ResultString
        Catch ex As ArgumentException
            'Syntax error in the regular expression
            Return ResultString
        End Try

    End Function


    Public Function GetVehSuperCat(ByVal VehCatIn) As String
        Dim VehCat As String

        VehCat = VehCatIn

        Select Case VehCat
            Case "Car"
                GetVehSuperCat = "CarLcvOthers"
                Exit Function
            Case "Motorcycle"
                GetVehSuperCat = "BikesQuads"
                Exit Function
            Case "LCV"
                GetVehSuperCat = "CarLcvOthers"
                Exit Function
            Case "Others"
                GetVehSuperCat = "CarLcvOthers"
                Exit Function
            Case Else
                GetVehSuperCat = "Unknown"
                Exit Function
        End Select
    End Function
    Public Function DecodeVehCat(ByVal ClientCatIn As String, ByVal ClientNameIn As String) As String

        Dim ClientCat As String
        Dim ClientName As String
        Dim StrCatOut As String

        ClientCat = LCase(ClientCatIn)
        ClientName = ClientNameIn
        StrCatOut = ""


        Select Case ClientName
            Case "Smmt"

                Select Case Left(ClientCat, 1)

                    Case = Chr(97)
                        'car
                        StrCatOut = "Car"

                    Case Chr(98) To Chr(100)
                        'lcv
                        StrCatOut = "LCV"

                    Case Chr(101) To Chr(104)
                        'hcv
                        StrCatOut = "Others"
                    Case Chr(106)
                        'psv
                        StrCatOut = "Others"
                    Case Chr(109)
                        'not assigned
                        StrCatOut = "Unknown"

                    Case Chr(116)
                        'bikes
                        StrCatOut = "Motorcycle"

                    Case Chr(120)
                        'spv
                        If ClientCat = "xa" Then StrCatOut = "Motorcycle"
                        If ClientCat = "xb" Then StrCatOut = "Others"

                    Case Else
                        'other
                        StrCatOut = "Unknown"
                End Select


            Case "Abi"
                Select Case Left(ClientCat, 1)
                    Case Chr(98)
                        'bikes
                        StrCatOut = "Motorcycle"
                    Case Chr(99)
                        'car
                        StrCatOut = "Car"
                    Case Chr(108)
                        'lcv
                        StrCatOut = "LCV"
                    Case Else
                        StrCatOut = "Unknown"
                End Select

            Case "Cap"
                Select Case Left(ClientCat, 1)
                    Case Chr(98)
                        'bikes
                        StrCatOut = "Motorcycle"
                    Case Chr(99)
                        'car
                        StrCatOut = "Car"
                    Case Chr(108)
                        'lcv
                        StrCatOut = "LCV"


                    Case Else
                        StrCatOut = "Unknown"
                End Select

            Case "Glass"
                Select Case Left(ClientCat, 1)
                    Case Chr(98)
                        'bikes
                        StrCatOut = "Motorcycle"
                    Case Chr(99)
                        'car
                        StrCatOut = "Car"
                    Case Chr(108)
                        'lcv
                        StrCatOut = "LCV"

                    Case Else
                        StrCatOut = "Unknown"
                End Select



            Case "Tvi"
                Select Case Left(ClientCat, 1)
                    Case Chr(99)
                        'car
                        StrCatOut = "Car"
                    Case Chr(108)
                        'lcv
                        StrCatOut = "LCV"
                    Case Else
                        StrCatOut = "Unknown"
                End Select

        End Select


        DecodeVehCat = StrCatOut

    End Function

    Public Function GetShortStringWordsData(ByVal ClientCode As String, ByVal TagType As Boolean) As DataTable
        Dim StrProcName As String
        Dim dtWords = New DataTable
        Dim connectionString As String = "Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True"

        If TagType = False Then
            StrProcName = "usp_getUnTaggedCodeWords"
        Else
            StrProcName = "usp_getTaggedCodeWords"
        End If

        Using conn As New SqlConnection(connectionString)
            Using cmd As New SqlCommand(StrProcName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                cmd.Parameters.AddWithValue("@ClientCodeIn", ClientCode)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtWords.Load(reader)
            End Using
        End Using
        Return dtWords
    End Function
    Public Sub CreateShort()
        Dim dtCodes = New DataTable
        Dim dtCodeWords = New DataTable
        Dim dtCodeWordsT = New DataTable
        Dim cmd As New SqlCommand
        Dim CmdWords As New SqlCommand
        Dim StrClientCode As String
        Dim StrProcName As String
        Dim StrShortString As String
        Dim StrShortTaggedString As String
        Dim StrProcUpdate As String
        Dim y As Long
        StrShortString = ""
        StrShortTaggedString = ""
        StrProcUpdate = "usp_UpdateShortStrings"
        StrProcName = "usp_getUnTaggedCodeWords"
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
            cnSql.Open()
            cmd.Connection = cnSql
            cmd.CommandText = "SELECT TblWords.ClientCode FROM TblWords LEFT JOIN TblWordTags ON TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition WHERE (((TblWordTags.ClientCodeWordPosition) Is Not Null)) GROUP BY clientcode;"

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            dtCodes.Load(reader)
            Form1.ProgressBar2.Step = 1

            For Each drRecord As DataRow In dtCodes.Rows
                y = dtCodes.Rows.Count

                StrClientCode = drRecord("ClientCode")
                dtCodeWords = GetShortStringWordsData(StrClientCode, False)
                Form1.ProgressBar2.Maximum = y
                For Each drRecordW As DataRow In dtCodeWords.Rows
                    StrShortString = StrShortString & drRecordW("word") & Chr(32)
                    StrShortString = StrShortString.ToUpper()
                Next
                StrShortString = RTrim(StrShortString)
                dtCodeWordsT = GetShortStringWordsData(StrClientCode, True)

                For Each drRecordWT As DataRow In dtCodeWordsT.Rows
                    StrShortTaggedString = StrShortTaggedString & drRecordWT("word") & Chr(32)
                Next
                StrShortTaggedString = RTrim(StrShortTaggedString)
                StrShortTaggedString = StrShortTaggedString.ToUpper()

                Using cmdStr As New SqlCommand(StrProcUpdate, cnSql)
                    cmdStr.CommandType = CommandType.StoredProcedure
                    cmdStr.Parameters.AddWithValue("@ClientCodeIn", StrClientCode)
                    cmdStr.Parameters.AddWithValue("@StrShortUnTagged", StrShortString)
                    cmdStr.Parameters.AddWithValue("@StrShortTagged", StrShortTaggedString)
                    cmdStr.BeginExecuteNonQuery()
                End Using

                StrShortString = ""
                StrShortTaggedString = ""
                Form1.ProgressBar2.PerformStep()
            Next

            cnSql.Close()
        End Using
        MessageBox.Show("Finished update of strings.")
    End Sub
    Public Sub UpdateVehicleCat()
        Dim StrClientName As String
        Dim dtRecordsFromAccessSmmt As DataTable
        Dim dtRecordsFromAccessAbi As DataTable
        Dim dtRecordsFromAccessCap As DataTable
        Dim dtRecordsFromAccessGlass As DataTable
        Dim dtRecordsFromAccessTvi As DataTable
        Dim cmd As New SqlCommand
        Dim y As Long
        StrClientName = ""
        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


        y = 0

        Form1.ProgressBar1.Step = 1
        Form1.ProgressBar1.Minimum = 1

        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
            cnSql.Open()
            cmd.Connection = cnSql
            cmd.CommandText = "DELETE FROM [dbo].[TblVehCat] where ClientName= @ClientName"
            cmd.Parameters.Add(New SqlParameter("@ClientName", StrClientName))
            cmd.ExecuteNonQuery()


            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 smmt.[MVRIS CODE], smmt.[Vehicle Category Code] FROM smmt WHERE (((smmt.[Vehicle Category Code]) Is Not Null));"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccessSmmt = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccessSmmt)
                            y = dtRecordsFromAccessSmmt.Rows.Count
                        End Using
                    End Using

                    Form1.ProgressBar1.Step = 1
                    Form1.ProgressBar1.Minimum = 1
                    Form1.ProgressBar1.Maximum = y

                    For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
                        Using cmdInsertSmmt As New SqlClient.SqlCommand
                            cmdInsertSmmt.Connection = cnSql

                            cmdInsertSmmt.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory, ClientName, VehCatSuper) VALUES ('" & drAccessRecord("MVRIS CODE") & "', '" & DecodeVehCat(drAccessRecord("Vehicle Category Code"), "Smmt") & "', '" & StrClientName & "', '" & GetVehSuperCat(DecodeVehCat(drAccessRecord("Vehicle Category Code"), "Smmt")) & "' )"
                            cmdInsertSmmt.ExecuteNonQuery()


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

                    Using cnAccessAbi 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 = cnAccessAbi
                            cnAccessAbi.Open()
                            cmdSelect.CommandText = "SELECT [AbiCode],[ABI_Cat]  FROM TClient WHERE [ABI_Cat] is not null ORDER BY [AbiCode];"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccessAbi = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccessAbi)
                            y = dtRecordsFromAccessAbi.Rows.Count
                        End Using
                    End Using

                    y = dtRecordsFromAccessAbi.Rows.Count
                    Form1.ProgressBar1.Step = 1
                    Form1.ProgressBar1.Minimum = 1
                    Form1.ProgressBar1.Maximum = y

                    For Each drAccessRecord As DataRow In dtRecordsFromAccessAbi.Rows ' each smmt veh cat record
                        Using cmdInsertAbi As New SqlClient.SqlCommand
                            cmdInsertAbi.Connection = cnSql

                            cmdInsertAbi.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory, ClientName, VehCatSuper) VALUES ('" & drAccessRecord("AbiCode") & "', '" & DecodeVehCat(drAccessRecord("ABI_Cat"), "Abi") & "', '" & StrClientName & "', '" & GetVehSuperCat(DecodeVehCat(drAccessRecord("Abi_Cat"), "Abi")) & "' )"
                            cmdInsertAbi.ExecuteNonQuery()


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

                    Using cnAccessCap 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 = cnAccessCap
                            cnAccessCap.Open()
                            cmdSelect.CommandText = "SELECT [CAPId_CAPcat],[CAP_Cat]  FROM CAPDATA WHERE [CAP_Cat] is not null ORDER BY [CAPId_CAPcat];"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccessCap = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccessCap)
                            y = dtRecordsFromAccessCap.Rows.Count
                        End Using
                    End Using
                    Form1.ProgressBar1.Step = 1
                    Form1.ProgressBar1.Minimum = 1
                    Form1.ProgressBar1.Maximum = y
                    For Each drAccessRecord As DataRow In dtRecordsFromAccessCap.Rows ' each smmt veh cat record
                        Using cmdInsertCap As New SqlClient.SqlCommand
                            cmdInsertCap.Connection = cnSql

                            cmdInsertCap.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory, ClientName, VehCatSuper) VALUES ('" & drAccessRecord("CAPId_CAPcat") & "', '" & DecodeVehCat(drAccessRecord("CAP_Cat"), "Cap") & "', '" & StrClientName & "', '" & GetVehSuperCat(DecodeVehCat(drAccessRecord("Cap_Cat"), "Cap")) & "' )"
                            cmdInsertCap.ExecuteNonQuery()


                        End Using
                        Form1.ProgressBar1.PerformStep()
                        Form1.Label3.Text = "# of Files Read Veh Cat = " & Math.Round((Form1.ProgressBar1.Value.ToString / y) * 100, 2) & "%"
                        Form1.Label3.Refresh()
                    Next
                    cnSql.Close()
                Case "Glass"
                    Using cnAccessGlass 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 = cnAccessGlass
                            cnAccessGlass.Open()
                            cmdSelect.CommandText = "SELECT [GLASSid_GLASScat], [GLASS_Cat]  FROM [Glass full Table] WHERE [GLASS_Cat] is not null ORDER BY [GLASSid_GLASScat];"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccessGlass = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccessGlass)
                            y = dtRecordsFromAccessGlass.Rows.Count
                        End Using
                    End Using
                    Form1.ProgressBar1.Step = 1
                    Form1.ProgressBar1.Minimum = 1
                    Form1.ProgressBar1.Maximum = y
                    For Each drAccessRecord As DataRow In dtRecordsFromAccessGlass.Rows ' each smmt veh cat record
                        Using cmdInsertGlass As New SqlClient.SqlCommand
                            cmdInsertGlass.Connection = cnSql

                            cmdInsertGlass.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory, ClientName, VehCatSuper) VALUES ('" & drAccessRecord("GLASSid_GLASScat") & "', '" & DecodeVehCat(drAccessRecord("GLASS_Cat"), "Glass") & "', '" & StrClientName & "', '" & GetVehSuperCat(DecodeVehCat(drAccessRecord("Glass_Cat"), "Glass")) & "' )"
                            cmdInsertGlass.ExecuteNonQuery()


                        End Using
                        Form1.ProgressBar1.PerformStep()
                        Form1.Label3.Text = "# of Files Read Veh Cat = " & Math.Round((Form1.ProgressBar1.Value.ToString / y) * 100, 2) & "%"
                        Form1.Label3.Refresh()
                    Next
                    cnSql.Close()
                Case "Tvi"
                    Using cnAccessTvi 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 = cnAccessTvi
                            cnAccessTvi.Open()
                            cmdSelect.CommandText = "SELECT [Derivative_code], [TVIVehicleTyep]  FROM TVIData WHERE [TVIVehicleTyep] is not null ORDER BY [Derivative_code];"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccessTvi = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccessTvi)
                            y = dtRecordsFromAccessTvi.Rows.Count

                        End Using
                    End Using

                    Form1.ProgressBar1.Step = 1
                    Form1.ProgressBar1.Minimum = 1
                    Form1.ProgressBar1.Maximum = y

                    For Each drAccessRecord As DataRow In dtRecordsFromAccessTvi.Rows ' each smmt veh cat record
                        Using cmdInsertTvi As New SqlClient.SqlCommand
                            cmdInsertTvi.Connection = cnSql

                            cmdInsertTvi.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory, ClientName, VehCatSuper) VALUES ('" & drAccessRecord("Derivative_code") & "', '" & DecodeVehCat(drAccessRecord("TVIVehicleTyep"), "Tvi") & "', '" & StrClientName & "', '" & GetVehSuperCat(DecodeVehCat(drAccessRecord("TVIVehicleTyep"), "Tvi")) & "' )"
                            cmdInsertTvi.ExecuteNonQuery()

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

                    Next
                    cnSql.Close()
            End Select
        End Using
        MessageBox.Show("Finished All Data Collection. " & Now())
    End Sub


    Public Sub CreateWords()
        Dim StrClientName As String

        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
        Dim y As Long
        Dim dtRecordsFromAccess As DataTable
        Dim FieldNameClientCode As String
        Dim FieldDescription As String

        FieldDescription = ""
        FieldNameClientCode = ""
        dtRecordsFromAccess = Nothing

        y = 0

        Form1.ProgressBar1.Step = 1
        Form1.ProgressBar1.Minimum = 1
        Form1.ProgressBar1.Value = Form1.ProgressBar1.Minimum
        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()



            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 FROM smmt;"


                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                        cnAccess.Close()
                    End Using
                    FieldNameClientCode = "MVRIS CODE"
                    FieldDescription = "ConCatDesc"


                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 FROM TClient ORDER BY [AbiCode];"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                        cnAccess.Close()
                    End Using

                    FieldNameClientCode = "abiCode"
                    FieldDescription = "ConCatDesc"


                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 FROM CAPDATA ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                        cnAccess.Close()
                    End Using

                    FieldNameClientCode = "CAPid_CAPcat"
                    FieldDescription = "ConCatDesc"


                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 FROM  [Glass Full Table] ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                        cnAccess.Close()
                    End Using

                    FieldNameClientCode = "GLASSid_GLASScat"
                    FieldDescription = "ConCatDesc"


                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 FROM TVIDATA ORDER BY [DERIVATIVE_CODE];"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                        cnAccess.Close()
                    End Using
                    FieldNameClientCode = "DERIVATIVE_CODE"
                    FieldDescription = "ConCatDesc"


            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()


                    y = dtRecordsFromAccess.Rows.Count
                    For Each drAccessRecord As DataRow In dtRecordsFromAccess.Rows
                        'Dim StrArray() As String = Split(drAccessRecord(FieldDescription))

                        Dim StrArray = Split(drAccessRecord(FieldDescription)).Distinct(StringComparer.OrdinalIgnoreCase).ToArray()
                        'Dim StrArray = Split(drAccessRecord(FieldDescription)).Distinct(StringComparer.OrdinalIgnoreCase)

                        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
                            StrClientCodeWordPos = StrClientCodeWordPos.ToUpper()
                            StrClientCode = drAccessRecord(FieldNameClientCode)
                            StrFull = RemoveUnwantedChr(drAccessRecord(FieldDescription))
                            StrClientName = StrClientName


                            StrWord = RemoveUnwantedChr(StrArray(index))
                            StrWord = StrWord.ToUpper()

                            intWordLen = Len(StrArray(index))
                            IntWordPosition = index + 1
                            IntNoOfWords = UBound(StrArray) + 1

                            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
                cnSql.Close()
            End Using

        Catch ex As Exception


        Finally

            con.Close()
        End Try
    End Sub


End Module

Open in new window

Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
and fixed it now:
    'Private Sub BtnClearBulk_Click(sender As Object, e As EventArgs) Handles  BtnClearBulk.Click
    '    ListBulk.ClearSelected()
    'End Sub

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.