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
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))
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
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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE