VB.Net - Insert .CSV file records after checking for duplicates

Question

I need to monitor multiple folders in a directory and insert records from multiple .csv files in each folder. I have used filesystemwatcher for monitoring. Now I need to check and insert records from each updated/newly created .csv file into SQL Server database. I cannot have unique field as there might be field values that can be duplicated. So I am using a combination of three fields to check for duplication and insert the .csv file record only if it does not exist in the database. The below code throws an error on the line cmdInsertSQL.ExecuteNonQuery() as:

The parameterized query '(@ColumnA int,@ColumnB datetime,@ColumnC time(7),@ColumnD varcha' expects the parameter '@ColumnA', which was not supplied.

Below is the code:

Private Sub ReadAndInsertCSVData()
Dim table As New DataTable()
table.Columns.Add("ColumnA")
table.Columns.Add("ColumnB")
table.Columns.Add("ColumnC")
table.Columns.Add("ColumnD")
table.Columns.Add("ColumnE")
table.Columns.Add("ColumnF")
table.Columns.Add("ColumnG")
table.Columns.Add("ColumnH")
table.Columns.Add("ColumnI")
table.Columns.Add("ColumnJ")
table.Columns.Add("ColumnK")
table.Columns.Add("ColumnL")
table.Columns.Add("ColumnM")
table.Columns.Add("ColumnN")
table.Columns.Add("ColumnO")
table.Columns.Add("ColumnP")
table.Columns.Add("ColumnQ")
table.Columns.Add("ColumnR")
table.Columns.Add("ColumnS")
table.Columns.Add("ColumnT")
table.Columns.Add("ColumnU")
table.Columns.Add("ColumnV")
table.Columns.Add("ColumnW")
table.Columns.Add("MetNo")
Dim parser As New FileIO.TextFieldParser("C:\Folder1\SD08.CSV")
parser.Delimiters = New String() {","}
parser.HasFieldsEnclosedInQuotes = True
parser.TrimWhiteSpace = True
Do Until parser.EndOfData = True
    table.Rows.Add(parser.ReadFields())
Loop
For Each row As DataRow In table.Rows
    row("MetNo") = lblMetNo.Text
