Public Sub CreateWords()
Dim StrClientName As String
Dim x As Boolean
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim index As Long
Dim StrClientCodeWordPos As String
Dim StrClientCode As String
Dim StrWord As String
Dim intWordLen As Int16
Dim StrFull As String
Dim IntWordPosition As Int16
Dim IntNoOfWords As Int16
Try
StrClientName = ""
'BackColor = Color.FromArgb(107, 115, 115)
'Me.Refresh()
If Form1.RadioButton1.Checked = True Then
StrClientName = "Smmt"
End If
If Form1.RadioButton2.Checked = True Then
StrClientName = "Abi"
End If
If Form1.RadioButton3.Checked = True Then
StrClientName = "Cap"
End If
If Form1.RadioButton4.Checked = True Then
StrClientName = "Glass"
End If
If Form1.RadioButton5.Checked = True Then
StrClientName = "Tvi"
End If
con.ConnectionString = "Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True"
con.Open()
cmd.Connection = con
cmd.CommandText = "DELETE FROM [dbo].[TblWords] where ClientName= @ClientName"
cmd.Parameters.Add(New SqlParameter("@ClientName", StrClientName))
cmd.ExecuteNonQuery()
con.Close()
Dim dtRecordsFromAccess As DataTable
Dim FieldNameClientCode As String
Dim FieldDescription As String
Dim VehCatClient As String
Dim VehCatValue As String
Select Case StrClientName
Case "Smmt"
Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\SMMT\smmt master.mdb")
Using cmdSelect As New OleDb.OleDbCommand
cmdSelect.Connection = cnAccess
cnAccess.Open()
cmdSelect.CommandText = "SELECT [MVRIS CODE], [Model Range] & ' ' & [variant] as ConCatDesc, [Vehicle Category Code] FROM smmt;"
'cmdSelect.CommandText = "SELECT [MVRIS CODE], [Model Range] & ' ' & [variant] as ConCatDesc, [Vehicle Category Code] FROM smmt;"
Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
dtRecordsFromAccess = New DataTable
dtAdpt.Fill(dtRecordsFromAccess)
End Using
End Using
FieldNameClientCode = "MVRIS CODE"
FieldDescription = "ConCatDesc"
VehCatClient = "Vehicle Category Code"
Case "Abi"
Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Abi\Abi Master.mdb")
Using cmdSelect As New OleDb.OleDbCommand
cmdSelect.Connection = cnAccess
cnAccess.Open()
cmdSelect.CommandText = "SELECT [AbiCode], [MODEL_DESCRIPTION] as ConCatDesc, Abi_Cat FROM TClient ;"
Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
dtRecordsFromAccess = New DataTable
dtAdpt.Fill(dtRecordsFromAccess)
End Using
End Using
FieldNameClientCode = "abiCode"
FieldDescription = "ConCatDesc"
VehCatClient = "Abi_Cat"
Case "Cap"
Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Cap\Cap Master.mdb")
Using cmdSelect As New OleDb.OleDbCommand
cmdSelect.Connection = cnAccess
cnAccess.Open()
cmdSelect.CommandText = "SELECT [CAPid_CAPcat], [LongModelDescription] & ' ' & [ShortDerivativeDescription] as ConCatDesc, CAP_cat FROM CAPDATA ;"
Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
dtRecordsFromAccess = New DataTable
dtAdpt.Fill(dtRecordsFromAccess)
End Using
End Using
FieldNameClientCode = "CAPid_CAPcat"
FieldDescription = "ConCatDesc"
VehCatClient = "CAP_Cat"
Case "Glass"
Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Glass\Glass Master 10.mdb")
Using cmdSelect As New OleDb.OleDbCommand
cmdSelect.Connection = cnAccess
cnAccess.Open()
cmdSelect.CommandText = "SELECT [GLASSid_GLASScat], [Range_desc] & ' ' & [Trim_desc] as ConCatDesc, GLASS_cat FROM [Glass Full Table] ;"
Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
dtRecordsFromAccess = New DataTable
dtAdpt.Fill(dtRecordsFromAccess)
End Using
End Using
FieldNameClientCode = "GLASSid_GLASScat"
FieldDescription = "ConCatDesc"
VehCatClient = "GLASS_cat"
Case "Tvi"
Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Thatcham\Tvi Master 4.mdb")
Using cmdSelect As New OleDb.OleDbCommand
cmdSelect.Connection = cnAccess
cnAccess.Open()
cmdSelect.CommandText = "SELECT [DERIVATIVE_CODE], [Model] & ' ' & [Trim] as ConCatDesc, TVIVehicleTyep FROM TVIDATA ;"
Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
dtRecordsFromAccess = New DataTable
dtAdpt.Fill(dtRecordsFromAccess)
End Using
End Using
FieldNameClientCode = "DERIVATIVE_CODE"
FieldDescription = "ConCatDesc"
VehCatClient = "TVIVehicleTyep"
End Select
Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
Using cmdInsert As New SqlClient.SqlCommand
cmdInsert.Connection = cnSql
cnSql.Open()
Dim y As Long
Dim Z As Double
Dim Counter As Long
y = dtRecordsFromAccess.Rows.Count
For Each drAccessRecord As DataRow In dtRecordsFromAccess.Rows
Dim StrArray() As String = Split(drAccessRecord(FieldDescription))
Form1.ProgressBar1.Step = 1
Form1.ProgressBar1.Minimum = 1
Form1.ProgressBar1.Maximum = y
For index = LBound(StrArray) To UBound(StrArray)
StrClientCodeWordPos = drAccessRecord(FieldNameClientCode) & "_" & RemoveUnwantedChr(StrArray(index)) & "_" & index + 1
StrClientCode = drAccessRecord(FieldNameClientCode)
StrFull = RemoveUnwantedChr(drAccessRecord(FieldDescription))
StrClientName = StrClientName
StrWord = RemoveUnwantedChr(StrArray(index))
intWordLen = Len(StrArray(index))
IntWordPosition = index + 1
IntNoOfWords = UBound(StrArray) + 1
VehCatValue = drAccessRecord(VehCatClient)
'Insert records in SQL database
cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords, VehCategory) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & ", '" & VehCatValue & "')"
'cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & "')"
cmdInsert.ExecuteNonQuery()
Next index
Form1.ProgressBar1.PerformStep()
Form1.Label3.Text = "# of Files Read = " & Math.Round((Form1.ProgressBar1.Value.ToString / y) * 100, 2) & "%"
Form1.Label3.Refresh()
Next
End Using
End Using
Catch ex As Exception
Finally
con.Close()
End Try
End Sub
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.