Windows service using VB.NET doesn't work?

Question

What my program supposed to do : My goal is to create a windows service which acts as a mediator between multiple SQL databases. In total there are 3 different tables in 3 different servers In detail, when this service runs it should oversee the data in the "Table1" and copy it to the "Table2" in periodical time(every 1 minute). But tricky part is it cannot paste duplicate records, has to check for "Table2" 's ID field and validate for not pasting the same record with the same ID.I've added a diagram for understanding purposes of what my goal is

What I've done : So far I've developed the code completely but the issue is it only copies data from one table(specifically "NOR_LABOR" according to the diagram I've attached) to "DEV_Test_Nor_Data" in the "MES_DEV" Database(con1 to con2). According to the diagram con1, con3, con4 are tables "NOR_LABOR", "SETTER_LABOR" and "wrap_labor" respectively. con2 is the destination which is "MES_DEV" DB.

Can anybody figure out why does other table's data(con3 to con2 & con4 to con2) won't copy?

My code - Data Collector

Imports System.Configuration
Imports System.Data.SqlClient

Public Class DataCollector

    Dim con1, con2, con3, con4 As New SqlConnection
    Dim timer1 As Timers.Timer
    Dim p_oConn As New Wisys.AllSystem.ConnectionInfo
    Protected Overrides Sub OnStart(ByVal args() As String)

        con1 = New SqlConnection("Data Source=NORMAC-CTMS\SQLEXPRESS;Database=Normac Data;Integrated Security=true")
        con1.Open()
        con2 = New SqlConnection("Data Source=STLEDGSQL01;Database=MES_DEV;Integrated Security=true")
        con2.Open()
        con3 = New SqlConnection("Data Source=201706-SETTER1\SQLEXPRESS;Database=Edge;Integrated Security=true")
        con3.Open()
        con4 = New SqlConnection("Data Source=PRINTER\SQLEXPRESS;Database=Wrapper Data;Integrated Security=true")
        con4.Open()

        timer1 = New Timers.Timer()
        timer1.Interval = 5000
        AddHandler timer1.Elapsed, AddressOf OnTimedEvent
        timer1.Enabled = True
        FileIO.WriteLog("Service has started")

    End Sub

    Protected Overrides Sub OnStop()
        timer1.Enabled = False
        FileIO.WriteLog("Service has stopped")
        con1.Close()
        con2.Close()
        con3.Close()
        con4.Close()
    End Sub


    Private Sub OnTimedEvent(obj As Object, e As EventArgs)

        Dim cmd1, cmd2, cmd3 As SqlCommand

        'Connecting the Normac Data table
        Dim da1 As SqlDataAdapter = New SqlDataAdapter("select ID, trx_date, work_order, department, work_center, operation_no, operator, total_labor_hours, feet_produced, item_no, posted, labor_feet_produced from NOR_LABOR", con1)
        Dim cb1 As SqlCommandBuilder = New SqlCommandBuilder(da1)
        Dim dt1 As DataTable = New DataTable()
        da1.Fill(dt1)

        'Connecting the Setter_Labor table
        Dim da2 As SqlDataAdapter = New SqlDataAdapter("select ID, trx_date, work_order, department, work_center, operation_no, operator, total_labor_hours, labor_feet_produced, item_no, posted from Setter_Labor", con3)
        Dim cb2 As SqlCommandBuilder = New SqlCommandBuilder(da2)
        Dim dt2 As DataTable = New DataTable()
        da2.Fill(dt2)

        'Connecting the Wrap_Labor table
        Dim da3 As SqlDataAdapter = New SqlDataAdapter("select ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete from wrap_labor", con4)
        Dim cb3 As SqlCommandBuilder = New SqlCommandBuilder(da3)
        Dim dt3 As DataTable = New DataTable()
        da3.Fill(dt3)


        Dim i, j, k As Integer

        'Inserting into DEV_Test_Nor_Data table
        For Each dr As DataRow In dt1.Rows

            cmd1 = New SqlCommand("Insert into DEV_Test_Nor_Data values('" & dr(0) & "','" & dr(1) & "','" & dr(2) & "','" & dr(3) & "','" & dr(4) & "','" & dr(5) & "','" & dr(6) & "','" & dr(7) & "','" & dr(8) & "','" & dr(9) & "','" & dr(10) & "','" & dr(11) & "')", con2)
            i = cmd1.ExecuteNonQuery()


        Next

        'Inserting into DEV_Test_Set_Lbr table
        For Each dr As DataRow In dt2.Rows

            cmd2 = New SqlCommand("Insert into DEV_Test_Set_Lbr values('" & dr(0) & "','" & dr(1) & "','" & dr(2) & "','" & dr(3) & "','" & dr(4) & "','" & dr(5) & "','" & dr(6) & "','" & dr(7) & "','" & dr(8) & "','" & dr(9) & "','" & dr(10) & "')", con2)
            j = cmd2.ExecuteNonQuery()

        Next

        'Inserting into DEV_Test_Wrp_Lbr table
        For Each dr As DataRow In dt3.Rows

            cmd3 = New SqlCommand("Insert into DEV_Test_Wrp_Lbr values('" & dr(0) & "','" & dr(1) & "','" & dr(2) & "','" & dr(3) & "','" & dr(4) & "','" & dr(5) & "','" & dr(6) & "','" & dr(7) & "','" & dr(8) & "','" & dr(9) & "','" & dr(10) & "','" & dr(11) & "','" & dr(12) & "','" & dr(13) & "','" & dr(14) & "','" & dr(15) & "','" & dr(16) & "','" & dr(17) & "','" & dr(18) & "','" & dr(19) & "','" & dr(20) & "')", con2)
            k = cmd3.ExecuteNonQuery()

        Next


        da1.Update(dt1)
        cmd1.Dispose()
        dt1.Dispose()
        da1.Dispose()

        da2.Update(dt2)
        cmd2.Dispose()
        dt2.Dispose()
        da2.Dispose()

        da3.Update(dt3)
        cmd3.Dispose()
        dt3.Dispose()
        da3.Dispose()

    End Sub
End Class

Reference2

Reference3

Reference4

Answer 1

See if this helps. It may be the parameterized queries are your entire problem.

Public Class DataCollector

    'Question text said one minute
    Private timer1 As New Timers.Timer(60000)

    Protected Overrides Sub OnStart(ByVal args() As String)
        AddHandler timer1.Elapsed, AddressOf OnTimedEvent
        timer1.Enabled = True
        FileIO.WriteLog("Service has started")
    End Sub

    Protected Overrides Sub OnStop()
        timer1.Enabled = False
        FileIO.WriteLog("Service has stopped")
    End Sub

    Private Sub OnTimedEvent(obj As Object, e As EventArgs)
        ' DEV_Test_Nor_Data Table
        ProcessOneTable("DEV_Test_Nor_Data", 12,
            "Data Source=NORMAC-CTMS\SQLEXPRESS;Database=Normac Data;Integrated Security=true", 
            "SELECT ID, trx_date, work_order, department, work_center, operation_no, operator, total_labor_hours, feet_produced, item_no, posted, labor_feet_produced FROM NOR_LABOR"
        )

        ' DEV_Test_Set_Lbr Table
        ProcessOneTable("DEV_Test_Set_Lbr", 11, 
            "Data Source=201706-SETTER1\SQLEXPRESS;Database=Edge;Integrated Security=true",
            "SELECT ID, trx_date, work_order, department, work_center, operation_no, operator, total_labor_hours, labor_feet_produced, item_no, posted from Setter_Labor"
        )

        ' DEV_Test_Wrp_Lbr Table
        ProcessOneTable("DEV_Test_Wrp_Lbr", 21,
            "Data Source=PRINTER\SQLEXPRESS;Database=Wrapper Data;Integrated Security=true",
            "SELECT ID, trx_date, work_order, Department, work_center, operation_no, operator, total_labor_hrs, job_start, job_end, qty_ordered, qty_produced, item_no, lot_no, default_bin, posted, wrapped, total_shift_hrs, check_emp, machine, operation_complete from wrap_labor"
        )
    End Sub

    Private EdgeConnStr As String = "Data Source=STLEDGSQL01;Database=MES_DEV;Integrated Security=true"

    Private Sub ProcessOneTable(destTableName As String, ParameterCount As Integer, sourceConnectionString AS String, sourceSql As String)
        Dim data As New DataTable()
        Using sourceConn As New SqlConnection(sourceConnectionString), _
              da As New SqlDataAdapter(sourceSql, sourceConn)
            da.Fill(data)
        End Using

        Dim paramList As String = String.Join(",", Enumerable.Range(0, ParameterCount).Select(Function(p) $"@p{p}"))
        ' Assumes first parateter (@p0) is always the ID.
        Dim sql As String = $"INSERT INTO {destTableName} SELECT {paramList} WHERE NOT EXISTS(SELECT ID FROM {destTableName} WHERE ID = @p0)"
        Using cn As New SqlConnection(EdgeConnStr), _
              cmd As New SqlCommand(sql, cn)

            For i As Integer = 0 To ParameterCount - 1
                cmd.Parameters.Add($"@p{i}", SqlDbType.VarChar)
            Next i
        
            cn.Open()
            For Each dr As DataRow In data.Rows
                For i As Integer = 0 to ParameterCount - 1
                    cmd.Parameters(i).Value = dr(i)
                Next i
                cmd.ExecuteNonQuery()
            Next dr
        End Using
    End Sub
End Class

It sounds like you also need to worry about merging the data, but start with this anyway; it fixes the HUGE GAPING SECURITY ISSUE in the original code, as well as isolating the important part of the code down to the minimum possible method size. This will make it easier to refactor just that part to also worry about what IDs may already exist... but I'll let you make an attempt at that yourself first (hint: INSERT + SELECT + WHERE NOT EXISTS() all in the same query)