asked on

How do you update multiple tables (Parent / Child) in a Data Grid?

I am building a grid completely on the fly, all SQL statements are pulled from a table
In a loop, adding each statement to a DataTable, then adding the Datatable to a Dataset
There could be up to 6 tables, they all drill down one from the other.

Example:
SQL.ID --Table 1
SQL.ID -- Table 2
SQL.ID2
SQLID2 -- Table 3
SQLID3
SQLID3 -- Table 4

'--Here is the code I am using to show the data

Public TB(20) As DataTable
Public dsetMain As DataSet

dsetMain = New DataSet
For iCollCount = 0 To clsInt.Coll_Count - 1 '---Count of Rows in Table

Dim sqcParent As New SqlCommand

stSQL = 'SQL from table'
TB(iCollCount) = New DataTable
With sqcParent
.CommandText = stSQL
.CommandType = CommandType.Text
.Connection = clsData.Data_Conn
End With
adp = New SqlDataAdapter(sqcParent)
adp.Fill(TB(iCollCount))

dsetMain.Tables.Add(TB(iCollCount))

Next

'-- From this point I set the Data relations .

DataGrid.DataSource = dsetMain

'-- So my question is how to save a Update to any or all tables in the grid?
'-- Any help is greatly appreciated

Brian Crowe

You would have to update from the parent down to avoid referential integrity issues.

JSkwarek

ASKER

When I try to loop thru from parent down I get this error:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Missing the DataColumn 'PK_Col_ID' in the DataTable 'Table1'
for the SourceColumn 'PK_Col_ID'.

JSkwarek

ASKER

'-- Here is the Code that is blowing up
Dim x As Int16
If dsetMain.HasChanges Then
Dim command_builder As New SqlCommandBuilder(adp)
For x = 0 To 2
adp.Update(TB(x))
Next

JSkwarek

ASKER

The For x = 0 to 2 was just for testing

Bob Learned

What does the SQL statement for the SelectCommand look like? I don't believe that it is really 'SQL from table'.

Bob

JSkwarek

ASKER

The SQL Statemment is simple

select * from patients :

it will then run thru each row getting another table to select form, they are all related,

JSkwarek

ASKER

Ok, I figured this one out myself. So no points are given.
What the problem was is this.

The DataTable and the DataAdapters data schema must match.
If not you get the error

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Missing the DataColumn 'PK_Col_ID' in the DataTable 'Table1'
for the SourceColumn 'PK_Col_ID'.
the PK_Col_Id is jusy my col, the error would show whatever col was used

So what I did was just create an Array of Adapters, so when I run thru the Updates the tables and the Adapters match.
Like this.

CREATE THE DATATABLES:
Public TB(20) As DataTable
Public adp(20) as SqlDataAdapter

Public dsetMain As DataSet

dsetMain = New DataSet
For iCollCount = 0 To clsInt.Coll_Count - 1 '---Count of Rows in Table

Dim sqcParent As New SqlCommand

stSQL = 'SQL from table'
TB(iCollCount) = New DataTable
With sqcParent
.CommandText = stSQL
.CommandType = CommandType.Text
.Connection = clsData.Data_Conn
End With
adp(iCollCount) = New SqlDataAdapter(sqcParent)
adp(iCollCount).Fill(TB(iCollCount))

dsetMain.Tables.Add(TB(iCollCount))

Next

THEN TO UPDATE:
Dim x as int16

If dsetMain(0).HasChanges Then
For x = 0 To iDatatabelnum
Dim command_builder As New SqlCommandBuilder(adp(x))
adp(x).Update(TB(x))
Next

dsetMain(0).AcceptChanges()

End If

This worked,
Thanks For trying.

ASKER CERTIFIED SOLUTION

CetusMOD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial