2

I've been working on a windows forms application, written in vb.net for several months now. I've had a LOT of problems with my binding sources and table adapters not updating the connected access (.accdb) database. Sometimes it works, sometimes it doesn't.

Basically all of my forms in the application have the following VS2022 generated code in the forms OnLoad event:

TblVendorTableAdapter.Fill(Me.RtdbDataSet.tblVendor)
TblAcctTableAdapter.Fill(Me.RtdbDataSet.tblAcct)
TblEmployeeTableAdapter.Fill(Me.RtdbDataSet.tblEmployee)
TblAssetsTableAdapter.Fill(Me.RtdbDataSet.tblAssets)
TblPartsTableAdapter.Fill(Me.RtdbDataSet.tblParts)
TblPurOrdTableAdapter.Fill(Me.RtdbDataSet.tblPurOrd)

These successfully FILL the forms from the dataset. But, when I make changes (say, add some text to a remarks block, etc) and call my sub to save changes, the dang changes don't save. When I close and re-open the form, the changes are gone. Here's my save changes sub (taken from the binding source save button click event):

Try
Me.Validate()
Me.TblPurOrdBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.RtdbDataSet)
Catch ex As Exception
MessageBox.Show("An error occurred: " & ex.Message.ToString())
End Try

I don't understand why this doesn't work consistently. I've checked the database's copy properties, and it is set not to copy when the solution is built. In fact, I specify the database to use at runtime, and it displays the correct records, so I don't believe it is attempting to save back to the wrong database.

I've been reading up on dataadapters and command builders, trying to figure out if theres a better way to drive my forms that only connects to the database to insert, update or delete, but there's a TON of information, mostly pertaining to datagridviews, etc. Where am I going wrong, and is there a code-based way to accomplish this that may be more reliable?

Edit:

I am not making any headway, and I've identified that it's just one of my table adapters not doing it's job. As an example, I've chosen two of my table relationships:

tblPurOrd : tblParts - fldPurcPONum is related to fldPartPONum

tblMaint : tblMaintActs - fldMaintID is related to fldMactMaintID

tblPartsTableAdapter saves changes correctly.

tblMaintTableAdapter saves changes correctly.

tblMaintActTableAdapter saves changes correctly.

The only one I'm having issues with is the tblPurOrdTableAdapter

Masieu Dilesiano
  • 21
  • 3
  • 1
    I think you'll find that `UpdateAll` returns a value. That value should be the number of records affected. If that value is not zero, records were saved. You should incorporate that knowledge into your testing. – jmcilhinney Apr 21 '23 at 00:17
  • 1
    It appears that you have added your database to your project. Please tell me that you're not connecting directly to that source copy when you run the project. What you should be doing is keeping that source copy clean and setting `Copy to Output Directory` to `Copy If Newer` and connecting to the database in the program folder using `|DataDirectory|`. You will then only overwrite your working copy if you make changes to the source file, which you would always want anyway. – jmcilhinney Apr 21 '23 at 00:20
  • Thanks for the input, jmcilhinney. As for incorporating the returned value of UpdateAll, I’ll work that in. As for the database, I’ve designed the project so that the user specifies the database to connect to during the first startup. Once the database is specified, the connection string is updated via code so that all future connections are to the proper database. So, the database produces the correct records. For some reason, the darn Table Adapter(s) have just quit updating the source. Its odd because it just quits working from time time, and I basically have to rebuild the dataset to – Masieu Dilesiano Apr 21 '23 at 01:11
  • 1
    What the end user will do after your app is deployed does not have to affect what you do during the development phase. There's no reason you can't use a database that is copied from a source file whenever you make changes, even if you are specifying the location at run time. – jmcilhinney Apr 21 '23 at 01:15
  • 1
    It seems very unlikely that a typed DataSet would just stop working. If there are changes to save, it will attempt to see them and that will either succeed or fail. – jmcilhinney Apr 21 '23 at 01:24

1 Answers1

0

Oh boy... I feel a bit like an idiot, yet remain slightly confused. So, I believe I solved the problem with the TblPurOrdTableAdapter failing to update the datatable and dataset. I was calling a function immediately following the SaveChanges() function that "formatted" the appearance of the purchase order number (record auto-number ID), using the following code:

Public Sub FormatPO()
    Dim strPOFormatted As String
    If Not IsNothing(GlobalVariables.strPOPX) Then
        strPOFormatted = GlobalVariables.strPOPX & Format(Val(txtPurcPONum.Text), "000000")
    Else
        strPOFormatted = Format(Val(txtPurcPONum.Text), "000000")
    End If
    txtPurcPONum.Text = strPOFormatted
    lblPurOrd.Text = "Purchase Order " & strPOFormatted
End Sub

For some reason, even though this was being called AFTER the SaveChanges() function, it was preventing the table adapter from seeing the changes I just made. When I temporarily removed the function FormatPO(), everything started working. Sigh... Thanks jmcilhinney for your suggestions. Ultimately, what ended up fixing it was eliminating other functions one at a time until it worked. Any idea why FormatPO() would have been causing that AFTER the SaveChanges() function was called? I've already worked out a work-around, pushing the PO number to an unbound text box THEN formatting that text box instead of the bound control.

Masieu Dilesiano
  • 21
  • 3