Inserting records into Multiple Tables in Dataset and Database (C#)

[john]

Hi,

I have a question about inserting records into Multiple tables in both a
dataset and database.

Currently I have a dataset created from a database with 3 tables. The
Database (ClientName) generates the ClientID and the other two tables
(Address, Misc) have ClientID as their primary key as well.

This is an MS Access Database.

In my code I have set up a DataRelation Between ClientName and Address Table
with ClientID field set and ClientName and Misc Table with ClientID set as
well.



I've also set up the ClientID field as the primary key in each table in the
dataset.

When I start to insert the records (information is coming from text boxes on
a form), I have an OleDbDataAdapter set up with the insert command for each
table.

When I insert the ClientName information it works fine, but when I go to
insert the Address information I get the following error:

"Foriegn key constraint <constraint name> requires the child key values (65)
to exist in the parent table"


This is the code for the second insert:

dbadapter = Return_Address_OleDbDataAdapter();
myNewRow = AddClientDataSet.Tables["Address"].NewRow();
myNewRow["ClientID"] = ClientID;
myNewRow["Address"] = this.tbStreet.Text;
myNewRow["Town"] = this.tbTown.Text;
myNewRow["PostalCode"] = this.tbPostalCode.Text;
myNewRow["HomeNumber"] = this.tbHomeNumber.Text;
myNewRow["WorkNumber"] = this.tbWorkNumber.Text;
AddClientDataSet.Tables["Address"].Rows.Add(myNewRow);
dbadapter.Update(AddClientDataSet, "Address");




I know it's saying that the ClientID (65) needs to be in the parent table
(ClientName) but it is from my first insert statement and I'm passing the
ClientID generated by the ClientName table to the Address table as well.

This has something to do with DataRelations and I'm not sure what I need to
do to correct this.

Any help would be appreciated.

Thanks.

John

 

[NuTcAsE]

Can you please post the code where your updating the ClientName table.
There might be an error where the auto generated client id from the
database is not being updated into the ClientName table. Check if an
Output parameter is added to the insert command, and the SoureColumn of
that parameter is set to ClientId. This will make sure that the
autogenerated ClientId is reflected back to the ClientId column in your
ClientName table.

 

[john]

You're right. The problem is that the ClientID is not being passed back to
the dataset, however, I'm not sure how to set up the dataAdapter to do this
or if there is another way to do this.

Currently the code looks like this:

The insertCommand for OleDbDataAdapter for ClientName:
I'm not passing back the ClientID but not sure if that's possible in the
same insert command.

// Create the InsertCommand.
cmd = new OleDbCommand("INSERT INTO ClientName (FirstName, LastName,
FullName) " +
"VALUES (@ClientID, @FirstName, @LastName, @FullName)", myConn);

cmd.Parameters.Add("@FirstName", OleDbType.Char, 50, "FirstName");
cmd.Parameters.Add("@LastName", OleDbType.VarChar, 50, "LastName");
cmd.Parameters.Add("@FullName", OleDbType.VarChar, 50, "FullName");

ClientName_OleDbDataAdapter.InsertCommand = cmd;


The code to insert the actual values for ClientName is this:

OleDbDataAdapter dbadapter = new OleDbDataAdapter();
// Update ClientName Table
dbadapter = Return_ClientName_OleDbDataAdapter();
DataRow myNewRow = AddClientDataSet.Tables["ClientName"].NewRow();
myNewRow["FirstName"] = this.tbFirstName.Text;
myNewRow["LastName"] = this.tbLastName.Text;
myNewRow["FullName"] =
this.tbLastName.Text.TrimEnd(null)+","+this.tbFirstName.Text.TrimEnd(null);

AddClientDataSet.Tables["ClientName"].Rows.Add(myNewRow);
dbadapter.Update(AddClientDataSet, "ClientName");

John

 

[NuTcAsE]

See
http://support.microsoft.com/default.aspx?scid=kb;en-us;815629&Product=adonet

You will have to run a select statement to get the newly inserted id
from the database by trapping the RowUpdated event of the data adpater.

 

[john]

Thanks,

I was able to figure this out with this help and some examples from the web.

John