DataSet Update (muIssue - Duplicating Entry in Second Table

[reyes]

Hi all

[Title should read 'Multiple Table Update Issue']

I've been working on this for a few days, but my brain is on fire and I'm probably missing something obvious (or doing something wrong). Here's my situation (all actual test data):

• I have a typed DataSet: dbCustTestDataSet
• Within it, I have one table showing which is formed by a join (within a stored procedure called 'CustomersAddresses_Select).
• Two tables: 'Customers' and 'Addresses' (there is a third 'Status' table but is irrelevant right now).
• Customers table consists of: CustomerId, Title, FirstName, MiddleName, LastName, StatusID.
• Address table consists of: AddressId, HouseNumber, CustomerID.

As I'm working with a typed DataSet, I've refrained from going down creating my own data adapters and written my own stored procedures instead. What I really want is:

• Maintain my current setup of binding my controls against my BindingSource (bsCustomer, in this case).
• Allow the textboxes displayed on a WinForm to be updated and saved using a TableAdapterManager (I have also created stored procedures for 'Update', 'Insert', 'Delete' manually. I think I could have created a basic stored procedure in the DataSet designer, saved, and gone back to amend it to include a join in order to make use of VS creating my commands for me (for update, delete, insert), but I assume I would have had to amend them to suit my own needs (multiple table deletions, insertions etc.)?

My problem is:

• Editing text boxes in the WinForm for 'LastName' etc. works fine. But, if I edit the HouseNumber, it updates all the records (only 3 test records right now) to the same house number.

Code for update SP (I assume, the culprit?):

CREATE PROCEDURE [dbo].[CustomersAddresses_Update]
(    


-- Customer
@CustomerID int,
@Title nvarchar(8),
@FirstName nvarchar(50),
@MiddleName nvarchar(50),
@LastName nvarchar(50),
@StatusID int,


-- Address
@HouseNumber int
)
AS


BEGIN


    UPDATE dbo.Customers SET


    Title = @Title,
    FirstName = @FirstName,
    MiddleName = @MiddleName,
    LastName = @LastName,
    StatusID = @StatusID


    WHERE CustomerId = @CustomerID


    UPDATE dbo.Addresses SET


    HouseNumber = @HouseNumber


END

Code for 'Save' button:

            this.Validate();
            this.bsCustomer.EndEdit();


            if (ds.HasChanges())
            {
                MessageBox.Show("Changes detected");


                dbCustTestDataSetTableAdapters.TableAdapterManager tm = new dbCustTestDataSetTableAdapters.TableAdapterManager();
                try {
                    dbCustTestDataSetTableAdapters.CustomersTableAdapter cta = new dbCustTestDataSetTableAdapters.CustomersTableAdapter();
                    tm.CustomersTableAdapter = cta;
                    tm.UpdateAll(ds);
                    ds.AcceptChanges();
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                }
            else
                MessageBox.Show("No changes.");

One thing I just remembered is when I assigned all of my stored procedures against the relevant commands in the TableAdapter configuration, it gave an error for no value for @customerID (the output within my 'Update' SP) - in case this is relevant.

Thank you.