Question TableAdapter, Add works, Update works, Delete has no effect

iQBus

New member
Joined
Oct 26, 2011
Messages
2
Programming Experience
3-5
Don't know what's happened to my post?...

Hi All,

I've been banging my head against this for days now if anyone can help it would be greatly appreciated

I've got a couple of forms that share a datatable (declared in a module as public), it's basically a list of documents, one form has a datagridview that lists all of the docs, the other allows the users to add, rename or delete them.

Access DB btw

When I add a new row, that works fine, when I run tableadapter.fill(datatable) the new row is there
When I rename a record, that also works fine, when I run tableadapter.fill(datatable) the changes are there
But when I try to delete a row, the dataset is updated because the rows are removed from the datagridview but when I reload the form the documents keep re-appearring. They're clearly not getting removed from the database

The SQL commands on the table adapter are

SELECT Policies.ID, Policies.Policy, Policies.Responsibility, Policies.Status, Policies.Comments, Policies.Implemented, Policies.DateFirstImplemented, Policies.ReviewDate, Policies.iQDefaultDoc, Policies.DocInUse, Policies.DocType, Policies.DocRead, Contacts.Name AS ResponsibilitySort, SortOrderDocStatus.SortOrder FROM ((Policies LEFT OUTER JOIN SortOrderDocStatus ON Policies.Status = SortOrderDocStatus.StatusText) LEFT OUTER JOIN (SELECT ID, IIF(ISNULL(Name), 'xxxxxx', Name) AS Name FROM Contacts) Contacts ON Policies.Responsibility = Contacts.ID

INSERT INTO `Policies` (`Policy`, `Responsibility`, `Status`, `Comments`, `Implemented`, `DateFirstImplemented`, `ReviewDate`, `iQDefaultDoc`, `DocInUse`, `DocType`, `DocRead`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

UPDATE `Policies` SET `Policy` = ?, `Responsibility` = ?, `Status` = ?, `Comments` = ?, `Implemented` = ?, `DateFirstImplemented` = ?, `ReviewDate` = ?, `iQDefaultDoc` = ?, `DocInUse` = ?, `DocType` = ?, `DocRead` = ? WHERE ((`ID` = ?))

DELETE FROM `Policies` WHERE ((`ID` = ?))

I had to create the TableAdapter with a more straight forward select query so it would auto-create the delete and update queries then edit it manually afterwards and choose not to update the others. The select query needs the extra columns to help with sorting some columns by a different criteria.

The code for updating the DB is:

Console.WriteLine("HasChanges? - " & PoliciesDataSet.HasChanges)
PoliciesDataTable.AcceptChanges()
Me.PoliciesTableAdapter1.Update(PoliciesDataTable)
Me.dgvLibrary3.Refresh()
Me.dgvLibrary3.Parent.Refresh()
<P>Console.WriteLine("HasChanges? - " & PoliciesDataSet.HasChanges)

The haschanges lines return "True" before and "False" afterwards...

The code for deleting the row is:
PoliciesDataTable.Rows(i).Delete()

anymore information needed? HEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEELP

cheers,
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,496
Location
Sydney, Australia
Programming Experience
10+
Do you know what the AcceptChanges method actually does? I suggest that you read up on the purpose of that method.

Also, you can just create whatever SELECT statement you wanted and then added the other commands manually.
 

iQBus

New member
Joined
Oct 26, 2011
Messages
2
Programming Experience
3-5
Cheers jmcilhinney,

I read what Microsoft's MSDN had to say about AcceptChanges and it made no sense at all, or certainly didn't mention anything that would enlighten me as to why this wasn't working. But after reading some other articles this sentance summed it up nicely

"The point here is, you should only call AcceptChanges on your rows, if you don't want them to be sent to your database to update records."

Thanks for the pointer, working now.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,496
Location
Sydney, Australia
Programming Experience
10+
AcceptChanges means that you have accepted your changes. If you want to save them then you obviously haven't accepted them yet. AcceptChanges gets called AFTER the changes have been saved to the database. There's generally no need to call it yourself because it is done implicitly by the call to Update.

To be specific, a DataRow has two versions of its data (original and current) and has a RowState value that describes their relationship:

Unchanged: original and current are the same
Added: original is empty and current contains data to be inserted
Modified: original contains data from database and current contains data to be updated
Deleted: original contains data from database and current is empty

The RowState of a DataRow indicates what should be done with it when you call Update. When you call AcceptChanges, Deleted rows are removed, Modified and Added rows have their original data overwritten with their current data and all RowStates are reset to Unchanged, indicating that their are no changes to save, because they have all been accepted.
 
Top Bottom