CODEWITHSUNDEEP
sql-servervb.netcsv
chlorinelemon
chlorinelemon

Reputation: 23

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

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

Upvotes: 0

Views: 772

Answers (1)

Mark
Mark

Reputation: 8160

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")

Upvotes: 1

Related Questions