CommandBuilder and Joins....

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

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 said:
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:
Click to expand...

Most people either handle the updates completely manually, or most create a stored procedure to
handle the update and point the command at that.
 
Cant do the stored procedure route as using ms-access on the backend.
How would i go about handling it manually?

ta
 
Marc said:
Cant do the stored procedure route as using ms-access on the backend.
How would i go about handling it manually?
Click to expand...

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.
 
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.
__________________________________
 
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.
__________________________________
 
Hello.

That whitepaper was very useful and i now have the database being
updated with the appropriate values. Now i need to return the
@@identity value - the article states - "They should also return
(through a separate SELECT or OUTPUT parameter) the server-generated
identity value for the new row". How do i do this?

Or can i use the fact that a datatable is being autoincremented and
drop the value into more than 1 table when im updating the table via
the dataadapter?

Bear in mind im using ms-access and therefore cannot create a stored
procedure with a output value.....

Cheers,

marc
 
Back
Top