[RESOLVED] Updating multiple table

[wes4dbt]

I'm just learning .Net, I've been programming in VB6 for many years. I've found alot of examples for add/edit/delete a single table. In VB6 ADO you could load multiple tables into a recordset using the join command. Then manipulate or modify the data.

I've found how to retreive multiple tables like,

Da = New OleDb.OleDbDataAdapter("Select groups.groupid,groups.userid,lots.groupid,lots.lotid from groups inner join lots on groups.groupid=lots.groupid order by groups.groupid asc", DB)

But the only way I have found to update is to use seperate Update commands for each table.

Is this the only way to update? If not could someone point me in the right direction.

Thanks

[jmcilhinney]

Absolutely it's the only way. A SQL UPDATE statement cannot update more than one table. Depending on your database and ADO.NET provider, you may be able to include multiple UPDATE statements in the one command and update multiple tables that way. Given that you're using OleDb though, there's every chance that you will only be able to include one SQL statement in the CommandText of your OleDbCommand. In that case you'll need two DataAdapters to update two tables from changes in the one DataTable.

In that case you'll need the first DataAdapter at least to have its AcceptChangesOnUpdate property set to False. If you don't then changes will be saved to the first table and then all the Modified flags cleared from the Datatable, leaving no changes to be saved to the second table.

[wes4dbt]

jmcilhinney,

thanks for the reply, that's what I needed to know. Now I can stop wasting my time looking through samples. My book isn't much help either, they want you to use bound control or data creation wizards. I'm trying to get away from those things and work only with code.

thanks again

[jmcilhinney]

I strongly suggest that you do not avoid using data-binding. Data-binding has nothing specific to do with wizards. Yes, if you use the Data Source wizard to generate a typed DataSet then you can drag tables and columns from the Data Sources window onto a form to create various objects, set up various bindings and even generate some user code. That's not necessarily a bad thing as it saves a lot of time but, if you want to avoid that, fair enough.

Data-binding is not limited to that scenario though. You can set up data-binding yourself in code, whether you're using a typed DataSet, an untyped DataSet or your own custom classes. Data-binding is a proven technology and trying to write all the code to shuttle data between data sources and the UI is really a big waste of time. I use data-binding wherever possible. There are some scenarios where you need to write your own code to move data around but they are the minority. In the majority of cases you will gain nothing by writing your own code to do what data-binding already does.

[wes4dbt]

jmcilhinney,

In the VB6 forum I constantly read how "bound controls are evil".
Not so in .Net? Sure would be alot easier.

Thanks for the reply

[jmcilhinney]

Data-binding in VB6 and VB.NET is different. In VB6, ADO maintains an open connection to the database so your controls are, in effect, bound directly to the database. In VB.NET, ADO.NET opens a connection, gets a copy of the data, then closes the connection. You then bind your controls to that copy and remain disconnected from the database until you need to save your changes. .NET data-binding works with any list of data too. It doesn't have to originate in a database. You might bind an array of strings to a ComboBox, for instance.

[wes4dbt]

jmcilhinney,

Thats great information. I just didn't want to start off using bad programming method that I would regret later. Well, my brain is fried, I've been in front of this screen for 13 hours. Good night

Thanks for the reply