CommandBuilder with JOIN sql

[wrzek]

Hello,

I have a dataadapter that joins two tables with and inner join. I'm trying to use CommandBuilder to create SQL commands. I know it's impossible to do it directly, but I found two suggestions:

1.
"You can only update 1 of the tables in the join. So pick one, change the SQL
query for the adapter to be a SELECT just from that table of the appropriate
columns, and then create the command builder. That way the command builder
will ignore any other columns, and the update will be able to update that
one table."

2.
"I had a similar problem, which I solved by using 2 dataadapters: one
which SELECTs without a JOIN and is used for UPDATEing, and another with
the JOIN. They both work with the same dataset - one reads, the other writes"

I was trying to use this solutions, but I'm unable to write a correct vb.net code. Does anybody used one of this solution and can paste some code here?

Thank you in advance!

 

[wrzek]

The answer (probably not perfect, but it works) is:

'Read:

Dim DaTbSpec As New OleDb.OleDbDataAdapter("SELECT tbAttributes.Attribute_Name, tbAttributes.Attribute_position,  tbSpec_data.* FROM tbSpec_data INNER JOIN tbAttributes ON tbSpec_data.Attribute_ColName = tbAttributes.Attribute_ColName WHERE  Spec_ID = " & SpecID, ConnQual)
'Read from two tables
DsTbSpec.Clear()
DaTbSpec.Fill(DsTbSpec, "TbSpec")
BsTbSpec.DataSource = DsTbSpec.Tables(0)
GrdSpecEdit.DataSource = BsTbSpec

'Write:

Dim DaSave As New OleDb.OleDbDataAdapter("SELECT * FROM tbSpec_data WHERE Spec_ID = " & SpecID, ConnQual)
'After changes save only one table (second table is not for change)
Dim CbSave As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(DaSave)
Dim ChangedTable As New DataTable()
ChangedTable = DsTbSpec.Tables(0).GetChanges()
If Not ChangedTable Is Nothing Then
    DaSave.Update(ChangedTable)
End If

 

[bboffin]

As an alternative if the Database Server supports Stored Procedures you can use stored procedures that you have written yourself for the Delete, Insert and Update commands. These will get called as required when you call the Update method of the DataAdapter.

I use this technique to update multiple tables when editing information relating to a client. I believe that I update at least 4 different tables (Client details, Client Addresses, Addresses, Client Users, Users, Credentials etc.)

Remember to do it all inside a transaction though!

Bob Boffin