vb.net import csv into database with extra column

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

Answers


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
‚Äč

Need Your Help

Avoiding initialization by default constructor in C++

c++ gcc clang

I need to be sure that a constructor does not initialize some member variables:

Laravel 5.1 Update form can't get it to work

forms laravel

I keep getting an : NotFoundHttpException in RouteCollection.php line 161: error whenever i try to update an user. I want it to work that an email gets filled in at zoekEmail input. And it will upd...