Next
Dim strSQLConnectionString As String = "Data Source=ServerName;Initial Catalog=DBName;Integrated Security=SSPI;"
Dim connTemp As New SqlClient.SqlConnection(strSQLConnectionString)
Dim connInsertSQL As New SqlClient.SqlConnection(strSQLConnectionString)
connTemp.Open()
connInsertSQL.Open()
Dim intReturn As Integer
Dim I As Integer
Dim strSQL As String = ""
Dim strSQLInsert As String = ""
Dim cmdInsertSQL As SqlClient.SqlCommand
For Each row As DataRow In table.Rows
    strSQL = "SELECT COUNT(*) AS NoOfRecords FROM MetDB WHERE ColumnA = @ColumnA AND " _
            & "ColumnB = @ColumnB AND ColumnC = @ColumnC AND MetNo = @MetNo"
    Dim cmdTemp As New SqlClient.SqlCommand(strSql, connTemp)
    With cmdTemp.Parameters
        .AddWithValue("@ColumnA", row("ColumnA"))
        .AddWithValue("@ColumnB", row("ColumnB"))
        .AddWithValue("@ColumnC", row("ColumnC"))
        .AddWithValue("@MetNo", lblMetNo.Text)
    End With
    intReturn = Convert.ToInt16(cmdTemp.ExecuteScalar)
    If (intReturn = 0) Then
        strSQLInsert = "INSERT INTO dbo.MetDB " _
                & "(ColumnA, ColumnB, ColumnC, ColumnD, ColumnE, ColumnF, ColumnG, ColumnH, ColumnI, ColumnJ, ColumnK, ColumnL, ColumnM, ColumnN, ColumnO, " _
                & "ColumnP, ColumnQ, ColumnR, ColumnS, ColumnT, ColumnU, ColumnV, ColumnW, MetNo) VALUES (@ColumnA, @ColumnB, @ColumnC, @ColumnD, " _
                & "@ColumnE, @ColumnF, @ColumnG, @ColumnH, @ColumnI, @ColumnJ, @ColumnK, @ColumnL, @ColumnM, @ColumnN, @ColumnO, @ColumnP, @ColumnQ, " _
                & "@ColumnR, @ColumnS, @ColumnT, @ColumnU, @ColumnV,  @ColumnW, @MetNo)"
        cmdInsertSQL = New SqlClient.SqlCommand(strSQLInsert, connInsertSQL)
        With cmdInsertSQL.Parameters
            cmdInsertSQL.Parameters.Add("@ColumnA", SqlDbType.Int, 4, "ColumnA")
            cmdInsertSQL.Parameters.Add("@ColumnB", SqlDbType.DateTime, 10, "ColumnB")
            cmdInsertSQL.Parameters.Add("@ColumnC", SqlDbType.Time, 8, "ColumnC")
            cmdInsertSQL.Parameters.Add("@ColumnD", SqlDbType.VarChar, 50, "ColumnD")
            cmdInsertSQL.Parameters.Add("@ColumnE", SqlDbType.VarChar, 50, "ColumnE")
            cmdInsertSQL.Parameters.Add("@ColumnF", SqlDbType.Decimal, 28, "ColumnF")
            cmdInsertSQL.Parameters("@ColumnF").Precision = 28
            cmdInsertSQL.Parameters("@ColumnF").Scale = 10
            cmdInsertSQL.Parameters.Add("@ColumnG", SqlDbType.VarChar, 50, "ColumnG")
            cmdInsertSQL.Parameters.Add("@ColumnH", SqlDbType.VarChar, 50, "ColumnH")
            cmdInsertSQL.Parameters.Add("@ColumnI", SqlDbType.Decimal, 28, "ColumnI")
            cmdInsertSQL.Parameters("@ColumnI").Precision = 28
            cmdInsertSQL.Parameters("@ColumnI").Scale = 10
            cmdInsertSQL.Parameters.Add("@ColumnJ", SqlDbType.VarChar, 50, "ColumnJ")
            cmdInsertSQL.Parameters.Add("@ColumnK", SqlDbType.Decimal, 28, "ColumnK")
            cmdInsertSQL.Parameters("@ColumnK").Precision = 28
            cmdInsertSQL.Parameters("@ColumnK").Scale = 10
            cmdInsertSQL.Parameters.Add("@ColumnL", SqlDbType.VarChar, 50, "ColumnL")
            cmdInsertSQL.Parameters.Add("@ColumnM", SqlDbType.Int, 10, "ColumnM")
            cmdInsertSQL.Parameters.Add("@ColumnN", SqlDbType.VarChar, 50, "ColumnN")
            cmdInsertSQL.Parameters.Add("@ColumnO", SqlDbType.Int, 4, "ColumnO")
            cmdInsertSQL.Parameters.Add("@ColumnP", SqlDbType.VarChar, 50, "ColumnP")
            cmdInsertSQL.Parameters.Add("@ColumnQ", SqlDbType.Int, 4, "ColumnQ")
            cmdInsertSQL.Parameters.Add("@ColumnR", SqlDbType.VarChar, 50, "ColumnR")
            cmdInsertSQL.Parameters.Add("@ColumnS", SqlDbType.Int, 4, "ColumnS")
            cmdInsertSQL.Parameters.Add("@ColumnT", SqlDbType.VarChar, 50, "ColumnT")
            cmdInsertSQL.Parameters.Add("@ColumnU", SqlDbType.Int, 4, "ColumnU")
            cmdInsertSQL.Parameters.Add("@ColumnV", SqlDbType.VarChar, 50, "ColumnV")
            cmdInsertSQL.Parameters.Add("@ColumnW", SqlDbType.Int, 4, "ColumnW")
            cmdInsertSQL.Parameters.Add("@MetNo", SqlDbType.VarChar, 6, "MetNo")
        End With
        cmdInsertSQL.ExecuteNonQuery()
    Else
        MsgBox("Record Exists", MessageBoxIcon.Error, "Error")
    End If
Next

End Sub

I am using the same code as obtained from stackoverflow.com/questions/31497825/… which on its own was working ok. Included the error line. — chlorinelemon
In the question you refer to, you are using a SqlDataAdapter and calling adapter.Update(table) with a DataTable, so the 4-parameter Parameters.Add call makes sense in that case, but you are not doing that here - you are calling cmdInsertSQL.ExecuteNonQuery(), which is totally different. — Mark
Note that you can use multiple fields to create a unique key (aka Primary Key) in the table which may make things more simple. Unless the server is outside your local network I wouldn't both with parameterized commands, just use dynamic SQL to build INSERT statements. SQL injection fear is much overblown IMO. — rheitzman

Mark Mark · Accepted Answer · 2015-07-24T17:00:21

You don't say where the code throws an error, but how you are adding the parameters for cmdInsertSQL doesn't seem right to me. You are using the 4 parameter overload of Parameters.Add, where the 4th parameter is sourceColumn, which I think (never used it myself!) is used for updating from a DataTable.

So, I believe your current code is adding the parameters, but not specifying a value, hence the error. Instead, you should set the value from the matching column in row:

cmdInsertSQL.Parameters.Add("@ColumnA", SqlDbType.Int, 4).Value = row("ColumnA")

VB.Net - Insert .CSV file records after checking for duplicates

1 Answers