problem in updating multiple tables in dataset...

[mar_zim]

halu. im a newbie
i used datagrid to place multiple tables w/ corresponding data from the database
i could update the first table and it saves into the database what i've change but when i'm going to update the second table it gives me an error: missing the datacolumn "code" in the Datatable 'gallons' for the SourceColumn 'code'
i dont know how to figure it out.

dim cn as new sqlconnection("......")
dim cm as new sqlcommand()
Private ds as new dataset()
dim da as new Sqldataadapter()
dim cmdtext as string

'form load

cn.open
cm.connection=cn
cm.commandtype = commandtype.storedprocedure
cmdtext = "select * from customertable ; select * from gallons"
da.selectcommand = new sqlcommand(cmdtext, cn)
da.tablemappings="Table","Customer"
da.tablemappings="Table1","Gallons"
da.fill(ds)
datagrid1.datasource = ds

'to save the changes

if ds.haschanges then
    dim cb as Sqlcommandbuilder
    da.selectcommand = new sqlcommand(cmdtext,cn)
    cb = new sqlcommandbuilder(da)
    da.Update(ds,"Customer")
    da.Update(ds,"Gallons")
end if

tanx in advance....

[crpietschmann]

I'm having the same problem, except mine says: Missing the DataColumn 'Date' in the DataTable 'FeeChargeAttendance' for the SourceColumn 'Date' I don't even have a column named 'Date' in any of the tables I'm working with.

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Missing the DataColumn 'Date' in the DataTable 'FeeChargeAttendance' for the SourceColumn 'Date'.

I think this must be some kind of bug in the SQLClient namespace.

[mar_zim]

hello..sorry for leaving this thread.

the proper way to update multiple tables is to use different adapters on it.

and if updating a master-detail table is to use relation object.

[crpietschmann]

I figured this one out before you posted a reply. Here is a more detailed description to hopefully better help others out:

One way to fill a DataSet with multiple tables is to send the database multiple requests. Another way to do this is to use multiple SELECT statements in a single request.

There are a couple of problems with doing it this way:
1) The DataTables don't have the same name as the tables in the database, you have to set them yourself
2) You can't update/save the tables to the database; to do that you must use a seperate DataAdapter for each table.

Dim myAdapter as SqlDataAdapter = new SqlDataAdapter(“SELECT * FROM Customers; SELECT * FROM Orders“, connection)
 
myAdapter.Fill(dsTables)
dsTables.Tables(0).TableName = “Customers“)
dsTables.Tables(1).TableName = “Orders“)

It would be so much easier if they made it so you can use the same DataAdapter to update all the tables you load into the DataSet with the DataAdapter.

If you do try to update all the tables with the same DataAdapter, then you will get an error like the one below (this error really frustrated me for a couple hours):

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: Missing the DataColumn 'Date' in the DataTable 'FeeChargeAttendance' for the SourceColumn 'Date'.