title: Why won't this DataAdapter insert rows into the database?
body: <p>So I have a situation where I am using a SqlDataAdapter to insert rows into a table in a SQL Server 2014 database.</p>
  
  <p>The source of the data is an Excel spreadsheet.</p>
  
  <p>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.</p>
  
  <p>However, I am refactoring the code to use an OleDbDataReader. Here is my function:</p>
  
  
  
  <pre class="lang-vb prettyprint-override"><code>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='" &amp; fullpath &amp; "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"
              Dim commandstring As String = "Select * From " &amp; 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
  </code></pre>
  
  <p>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 <em>unchanged</em> for both cases.</p>
  
  <pre class="lang-vb prettyprint-override"><code>    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
  </code></pre>
  
  <p>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.</p>
  
  <p>Am I missing some sort of basic functionality or property of the <code>DataTable</code> or maybe a property inherited or created during the Load?  Is it something else I haven't thought of? Why does my <code>SqlDataAdapter</code> insert data into the database when the source is a <code>DataTable</code> created manually versus a <code>DataTable</code> filled by the <code>OleDbReader</code>? </p>