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

Visual Basic.NET

Avatar of undefined
Last Comment
NevSoFly

8/22/2022 - Mon
theplonk

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

da.UpdateCommand = cb.GetUpdateCommand()
ASKER
NevSoFly

I placed the update command after dt.Rows.Add(dr) but it still didn't work.
theplonk

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))
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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
Sancler

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
NevSoFly

Thanks
ASKER
NevSoFly

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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.