Hello.

I have two tables in a dataset which have both been updated, i am aware
that i cannot use the commandbuilder to update tow tables with a join,
and i obviously get referential integrity errors when i try to update
them one at a time. I am aware i need to roll my own update routine:
has anyone done this before and have any pointers? I can find very
little regarding this on groups and the internet. My first attempt is:

<code>
try
{
string updateCmd;
updateCmd = "UPDATE tblTimes ";
updateCmd += "INNER JOIN tblActivityTimes ";
updateCmd += "ON tblTimes.TimeID = tblActivityTimes.TimeID ";
updateCmd += "SET tblActivityTimes.TimeID = @newTimeID, ";
updateCmd += "tblTimes.TimeID = @timeid, tblTimes.[Day] = @day, ";
updateCmd += "tblTimes.[Date] = @date, tblTimes.Start = @start, ";
updateCmd += "tblTimes.[End] = @end";

//add parameters for each data object
cmdBuild = new OleDbCommandBuilder(oDA);
cmdBuild.QuotePrefix = "[";
cmdBuild.QuoteSuffix = "]";

oDA.SelectCommand = new OleDbCommand(sqlTimes, oConn);
oDA.SelectCommand.Connection = dbConnect(db.connString());
oDA.SelectCommand.CommandText = (string)ViewState["sqlTimes"];

oDA.UpdateCommand = new OleDbCommand(updateCmd, oConn);
oDA.UpdateCommand.Connection = dbConnect(db.connString());
oDA.UpdateCommand.CommandText = updateCmd;

oDA.Update(ds,"tblTimes");
}
catch(Exception exc)
{
Response.Write(exc);
}
</code>

but i still get the OleDbException:

<error>
System.Data.OleDb.OleDbException: The record cannot be deleted or
changed because table 'tblActivityTimes' includes related records. at
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) at
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable) at ASP.proforma_aspx.mergeDtNew() in
C:\Inetpub\wwwroot\physical_activity\admin\proforma.aspx:line 711
</error>

Any help would be appreciated!

cheers,

marc

"Marc" <(E-Mail Removed)> wrote in news:1118915164.355662.109330
@z14g2000cwz.googlegroups.com:
> I have two tables in a dataset which have both been updated, i am aware
> that i cannot use the commandbuilder to update tow tables with a join,
> and i obviously get referential integrity errors when i try to update
> them one at a time. I am aware i need to roll my own update routine:
> has anyone done this before and have any pointers? I can find very
> little regarding this on groups and the internet. My first attempt is:

Most people either handle the updates completely manually, or most create a stored procedure to
handle the update and point the command at that.


--
Chad Z. Hower (a.k.a. Kudzu) - http://www.hower.org/Kudzu/
"Programming is an art form that fights back"

Blog: http://blogs.atozed.com/kudzu

Cant do the stored procedure route as using ms-access on the backend.
How would i go about handling it manually?

ta

"Marc" <(E-Mail Removed)> wrote in news:1118921997.779764.63580
@o13g2000cwo.googlegroups.com:
> Cant do the stored procedure route as using ms-access on the backend.
> How would i go about handling it manually?

Well the manual route is not easy. Bascially you have to iterate the dataset yourself and look at
the modified, inserted and deleted rows and decide how that "applies" to your database and issue the
SQL appropriately.

In simle cases, you might even be able to get an adaptor to do it.


--
Chad Z. Hower (a.k.a. Kudzu) - http://www.hower.org/Kudzu/
"Programming is an art form that fights back"

Blog: http://blogs.atozed.com/kudzu

Marc,

What is the reason you are using a join while you have designed your
database with at least with two tables.

That design should reflect the real situation.

A join is in my opinion to get data for presentation.

Therefore would the approach in this case not be better to use two seperated
tables?

In my idea will you see that in this situation as you want it now you will
get forever concurrency errors if you use a dataadapter. When you have
updated one row, than the seconds, which holds the same data, will probably
direct be rejected.

I won't say it is impossible however probably a lot of work for nothing. And
then have to take a kind of approach as Kudzu suggest.

Just my thought,

Cor

The reason why i need to use a join is that in my dataset i have made
updates to the datatable on the lhs of a relationship (in the database)
and additions to the table on the right hand side. Thereby i cant
update either one at a time due to referential integrity errors.

I would be happy to deal with the datasets manually but how do i issue
an SQL query against the data adapter? Or do i just build the relevant
command objects - with the relevant sql strings - using the same
connection?

So confusing.....

ta

To get around this i have removed the relationship in the underlying
database, and the DataRelation from the dataset. It *seems* to work,
but its a horrible work-around.

ta

marc

In Access/JET you can create what we used to call a QueryDef which is a
database-persisted query.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"Marc" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Cant do the stored procedure route as using ms-access on the backend.
> How would i go about handling it manually?
>
> ta
>

If I understand you correctly, you're having trouble making changes to the tables that are prevented because of RI rules.
Okay, in this case you need to remember that first,
1.. Child rows need to deleted or added (to existing parents), new parents added and existing parents can be changed.
2.. Next, you can add new children to the new parents.
This is a two-step process that takes two separate DataAdapter Update statements.

I describe this in my whitepaper on handling an @@Identity crisis (see www.betav.com\articles.htm)

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"Marc" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> The reason why i need to use a join is that in my dataset i have made
> updates to the datatable on the lhs of a relationship (in the database)
> and additions to the table on the right hand side. Thereby i cant
> update either one at a time due to referential integrity errors.
>
> I would be happy to deal with the datasets manually but how do i issue
> an SQL query against the data adapter? Or do i just build the relevant
> command objects - with the relevant sql strings - using the same
> connection?
>
> So confusing.....
>
> ta
>

Thats what ive ended up doing with two seperate DataAdapter updates.

Cheers,

Marc