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,
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: