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:

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

Thank you all,
PE
pauledwardianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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)

0
pauledwardianAuthor Commented:
Can you give me an example of how to reate a stored procedure to do the updates please?
0
David ToddSenior DBACommented:
Hi PE

>> select SID, Last_Name, First_Name from Customer, Customer2 where Customer.SID = Customer2.SID2

This should be more properly written as
select SID, Last_Name, First_Name from Customer inner join Customer2 on Customer.SID = Customer2.SID2

Of course you should probaby wrap both updates in a transaction, and depending on which version of SQL use the Exception handling ...

HTH
  David

PS Your diagram is almost unreadable - I had to guess at the type for the Last_Name and First_Name columns. Suggest using nvarchar instead of nchar, and does the field _need_ to be nvarchar instead of just varchar?
-- =============================================
-- Create proc_UpdateCustomer
-- =============================================
-- Creating the store procedure
if exists (select name 
	   from   sysobjects 
	   where  name = N'proc_UpdateCustomer'
	   and 	  type = 'p')
    drop procedure proc_UpdateCustomer 
go

create procedure proc_UpdateCustomer
	@SID int
	, @Last_Name nvarchar( 20 )
	, @First_Name nvarchar( 10 )
as
	set nocount on
	
	update dbo.Customer
		set Last_Name = @Last_Name
		, First_Name = @First_Name
	where
		SID = @SID
	;
	
	update dbo.Customer2
		set Last_Name2 = @Last_Name
		, First_Name2 = @First_Name
	where
		SID = @SID
	;
	
go

-- =============================================
-- Grant execute rights to Public for the store procedure
-- =============================================
grant execute on proc_UpdateCustomer to Public 
go

-- =============================================
-- example to execute the store procedure
-- =============================================
execute proc_UpdateCustomer 
	@SID = 123
	, @Last_Name = 'Smith'
	, @First_Name = 'John' 
go

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

pauledwardianAuthor Commented:
Thank you very much. I will use this code that you provided and post the feedback. Thanks for your time.

PE
0
pauledwardianAuthor Commented:
dtodd,

Can you also PLEASE help me with this request as well. Im totally lost :(

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27518715.html

0
pauledwardianAuthor Commented:
thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.