I'm getting this following exception to my log file when I try to run the windows service,
Exception: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.
What should I modify to avoid this exception? Thanks
What I have tried:
Imports System.Configuration
Imports Microsoft.Data.SqlClient
Public Class DataCollector_Modified
Dim timer1 As New Timers.Timer(5000)
Dim timer2 As New Timers.Timer(5000)
Dim timer3 As New Timers.Timer(5000)
Protected Overrides Sub OnStart(ByVal args() As String)
AddHandler timer1.Elapsed, AddressOf OnTimedEvent1
AddHandler timer2.Elapsed, AddressOf OnTimedEvent2
AddHandler timer3.Elapsed, AddressOf OnTimedEvent3
timer1.Enabled = True
timer2.Enabled = True
timer3.Enabled = True
FileIO.WriteLog("Service has started")
End Sub
Protected Overrides Sub OnStop()
timer1.Enabled = False
timer2.Enabled = False
timer3.Enabled = False
FileIO.WriteLog("Service has stopped")
End Sub
Private Sub OnTimedEvent1(obj As Object, e As EventArgs)
Try
ProcessOneTable1("DEV_Test_Norm_Data", 14,
"Data Source=NORMAC-CTMS;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, lot_no, default_bin, posted, labor_feet_produced FROM NOR_LABOR"
)
Catch ex As Exception
FileIO.WriteLog("Exception: " & vbCrLf & ex.Message)
End Try
End Sub
Private Sub OnTimedEvent2(obj As Object, e As EventArgs)
Try
ProcessOneTable2("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"
)
Catch ex As Exception
FileIO.WriteLog("Exception: " & vbCrLf & ex.Message)
End Try
End Sub
Private Sub OnTimedEvent3(obj As Object, e As EventArgs)
Try
ProcessOneTable3("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"
)
Catch ex As Exception
FileIO.WriteLog("Exception: " & vbCrLf & ex.Message)
End Try
End Sub
Private EdgeConnStr As String = "Data Source=STLEDGSQL01;Database=MES_DEV;Integrated Security=true"
Private Sub ProcessOneTable1(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}"))
Dim sql As String = $"INSERT INTO {destTableName} ({paramList}) SELECT * FROM NOR_LABOR t1 WHERE NOT EXISTS(SELECT ID FROM {destTableName} t2 WHERE t2.ID = t1.ID)"
Using cn As New SqlConnection(EdgeConnStr),
cmd As New SqlCommand(sql, cn)
For i As Integer = 0 To ParameterCount - 1
Dim p As SqlParameter = cmd.CreateParameter()
p.ParameterName = $"@p{i}"
cmd.Parameters.Add(p)
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
Private Sub ProcessOneTable2(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}"))
Dim sql As String = $"INSERT INTO {destTableName} ({paramList}) SELECT * FROM Setter_Labor t1 WHERE NOT EXISTS(SELECT ID FROM {destTableName} t2 WHERE t2.ID = t1.ID)"
Using cn As New SqlConnection(EdgeConnStr),
cmd As New SqlCommand(sql, cn)
For i As Integer = 0 To ParameterCount - 1
Dim p As SqlParameter = cmd.CreateParameter()
p.ParameterName = $"@p{i}"
cmd.Parameters.Add(p)
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
Private Sub ProcessOneTable3(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}"))
Dim sql As String = $"INSERT INTO {destTableName} ({paramList}) SELECT * FROM wrap_labor t1 WHERE NOT EXISTS(SELECT ID FROM {destTableName} t2 WHERE t2.ID = t1.ID)"
Using cn As New SqlConnection(EdgeConnStr),
cmd As New SqlCommand(sql, cn)
For i As Integer = 0 To ParameterCount - 1
Dim p As SqlParameter = cmd.CreateParameter()
p.ParameterName = $"@p{i}"
cmd.Parameters.Add(p)
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