Why won't this DataAdapter insert rows into the database?

Question

So I have a situation where I am using a SqlDataAdapter to insert rows into a table in a SQL Server 2014 database.

The source of the data is an Excel spreadsheet.

The insert works fine when the DataTable object is populated using a few For loops and .Columns.Add and .Rows.Add to copy the data from the Excel sheet. This working code I have not included here.

However, I am refactoring the code to use an OleDbDataReader. Here is my function:

Private Function FillDataTable(path As String, name As String) As DataTable
        Dim fullpath As String = path
        Dim wsname As String = name
        Dim dt = New DataTable()
        Try
            Dim connectionstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & fullpath & "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"
            Dim commandstring As String = "Select * From " & wsname
            Using con As New OleDbConnection(connectionstring)
                Using cmd As New OleDbCommand(commandstring, con)
                    con.Open()
                    Using dr As OleDbDataReader = cmd.ExecuteReader()
                        With dt
                            For Each c In aryFieldList
                                .Columns.Add(c.FieldName, ConvertType(c.DataType))
                            Next

                            .Columns.Add("SubmID")
                            .Columns("SubmID").DefaultValue = 0

                            .Columns.Add("S_ORDER")
                            .Columns("S_ORDER").DefaultValue = 0

                            .Columns.Add("C_ORDER")
                            .Columns("C_ORDER").DefaultValue = 0
                        End With
                        dt.Load(dr)
                    End Using
                End Using
            End Using

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return dt

    End Function

When I debug, the DataTable that is returned from the function has data in the set, and otherwise appears to be identical to the DataTable from the previous version of code. Here is the code to .Update the database. This code is unchanged for both cases.

    Dim dt = New DataTable()
    dt = FillDataTable(fullpath, wsname)

Using cn = New SqlConnection(ConfigurationManager.ConnectionStrings("Connection").ConnectionString)
    cn.Open()
    Using adp = New SqlDataAdapter()
        Dim sb As New StringBuilder

        [...StringBuilder code to build the Insert command here...]

        Dim cmd As New SqlCommand(sb.ToString, cn)

        With adp
            .InsertCommand = cmd
            .InsertCommand.Parameters.Add("SubmID", SqlDbType.Int, 1, "SubmID")
            .InsertCommand.Parameters.Add("S_ORDER", SqlDbType.Int, 1, "S_ORDER")
            .InsertCommand.Parameters.Add("C_ORDER", SqlDbType.Int, 1, "C_ORDER")

            For Each p In aryFieldList
                If p.Excluded = False Then
                    .InsertCommand.Parameters.Add(p.FieldName, p.DataType, p.Length, p.FieldName)
                End If
            Next
                adp.Update(dt)

        End With 'adp
    End Using 'adp
End Using 'cn

No exceptions are ever thrown. Debugging the adp.Update(dt) line has no latency as if the query is not executed at all. That is the only difference I notice between the Rows/Columns Added DT and the OleDB populated DT--There is a slight latency time as the data is inserted successfully.

Am I missing some sort of basic functionality or property of the DataTable or maybe a property inherited or created during the Load? Is it something else I haven't thought of? Why does my SqlDataAdapter insert data into the database when the source is a DataTable created manually versus a DataTable filled by the OleDbReader?

A `DataTable` tracks the `RowState` of each row, so the manually adding loop works because they are all `Added`. If you load from some other source, they are not added/new. (Still wading thru that code) — Ňɏssa Pøngjǣrdenlarp, Sep 21 '17 at 14:45
Have you tried using cmd.ExecuteNonQuery rather than adp.update? — Benno, Sep 21 '17 at 14:47
If you fill the table using a dataadapter, you can use `myDA.AcceptChangesDuringFill = False` so the row state flag isnt cleared — Ňɏssa Pøngjǣrdenlarp, Sep 21 '17 at 14:49
@Plutonix Is it possible to change the RowState after or during .Load so that they are Added? — Jacob H, Sep 21 '17 at 14:59
Thats what `AcceptChangesDuringFill = False` does! (use it when you load /fill the DT from Excel) — Ňɏssa Pøngjǣrdenlarp, Sep 21 '17 at 15:03
@Plutonix That did it! Changing from OleDbDataReader to OleDbDataAdapter in my FillDataTable function and adding AcceptChangesDuringFill = False before using Fill solved the problem. Do you want to add an answer and I will accept? If not, I will self answer. Thanks for your help. — Jacob H, Sep 21 '17 at 15:50
@Benno FYI we actually ended up using sqlbulkcopy instead of both of those as we are data typing, validating and sanitizing all of the Excel columns before import so we can simply do a ColumnMapping operation and the insert of 100,000 rows took under 5 seconds. The Adapter operations were taking about 30 minutes for the same inserts. We also wanted to avoid creating table types in SQL to do our inserts. Thank you for the suggestion! — Jacob H, Sep 21 '17 at 19:51

score 2 · Accepted Answer · answered Sep 21 '17 at 15:55

Each DataTable tracks the RowState of its rows, so manually adding data in a loop works because they are all Added (it has nothing to do with manually creating the DataTable - its the rows). When you load from some other source like Excel, they are not added/new.

If you use a DataAdapter to fill the table, you can tell it not to set the RowState to Unchanged. This is very useful for migrating data from one data store to another:

myDA.AcceptChangesDuringFill = False
...
rows = myDA.Fill(xlDT)

score 0 · Answer 2 · answered Apr 30 '19 at 12:57

I had the same issue, and the solution was quite simple, once you've seen it... I had my Database in Visual studio, and its property "Copy to Output Directory" was set to "Copy always", instead of "Do not copy". So everytime I was running my code, the database was erased!

Solution : - modify your connection string so it points directly to your database - Change your database property to "Do not copy"

Why won't this DataAdapter insert rows into the database?

2 Answers2

Linked

Related