vb.net import csv into database with extra column

Question

Below is my code to import a .csv file into sql server database. While the records from the .csv file import without issues, there is a need to add an extra column while importing. Even though I have hard coded the value for the extra column (MetNo), the field is empty after importing. What am I doing wrong here? The MetNo column is not a part of the .csv file and needs to be added as a label value

        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:\S07_180004.CSV")
        parser.Delimiters = New String() {","}
        parser.HasFieldsEnclosedInQuotes = True
        parser.TrimWhiteSpace = True
        Do Until parser.EndOfData = True
            table.Rows.Add(parser.ReadFields())
        Loop
        Dim strSql As String = "INSERT INTO dbo.MetData " _
                & "(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)"
        Dim SqlconnectionString As String = "Data Source=ServerAddress;Initial Catalog=CatalogName;Integrated Security=SSPI;"
        Using connection As New SqlClient.SqlConnection(SqlconnectionString)
            Dim cmd As New SqlClient.SqlCommand(strSql, connection)
With cmd.Parameters
                cmd.Parameters.Add("@ColumnA", SqlDbType.Int, 4, "ColumnA")
                cmd.Parameters.Add("@ColumnB", SqlDbType.DateTime, 10, "ColumnB")
                cmd.Parameters.Add("@ColumnC", SqlDbType.Time, 8, "ColumnC")
                cmd.Parameters.Add("@ColumnD", SqlDbType.VarChar, 50, "ColumnD")
                cmd.Parameters.Add("@ColumnE", SqlDbType.VarChar, 50, "ColumnE")
                cmd.Parameters.Add("@ColumnF", SqlDbType.Decimal, 28, "ColumnF")
                cmd.Parameters("@ColumnF").Precision = 28
                cmd.Parameters("@ColumnF").Scale = 10
                cmd.Parameters.Add("@ColumnG", SqlDbType.VarChar, 50, "ColumnG")
                cmd.Parameters.Add("@ColumnH", SqlDbType.VarChar, 50, "ColumnH")
                cmd.Parameters.Add("@ColumnI", SqlDbType.Decimal, 28, "ColumnI")
                cmd.Parameters("@ColumnI").Precision = 28
                cmd.Parameters("@ColumnI").Scale = 10
                cmd.Parameters.Add("@ColumnJ", SqlDbType.VarChar, 50, "ColumnJ")
                cmd.Parameters.Add("@ColumnK", SqlDbType.Decimal, 28, "ColumnK")
                cmd.Parameters("@ColumnK").Precision = 28
                cmd.Parameters("@ColumnK").Scale = 10
                cmd.Parameters.Add("@ColumnL", SqlDbType.VarChar, 50, "ColumnL")
                cmd.Parameters.Add("@ColumnM", SqlDbType.Int, 10, "ColumnM")
                cmd.Parameters.Add("@ColumnN", SqlDbType.VarChar, 50, "ColumnN")
                cmd.Parameters.Add("@ColumnO", SqlDbType.Int, 4, "ColumnO")
                cmd.Parameters.Add("@ColumnP", SqlDbType.VarChar, 50, "ColumnP")
                cmd.Parameters.Add("@ColumnQ", SqlDbType.Int, 4, "ColumnQ")
                cmd.Parameters.Add("@ColumnR", SqlDbType.VarChar, 50, "ColumnR")
                cmd.Parameters.Add("@ColumnS", SqlDbType.Int, 4, "ColumnS")
                cmd.Parameters.Add("@ColumnT", SqlDbType.VarChar, 50, "ColumnT")
                cmd.Parameters.Add("@ColumnU", SqlDbType.Int, 4, "ColumnU")
                cmd.Parameters.Add("@ColumnV", SqlDbType.VarChar, 50, "ColumnV")
                cmd.Parameters.Add("@ColumnW", SqlDbType.Int, 4, "ColumnW")
                cmd.Parameters.Add("@MetNo", SqlDbType.VarChar, 6, "MetNo")
                cmd.Parameters("@MetNo").Value = "087826"
            End With
            Dim adapter As New SqlClient.SqlDataAdapter()
            adapter.InsertCommand = cmd
            Dim intRowsAdded As Int32 = adapter.Update(table)
        End Using
    End Sub

jdweng jdweng · Accepted Answer · 2015-07-19T11:03:16

After filling the table with parser.ReadFields() method you need to add the data to the new column

        For Each row As DataRow In table.Rows
            row("MetNo") = "new data"
        Next

vb.net import csv into database with extra column

1 Answers