Tips on file import

Question

Below code is supposed to update a table in oracle with rows imported. But it seems to update all columns in the table with only the first column which is imported from the CSV file. I figure its the "ReadFields" property which needs to manipulated/changed.. PLEASE offer me any suggestions, its urgent.

    MsgBox("Saving...")

    Dim parser As New FileIO.TextFieldParser("C:\Documents and Settings\test\Desktop\test.csv")

    parser.Delimiters = New String() {","} ' fields are separated by comma
    parser.HasFieldsEnclosedInQuotes = True
    parser.TrimWhiteSpace = True

    Dim i As Integer

    For i = 0 To DataGridView1.ColumnCount - 1


        **Dim CurrentField = parser.ReadFields()**



        Dim sConnectionString As String = "Data Source=TEST;User ID=TEST;Password=TEST;"

        Dim strSql As String = "INSERT INTO SHOP_MATERIAL_ALLOCT(ORDER_NO, LINE_ITEM_NO, CONTRACT, PART_NO, QTY_REQUIRED, QTY_PER_ASSEMBLY) VALUES (:ORDER_NO,:LINE_ITEM_NO,:CONTRACT,:PART_NO,:QTY_REQUIRED,:QTY_PER_ASSEMBLY)"




        Using conn As New OracleClient.OracleConnection(sConnectionString)
            Using cmd As New OracleClient.OracleCommand()


                Dim adapter As New OracleDataAdapter
                conn.Open()
                cmd.Connection = conn
                cmd.CommandText = strSql
                cmd.Parameters.AddWithValue("ORDER_NO", CurrentField(i))
                cmd.Parameters.AddWithValue("LINE_ITEM_NO", CurrentField(i))
                cmd.Parameters.AddWithValue("CONTRACT", CurrentField(i))
                cmd.Parameters.AddWithValue("PART_NO", CurrentField(i))
                cmd.Parameters.AddWithValue("QTY_REQUIRED", CurrentField(i))
                cmd.Parameters.AddWithValue("QTY_PER_ASSEMBLY", CurrentField(i))



                cmd.CommandText = strSql
                adapter.InsertCommand = New OracleCommand(strSql, conn)
                adapter.UpdateCommand = cmd
                'adapter.Update(table)
                cmd.ExecuteNonQuery()
                cmd.Connection.Close()

                DataGridView1.DataSource = Nothing
            End Using
        End Using
    Next

End Sub

Answer 1

The FileIO.TextFieldParser.ReadFields method parses all the fields in the input string and stores them in an array. In the case above, you should iterate over the individual values in the returned array, but you're always using CurrentField(i). Try using the following to replace the relevant lines above:

cmd.Parameters.AddWithValue("ORDER_NO", CurrentField(0))
cmd.Parameters.AddWithValue("LINE_ITEM_NO", CurrentField(1))
cmd.Parameters.AddWithValue("CONTRACT", CurrentField(2))
cmd.Parameters.AddWithValue("PART_NO", CurrentField(3))
cmd.Parameters.AddWithValue("QTY_REQUIRED", CurrentField(4))
cmd.Parameters.AddWithValue("QTY_PER_ASSEMBLY", CurrentField(5))

Share and enjoy.