asked on

SQL Tables Relationships

How can I Update two tables in sql that have two primary keys that are connected in the Database Diagram. I have selected the tables in my C# winform application on the DataGridView by using this code:

private void Form4_Load(object sender, EventArgs e)
        {
            connectionString = ConfigurationManager.AppSettings["connectionString"];
            sqlConnection =  new SqlConnection(connectionString);
            selectQueryString = selectQueryString = " select SID, Last_Name, First_Name from Customer, Customer2 where Customer.SID = Customer2.SID2;";

            sqlConnection.Open();

            sqlDataAdapter = new SqlDataAdapter(selectQueryString, sqlConnection);
            sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);

            dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            bindingSource = new BindingSource();
            bindingSource.DataSource = dataTable;
            dataGridViewTrial.DataSource = bindingSource;
        }

Open in new window

And I have a code that would Edit and Update the info on the DataGrid. It works on one table without using inner join but it doesnt update when two tables are connected in SQL.

private void button2_Click(object sender, EventArgs e)
        {

            try
            {
                sqlDataAdapter.Update(dataTable);
            }
            catch (Exception exceptionObj)
            {
                MessageBox.Show(exceptionObj.Message.ToString());
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                dataGridViewTrial.Rows.RemoveAt(dataGridViewTrial.CurrentRow.Index);
                sqlDataAdapter.Update(dataTable);
            }
            catch (Exception exceptionObj)
            {
                MessageBox.Show(exceptionObj.Message.ToString());
            }
        }

Open in new window

Also, this is how I connected the two tables in the Database Diagram in SQL server 2008:

Please be clear in your comments since I am a beginner in C#.net and SQL

Thank you all,
PE

Lowfatspread

basically you can only update 1 table at a time with an update sql statement..

so you need two update statements to update both tables...

ideally you should create a stored procedure to do the updates for you , passing the necessary details to the procedure...

....

technically you could define the relationship as a view
and use an instead of trigger to handle the update to the actual base tables...

you writing a single source update statement against the view... but that is "advanced" sql , and probably not required, or advisable for most scenario's (you still essentially need the "stored procedure"/2 base update statements with the trigger anyway)

pauledwardian

ASKER

Can you give me an example of how to reate a stored procedure to do the updates please?

ASKER CERTIFIED SOLUTION