Link to home
Start Free TrialLog in
Avatar of NevSoFly
NevSoFly

asked on

Can't add record to Access DB

I'm using the attached code to add a record to an Access DB everything seems to work except the record is never added.  No error is displayed.  The table (table1) in the Access DB has the below fields:
ID   (Key)
Item
PN
Rev

I think that it may have something to do with the Key.  I can see that the data in the datatable has added the record when the dt.Rows.Add(dr) command is executed but after the da.Update(dt) and dt.AcceptChanges() command are executed the Access DB doesn't contain the new record.
strSQL = "SELECT * FROM table1"
        Dim da As New OleDb.OleDbDataAdapter(strSQL, OLEConn)
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dt As New DataTable
        da.Fill(dt)
        Dim dr As DataRow = dt.NewRow
 
        dr.Item("Item") = txtSystem.Text
        dr.Item("PN") = txtLocation.Text
        dr.Item("Rev") = txtCarrier.Text
 
        dt.Rows.Add(dr)
        da.Update(dt)
        dt.AcceptChanges()

Open in new window

Avatar of theplonk
theplonk
Flag of Australia image

You might need to create your updatecommand. You can use OleDbCommandBuilder to create it for you.

da.UpdateCommand = cb.GetUpdateCommand()
Avatar of NevSoFly
NevSoFly

ASKER

I placed the update command after dt.Rows.Add(dr) but it still didn't work.
I tried the following and it worked for me. I had a table with an auto increment id field and a text field called "Test".

        Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Database1.mdb;User Id=admin;Password=;")
        Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM Table1", conn)
        Dim ds As DataSet = New DataSet()
        da.Fill(ds)

        Dim dr As DataRow = ds.Tables(0).NewRow()
        dr("Test") = "Test"
        ds.Tables(0).Rows.Add(dr)

        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)
        da.UpdateCommand = cb.GetUpdateCommand()
        da.Update(ds.Tables(0))
Avatar of Wayne Taylor (webtubbs)
Try using the Schema of the Access table in your DataTable, as well adding the Insert command
strSQL = "SELECT * FROM table1"
        Dim da As New OleDb.OleDbDataAdapter(strSQL, OLEConn)
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dt As New DataTable
        da.FillSchema(dt, SchemaType.Source)
        da.Fill(dt)
        Dim dr As DataRow = dt.NewRow
 
        dr.Item("Item") = txtSystem.Text
        dr.Item("PN") = txtLocation.Text
        dr.Item("Rev") = txtCarrier.Text
 
        dt.Rows.Add(dr)
        da.UpdateCommand = cb.GetUpdateCommand()
        da.InsertCommand = cb.GetInsertCommand()
        da.Update(dt)
        dt.AcceptChanges()

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks
So there was nothing wrong with the code at all.  

I have my Access DB set to always copy to the output directory of my solution and the Access DB that is displayed in the Solution Explorer is the source file not the copy of the Access DB that is created everytime I run or test my application.

So the application work that way it was ment to.  Its just that the Access DB that I was checking for the new data was actually the source DB when it should have been the Access DB in the Debug folder.

But everytime I tested my application a clean version of the Access DB was copied the the Debug folder.

Thanks to everyone for your help.