How to Insert a Row into a MS Access DB with OleDB

[Frorider]

Since days, i´m trying to Insert a Row into a MS Access Table with VB .NET.

I already searched the forum but all i´ve found and tried doesn´t work.

 

I´m using the following code:

 

Dim dbTk AsNew DBToolKit() 'Own Class which provides methods for connecting to databases

Dim dap AsNew OleDb.OleDbDataAdapter("Select * from TestTbl", dbTk.openDB())

Dim cmdBld AsNew OleDb.OleDbCommandBuilder(dap)

Dim dsSrc AsNew DataSet("DS")

 

dap.Fill(dsSrc, "TBL")

showDS(dsSrc, "TBL")'function that shows the contents of the dataset in the debug window

Dim nRow As DataRow

 

nRow = dsSrc.Tables("TBL").NewRow()

nRow.Item(0) = "Fld1_3"

nRow.Item(1) = "Fld2_3"

nRow.Item(2) = "Fld3_3"

dsSrc.Tables("TBL").Rows.Add(nRow)

'dsSrc.Tables("TBL").ImportRow(nRow) 'Doesn´t work too

dsSrc.Tables("TBL").AcceptChanges()

MsgBox(dap.Update(dsSrc, "TBL")) 'always return 0 (no records updated)

 

When I show the contents of the Dataset after inserting the row it is correctly inserted in the Dataset, but the Access DB will not get updated.

Where is the problem? Did i forget an important method to call?:confused:

[PlausiblyDamp]

You will also need to provide the DataAdapter with a valid InsertCommand.

[Frorider]

But when I add this lines it doesn´t work as well.

 

dap.InsertCommand = cmdBld.GetInsertCommand()

dap.UpdateCommand = cmdBld.GetUpdateCommand()

dap.DeleteCommand = cmdBld.GetDeleteCommand()

But when I show the insert, update and delete commandtext-strings in a msgbox it seems ok.

For example the insert Statement is:

INSERT INTO TestTbl( Fld1, Fld2, Fld3 ) VALUES ( ? , ? , ? )

[Frorider]

I found the mistake. I must not call:

dsSrc.Tables("TBL").AcceptChanges()

 

When I uncomment it and run it, it works and inserts the row. :D

I don´t need to call the CommandBuilder´s getInsertCommand and getUpdateCommand methods to set the statements in the DataAdapter.