How to insert multiple records at once using DataSet and DataAdapter in vb.net & ms access?

Question

i was trying to insert record from one table to another using Data Set, first here's the code:

 Try
            Dim ds1 As New DataSet
            Dim dbada2 As New OleDbDataAdapter
            Dim dbbl2 As New OleDbCommandBuilder
            Dim dbcon As OleDbConnection = New OleDbConnection(dbconstring)

            dbcon.Open()
            Dim cmd1 As OleDbCommand = New OleDbCommand("SELECT InspectionID, ICTDIR, UnitDescription, SizeType, QuantityNo, SerialNo FROM DeliveryInspection WHERE ICTDIR = @ictdir", dbcon)
            cmd1.Parameters.AddWithValue("@ictdir", txtRefNo.Text)
            Dim dbada1 As New OleDbDataAdapter(cmd1)
            ds1.Clear()
            dbada1.Fill(ds1)
            dbcon.Close()

            dbcon.Open()
            dbada2 = New OleDbDataAdapter("SELECT * FROM LogSUF", dbcon)
            dbada2.Update(ds1)
            dbcon.Close()

        Catch ex As Exception
            MessageBox.Show(ex.ToString, "DBADA 2 Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

here i have 2 DataAdapters, The first one is from the table DeliveryInspection and the second is from LogSUF. both tables have the same Column with the only difference is that LogSUF has no records.

I want to make a system that will insert a record from DeliveryInspection (with the corresponding ICTDIR from the Text Box where the user typed it which is txtRefNo) to the LogSUF. My code doesn't have error but it doesn't work either and i tried some ways to fix this problem. Please help and also is there another way to do this? I'm a beginner.

Answer 1

When you call Update, only the rows with a RowState of Added, Modified or Deleted will be saved. As it stands, all your rows have a RowState of Unchanged, so there's nothing to save.

The solution is to set AcceptChangesDuringFill to False on the first data adapter, before you call Fill. That way, the AcceptChanges method is not called implicitly when you call Fill and each RowState will remain Added, ready for the row to be inserted when you call Update.