master/slave

[jagguy]

hi,

i can connect to a database with binding controls using a datagrid and textbox.

What I am a little hazy about is when i want to update a record to 1 table i want to automatically update all the required rows in another table as a master/slave type table setup.
eg 1 to many relationship.

It is a common task and i can put up code but is there a simple link to an example. Previous example i have found only updating 1 table .

[jmcilhinney]

What values would you want to update in the related table? The only common value between the two should be the foreign key and, while it is legal, you should NEVER be changing that. If there are other common values then you have probably designed your database incorrectly.

[jagguy]

Say I had information about a subject on 1 table with sub-id as PK.

Say on another table I had a list of students stud_id and subjects sub_id with combined PK. The sub_id can be repeated of course as it is only part of the key.

I change the sub_id on master table and then i need to change on l save table. Say this is the cse for argument sake.

I have a subject information in textboxes single record and a datagrid for each subject/student entry.

[jmcilhinney]

If you configure the foreign key constraint to cascade updates then it will do so automatically. That said, you shouldn't ever have to do that. If the subject number may have to change then you shouldn't use it as the PK. You should use an auto-generated number as the ID so the subject number can change without the need to change related records.

[jagguy]

OK from a vb.net book you use databinding to move through records but not for update,add,deletes on more complicated data eg data over 2 tables as opposed to a single table.
So i used a ExecuteNonQuery to add 1 record to 2 tables and it works fine so i can modify more than 1 table with this method.

Adding name.AddNew() and running ExecuteNonQuery causes an exception error so i just use the later and 2 ExecuteNonQuery commands (1 for each table).

So if I am reading right from http://www.vbforums.com/showthread.php?t=469872

the ExecuteNonQuery is used for add/delete/update over 1 or more tables. I use the databindings to navigate through records .
I use a commandbuilder to populate the data. I was able to view new added records with ExecuteNonQuery although it said I couldnt on the website.

I have a vb.net 1.1 book on such a topic where it uses this method with a database comprising of 2 tables.

If I am on the wrong theoretical track tell me.

[jagguy]

this does not update a record and gives no error. the fields exists in Access, there is data in the textfield, and the paramaters names seem ok.

????

  Dim command As OleDbCommand = New OleDbCommand
        Dim myone As Integer

        command.Connection = cn
        command.CommandText = "update departments set dname =@two where deptno =@one"
        ' MsgBox(txtdeptno.Text)
        command.Parameters.Add("@one", txtdeptno.Text)
        command.Parameters.Add("@two", txtDname.Text)
       
        cn.Open()
        command.ExecuteNonQuery()

        ' command2.ExecuteNonQuery()

        cn.Close()

[jmcilhinney]

You're adding your parameters in the wrong order. Unlike SQL Server, Access (or rather the Microsoft Jet OLEDB provider used to intercat with Access) ignores the parameter names and cares only about the order they are added.

By the way, why would you choose names like that for parameters anyway, especially if they don't indicate the order? Unless there's a specific reason to do otherwise I suggest that use column names for the parameters. I always add my parameters in the same order as they appear anyway, even for databases where it doesn't actually matter. If you always do things sensibly then there's less chance of error. Why mix up the order of things if you don't have to?

[jagguy]

the parameter names are not very good as i was just testing. it works so thanks for that.
I am using databinding but the sql command for add,update,delete
this is how a book i have teaches it.

[jmcilhinney]

Data-binding and data access have nothing to do with each other. You can retrieve and save data in a database without any data-binding. In fact, data access doesn't require any UI at all. You can also bind data to your UI without it having come from or go to any database. The two are often used in convert because it's useful to get data from a database, display it to the user for editing, then save the changes. That doesn't mean that they are directly related. They are essentially two black boxes that can be used where and as needed, which includes being connected and used together.

If you want to use data-binding then you should get your data into a DataTable, bind that to your controls, then save the DataTable's contents back to the database. See this thread for an example of how to do that.