PeterBaileyUk
asked on
visual studio vb.net windows onkeyup
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.
form1 vb
Form1 designer code
module code
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 ModuleForm1 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 Classmodule 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 ModuleASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Open in new window