For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
'********************************************************************************************************
cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
cmdInsertSmmt.Parameters.AddWithValue("@ClientCode", drAccessRecord("MVRIS CODE"))
cmdInsertSmmt.Parameters.AddWithValue("@VehCategory", drAccessRecord("Vehicle Category Code"))
cmdInsertSmmt.ExecuteNonQuery()
Next
Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
Using cmdInsertSmmt As New SqlClient.SqlCommand
cmdInsertSmmt.Connection = cnSqlSmmt
cnSqlSmmt.Open()
Dim y As Long
y = dtRecordsFromAccessSmmt.Rows.Count
For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
Public Sub UpdateVehicleCat()
Dim dtRecordsFromAccessSmmt As DataTable
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], [Vehicle Category Code] FROM smmt;"
Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
dtRecordsFromAccessSmmt = New DataTable
dtAdpt.Fill(dtRecordsFromAccessSmmt)
End Using
End Using
Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
Using cmdInsertSmmt As New SqlClient.SqlCommand
cmdInsertSmmt.Connection = cnSqlSmmt
cnSqlSmmt.Open()
Dim y As Long
y = dtRecordsFromAccessSmmt.Rows.Count
For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
cmdInsertSmmt.Parameters.Add("@ClientCode", SqlDbType.NVarChar, 50).Value = drAccessRecord("MVRIS CODE")
cmdInsertSmmt.Parameters.Add("@VehCategory", SqlDbType.NVarChar, 50).Value = drAccessRecord("Vehicle Category Code")
cmdInsertSmmt.ExecuteNonQuery()
Next
End Using
cnSqlSmmt.Close()
End Using
End Sub
Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
Dim y As Long
y = dtRecordsFromAccessSmmt.Rows.Count
For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
Using cmdInsertSmmt As New SqlClient.SqlCommand
cmdInsertSmmt.Connection = cnSqlSmmt
cnSqlSmmt.Open()
cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
cmdInsertSmmt.Parameters.Add("@ClientCode", SqlDbType.NVarChar, 50).Value = drAccessRecord("MVRIS CODE")
cmdInsertSmmt.Parameters.Add("@VehCategory", SqlDbType.NVarChar, 50).Value = drAccessRecord("Vehicle Category Code")
cmdInsertSmmt.ExecuteNonQuery()
cnSqlSmmt.Close()
End Using
Next
End Using
End Sub
Public Sub UpdateVehicleCat()
Dim dtRecordsFromAccessSmmt As DataTable
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], [Vehicle Category Code] FROM smmt where [Vehicle Category Code] is not null ORDER BY [MVRIS CODE];"
Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
dtRecordsFromAccessSmmt = New DataTable
dtAdpt.Fill(dtRecordsFromAccessSmmt)
End Using
End Using
Using cnSqlSmmt As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
Dim y As Long
y = dtRecordsFromAccessSmmt.Rows.Count
For Each drAccessRecord As DataRow In dtRecordsFromAccessSmmt.Rows ' each smmt veh cat record
Using cmdInsertSmmt As New SqlClient.SqlCommand
cmdInsertSmmt.Connection = cnSqlSmmt
cnSqlSmmt.Open()
cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
cmdInsertSmmt.Parameters.Add("@ClientCode", SqlDbType.NVarChar, 50).Value = drAccessRecord("MVRIS CODE")
cmdInsertSmmt.Parameters.Add("@VehCategory", SqlDbType.NVarChar, 50).Value = drAccessRecord("Vehicle Category Code")
cmdInsertSmmt.ExecuteNonQuery()
cnSqlSmmt.Close()
End Using
Next
End Using
MessageBox.Show("Finished Update Vehicle Category.")
End Sub
cmdInsertSmmt.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory) VALUES ('" & drAccessRecord("MVRIS CODE") & "', '" & drAccessRecord("Vehicle Category Code") & "','" & " )"
cmdInsertSmmt.ExecuteNonQuery()
cmdInsertSmmt.CommandText = "INSERT INTO TblVehCat (ClientCode, VehCategory) VALUES ('" & drAccessRecord("MVRIS CODE") & "', '" & drAccessRecord("Vehicle Category Code") & "'" & " )"
cmdInsertSmmt.CommandText = "UPDATE dbo.TblWords SET VehCategory = @VehCategory WHERE ClientCode = @ClientCode "
cmdInsertSmmt.Parameters.A
cmdInsertSmmt.Parameters.A
Obviously you'd change the "SqlDBType.VarChar" to reflect the actual datatypes.