Question External data obtained via OleDbDataAdapter won't save to the database

[tompkinsjs]

Hi!

I am hoping to get some help with this problem. It seems fairly straight forward, but I just cannot get it to work.

Basically, I am trying to import a text file into a datatable (which can be viewed via a datagridview on a form), then save the imported data to an MS Access database to which the datatable and dataset are bound to. I can get the data from the text file into the datatable (confirmed by viewing it in the datagridview) but I cannot for the life of me get it to save into the MSAccess database. Any help would be appreciated. My code is below.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.OpenFileDialog1.ShowDialog()

Dim strFileName As String = OpenFileDialog1.SafeFileName
Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\files\;Extended Properties=""Text;HDR=Yes;FMT=Delimited""")

Dim da As New OleDbDataAdapter()
Dim ds As DataSet = Me.InvAnalyzeDataSet
Dim cd As New OleDbCommand("SELECT * FROM " & strFileName, cn)
cn.Open()
da.SelectCommand = cd
da.Fill(ds, "LocalInventory")
cn.Close()
da.Dispose()
InvAnalyzeDataSet.LocalInventory.AcceptChanges()


I have also tried to save the data from the datatable using this:
Me.Validate()
Me.LocalInventoryBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.InvAnalyzeDataSet)

but that does not work either.

Thanks for the help!

 

[jmcilhinney]

The problem is that the RowState of your DataRows is wrong. When a DataRow is in a DataTable, it's RowState can be Unchanged, Added, Modified or Deleted. When saving, Unchanged rows are ignored, Added rows are inserted, Modified rows are updated and Deleted rows are deleted.

First, calling AcceptChanges doesn't save data. What it does is set all RowStates to Unchanged. It only gets done AFTER data is saved. You rarely need to do it manually though, because Update and UpdateAll do the saving and then implicitly call AcceptChanges.

What you need to do is to make sure all your DataRows have a RowState of Added. They all do by default, but your call to Fill also contains an implicit call to AcceptChanges. You need to set the AcceptChangesDuringFill property of your DataAdapter to False. That way Fill will not call AcceptChanges and all your rows will be ready to be inserted.

 

[tompkinsjs]

That was the exact problem. I added code to set the AcceptChangesDuringFill = false and the data is now saved in the database. Thank you very much for your help!