Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

VB.NET
Imports System.Configuration
Imports Microsoft.Data.SqlClient

Public Class DataCollector_Modified

    'Question text said one minute
    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
            ' DEV_Test_Norm_Data Table
            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
            ' DEV_Test_Set_Lbr Table
            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
            ' DEV_Test_Wrp_Lbr Table
            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
Posted
Updated 16-Apr-21 8:15am
v2

1 solution

Try replacing:
VB.NET
cmd.Parameters.Add($"@p{i}")
with:
VB.NET
Dim p As SqlParameter = cmd.CreateParameter()
p.ParameterName = $"@p{i}"
cmd.Parameters.Add(p)
 
Share this answer
 
Comments
GlennAshan 16-Apr-21 14:12pm    
Wow you're amazing Richard. Thank you very much! Can you help me with one more question if possible please and thank you.

I updated my current INSERT queries to

"INSERT INTO {destTableName} ({paramList}) SELECT * FROM NOR_LABOR t1 WHERE NOT EXISTS(SELECT ID FROM {destTableName} t2 WHERE t2.ID = t1.ID)"

"INSERT INTO {destTableName} ({paramList}) SELECT * FROM Setter_Labor t1 WHERE NOT EXISTS(SELECT ID FROM {destTableName} t2 WHERE t2.ID = t1.ID)"

"INSERT INTO {destTableName} ({paramList}) SELECT * FROM wrap_labor t1 WHERE NOT EXISTS(SELECT ID FROM {destTableName} t2 WHERE t2.ID = t1.ID)" so that it won't copy any duplicates from sources to destinations. But it's giving me some exceptions
Exception: Invalid object name 'Setter_Labor'
Exception: Invalid object name 'NOR_LABOR'
Exception: Invalid object name 'wrap_labor'

Any suggestions?
RedDk 16-Apr-21 14:50pm    
Invalid object name exception says that those "name" objects don't exist. Check all your code for the keyword "CREATE TABLE". I do this by searching my code base for that phrase. In SSMSE, double-clicking on a line in the find return will actually open that script. Then you can narrow your search using those "name" objects or, as would be the case were the actual location was now open, you can visually see each table name.

Not there? Well then, you're out of luck.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900