[2005] TableAdapter NOT Updating Database

[jesus4u]

Hello,

I am using a strong typed dataset with tableadapters. I use the GetBy method to return a datatable from Oracle containing X amount of records.

In my code I get a DataTable and then loop through the TableRows. All is well until I try to call the Update on my TableAdapter. I get a return code of 0 but no changes are made to my database.

Any ideas?

Thanks

[jesus4u]

Try
            '--Turn off timer so we don't step on toes.
            Timer1.Enabled = False
            Dim IIIReleaseAdapter As New III_RELEASETableAdapter
            Dim IIIReleaseDataTable As New IIIRelease.III_RELEASEDataTable
            '--Get release data.
            IIIReleaseDataTable = IIIReleaseAdapter.GetReleaseByImageSourceByStatusCode()
            'IIIReleaseAdapter.FillByImageSourceByStatusCode(IIIReleaseDataTable)
            For Each row As IIIRelease.III_RELEASERow In IIIReleaseDataTable.Rows
                '--Set up the feed class with the correct information.
                '--This also clears out any previous values.
                Dim MyFIS As New FISFeed
                With MyFIS
                    .FIS_OLEDB_Connection = OleDbConnection1
                    .LesseeNumber = row.MFINDEX1.ToString
                    .LesseeName = row.MFINDEX2.ToString
                    .DocumentType = row.BINDER_CATEGORY.ToString.Substring(0, 3)
                    .DocScanDate = CType(row.SCAN_DATE, DateTime).ToString("yyyyMMdd")
                    .DocScanTime = CType(row.SCAN_DATE, DateTime).ToString("hhmmss")
                    .CDNumber = ""
                    .TLSADate = row.BINDEX1.ToString
                    .DocVerDate = row.BINDEX2.ToString
                    .DocSignDate = row.BINDEX3.ToString
                    .ScheduleStatus = row.BINDEX4.ToString
                    .DocOrigFile = row.BINDEX5.ToString
                    .ExpirationDate = row.BINDEX6.ToString
                    .VehicleNumber = row.BINDEX1.ToString
                End With
                row.STATUS_CODE = "X"
                row.AcceptChanges()
                '--Don't want to insert in debug mode.
#If Debug = False Then
                MyFIS.Insert()
#End If
            Next
            IIIReleaseAdapter.Update(IIIReleaseDataTable)
            '--Turn it back on.
            Timer1.Enabled = True
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString)
        End Try

[jmcilhinney]

You must be the millionth person to call AcceptChanges and then wonder why their update doesn't work. When you call AcceptChanges on a DataRow, DataTable or DataSet you clear all the flags that indicate that the data has changed. When you call Update only rows that are flagged as changed are processed. Do NOT call AcceptChanges explicitly in this situation. The default behaviour of the Update method is to implicitly call AcceptChanges after the changes have been saved. There are situations where it is necessary to call AcceptChanges explicitly but this is not one of them.

To be detailed, a DataRow has two sets of data: original and current. When you add a new row the original data is blank and the RowState is Added. When you edit a row the original data is the same as it started and the current data contains the new data, while the RowState is Modified. When you delete a row the current data is blank and the RowState is Deleted. When you call AcceptChanges the values of the original data is overwritten by the values of the current data and the RowState is Unchanged. If the row was deleted it is removed from the table altogether. Now, when you call Update any rows with a RowState of Unchanged are ignored, so if you've called AcceptChanges on all rows then there are no changes in the table so nothing happens.

[jesus4u]

You must be the millionth person to call AcceptChanges and then wonder why their update doesn't work. When you call AcceptChanges on a DataRow, DataTable or DataSet you clear all the flags that indicate that the data has changed. When you call Update only rows that are flagged as changed are processed. Do NOT call AcceptChanges explicitly in this situation. The default behaviour of the Update method is to implicitly call AcceptChanges after the changes have been saved. There are situations where it is necessary to call AcceptChanges explicitly but this is not one of them.

To be detailed, a DataRow has two sets of data: original and current. When you add a new row the original data is blank and the RowState is Added. When you edit a row the original data is the same as it started and the current data contains the new data, while the RowState is Modified. When you delete a row the current data is blank and the RowState is Deleted. When you call AcceptChanges the values of the original data is overwritten by the values of the current data and the RowState is Unchanged. If the row was deleted it is removed from the table altogether. Now, when you call Update any rows with a RowState of Unchanged are ignored, so if you've called AcceptChanges on all rows then there are no changes in the table so nothing happens.

Thanks for the reply:

So what you are saying is that I should simply drop the

row.AcceptChanges()

and all will be well?

Also, after a little more investigation on this Oracle table, the DBA provided no primary key. That poses a problem doesn't it?

[jmcilhinney]

There is no requirement to have a primary key in a table but it does simplify things quite a bit. When operating on a record it is generally important to be able to uniquely identify that record. Using a primary key is the most convenient way to do that.

[jesus4u]

There is no requirement to have a primary key in a table but it does simplify things quite a bit. When operating on a record it is generally important to be able to uniquely identify that record. Using a primary key is the most convenient way to do that.

So then how does the code know how to uniquely identify a row?

[jmcilhinney]

So then how does the code know how to uniquely identify a row?

If there's a way to uniquely identify the row by some column or combination of columns that aren't the primary key then you have to specify that. Most tables require that rows be uniquely identified and the easiest way to do that is with a primary key. If there is no primary key then you have to do more of the work yourself to write the data access code and specify the appropriate columns. If this table has been provided without a primary key without a good reason then that's just slack. If you need to be able to uniquely identify the rows and there is no way to do so then the database is useless and the person responsible needs a kick.

[jesus4u]

If there's a way to uniquely identify the row by some column or combination of columns that aren't the primary key then you have to specify that. Most tables require that rows be uniquely identified and the easiest way to do that is with a primary key. If there is no primary key then you have to do more of the work yourself to write the data access code and specify the appropriate columns. If this table has been provided without a primary key without a good reason then that's just slack. If you need to be able to uniquely identify the rows and there is no way to do so then the database is useless and the person responsible needs a kick.

Point taken ... so back to my code above. If I remove the row.AcceptChanges() call then the code will know what row I am referencing due to the fact of the For Loop?