DataGrid - Update Tow Tables 1

[Skittle]

I have two tables that have a one to one relationship.
I want to quickly set up a datagrid that allows users to edit values in both tables where one row is displayed
in one grid to represent the two tables.

I have dragged and dropped one data source table1 on to a windows form as a datagrid.

I have then edited the auto-generated SelectCommand to join table1 to table2 and include the relevant fields
After changing the select command I was careful not to auto-generate the Update and delete commands as they would go mad with
two tables present in the Select statement.

I then manually changed the Update Statement for the table adapter so it has two UPDATE commands in it seperated by a ';'.
The first UPDATE command still updates the first table just as it was auto-generated when I dragged the table on to the form.
The second UPDATE command I coded myself to update the second table fields I brought in by changing the SELECT statement.

At run time the datagrid shows my extra fields from the select join.
The data grid also tries to execute both UPDATE statements but does not like the ';' that indicates the end of the first statement.
The error message is 'Token ; was no valid. Valid toekn :<END-OF-STATEMENT>'.
This may be a unique issue to the Iseries/AS400 database that I am trying to update.

So I have a couple of questions:-
1) Can you have more than one UPDATE statement in the Update Command of a datagrid?
2) Should I use something else instead of ';' to seperate the commands?
3) Is a better approach to execute the update via one statement that calls a stored procedure that
performs both update commands?.



Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[MajP]

I read David Sceppa's ADO.Net core reference, and he always recommends to use store procedures as your first means of updating data. He provides several good points on why he recommends this.

A when you change the records in a dattable, it records which records have been modified, added, or deleted. Also every changed field stores the original value and the new value.
There are several problems with your approach. First you have to do things in a specific order
1. submit new parent records
2. submit new child
3. submit modified parent
4. submit modified child
5 submit deleted child
6. submit deleted parent

Second once you submit a change that row is no longer marked as being modified. (Although you can specify to submit the change and leave it tagged as still modified). If you try to update another table, it looks for the modified rows, but now the row is no longer tagged as modified.

If you do not want to write update,delete, insert commands and you are using a strong typed dataset then just bring in the individual tables (parent, child) without the join into your dataset. Then you will have tableadapters for the individual tables. You can still have the joined datatable as well. Although you will use the joined datatable to display, you will use the individual tableadapters to submit. See the link

http://msdn.microsoft.com/en-us/library/xzb1zw3x.aspx

Here is the basics.

Private Sub UpdateDB()
    Dim deletedChildRecords As NorthwindDataSet.OrdersDataTable =
        CType(NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Deleted), NorthwindDataSet.OrdersDataTable)

    Dim newChildRecords As NorthwindDataSet.OrdersDataTable =
        CType(NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Added), NorthwindDataSet.OrdersDataTable)

    Dim modifiedChildRecords As NorthwindDataSet.OrdersDataTable =
        CType(NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Modified), NorthwindDataSet.OrdersDataTable)

    Try 
        If deletedChildRecords IsNot Nothing Then
            OrdersTableAdapter.Update(deletedChildRecords)
        End If

        CustomersTableAdapter.Update(NorthwindDataSet.Customers)

        If newChildRecords IsNot Nothing Then
            OrdersTableAdapter.Update(newChildRecords)
        End If 

        If modifiedChildRecords IsNot Nothing Then
            OrdersTableAdapter.Update(modifiedChildRecords)
        End If

        NorthwindDataSet.AcceptChanges()

    Catch ex As Exception
        MessageBox.Show("An error occurred during the update process")
        ' Add code to handle error here. 

    Finally 
        If deletedChildRecords IsNot Nothing Then
            deletedChildRecords.Dispose()
        End If 

        If newChildRecords IsNot Nothing Then
            newChildRecords.Dispose()
        End If 

        If modifiedChildRecords IsNot Nothing Then
            modifiedChildRecords.Dispose()
        End If 

    End Try 
End Sub

In the above code

 CType(NorthwindDataSet.Orders.GetChanges(Data.DataRowState.Deleted), NorthwindDataSet.OrdersDataTable)

The getchanges function returns an Array of datarows, not a table. So you can cast the arrray into the strong type datatable as shown. However, not sure if you even have to do that because i thought the update function can take as an argument an array of datarows as well. But I always do it as above.

 

[MajP]

Sorry that example would be a form set up with a separate Parent view and child view. Such as parent records in the main form and child records in the sub form (such as a datagrid)

On the same link this is the situation you have. Just as slight modification since you are getting your temporary tables from a single datatable, not two seperate datatables.

The following example shows how to update a data source with a dataset that contains related tables. In order to follow the above sequence, three temporary DataTables will be created to hold the differing records. Then the Update method will be called for each subset of rows from within a try/catch block. If update errors occur, the suggested course of action is to branch off and resolve them. Then the dataset commits the changes. Finally, dispose of the temporary data tables to release the resources.

Private Sub UpdateDB()
    Dim DeletedChildRecords As DataTable =
        dsNorthwind1.Orders.GetChanges(DataRowState.Deleted)

    Dim NewChildRecords As DataTable =
        dsNorthwind1.Orders.GetChanges(DataRowState.Added)

    Dim ModifiedChildRecords As DataTable =
        dsNorthwind1.Orders.GetChanges(DataRowState.Modified)

    Try 
        If Not DeletedChildRecords Is Nothing Then
            daOrders.Update(DeletedChildRecords)
        End If

        daCustomers.Update(dsNorthwind1, "Customers")

        If Not NewChildRecords Is Nothing Then
            daOrders.Update(NewChildRecords)
        End If 

        If Not ModifiedChildRecords Is Nothing Then
            daOrders.Update(ModifiedChildRecords)
        End If

        dsNorthwind1.AcceptChanges()

    Catch ex As Exception
        ' Update error, resolve and try again 

    Finally 
        If Not DeletedChildRecords Is Nothing Then
            DeletedChildRecords.Dispose()
        End If 

        If Not NewChildRecords Is Nothing Then
            NewChildRecords.Dispose()
        End If 

        If Not ModifiedChildRecords Is Nothing Then
            ModifiedChildRecords.Dispose()
        End If 
    End Try 
End Sub

 

[Skittle]

Thats very interesting thanks.
My case is a bit different in that it is a one to one relationship so technically the second table is not a child but a sister table.
It also highlights to me that there are so many different ways to go about things like this.
I have looked up quite a few variants since looking at this yesterday.

However I understand the point.
I have been mulling it over and think stored procedures is the way to go.

One thing that intrigues me though is whether or not the command issued by the table adapter for a delete or update can consist
of more than one command without using a stored procedure. In other words the command would be a text type with more than one command in it.
I havn't been able to achieve this but I accept it's not necessarily the best way to go anyway.


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[MajP]

My case is a bit different in that it is a one to one relationship so technically the second table is not a child but a sister table.

You may not have constraints set but probably one table has a PK and the other a FK. So it is really still a Parent child. But regardless the technique would be the same. The only difference is if there is no FK constraint then the order in which you add and delete the "child" would not really matter.

I think you may be able to do it your way by telling the command not to update the datarowstate property. So when you use a dataadapter you read each row that has a changed rowstate. Then when you update it changes it back to "not changed". You could tell it not to update the rowstate after the update like
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None

However, you would then have to reload the datatable because everything would still be in a modified state, even though you did the update.

Like I said I think your original approach did not work because since this property was not set it did the updates on the first sql row by row. Now each has been put into an unmodified state. So when the second "command" runs there are no records in a modified state any longer.