TableAdapter, INNER JOINs, stored procs, and problems with Update

[Bogdan]

Hi,

I have a stored procedure that uses JOINs to return columns from multiple
tables. I also have another stored proc that that takes a series of params
and updates multiple tables. I used the framework to auto-generate a table
adapter specifying both stored procs as Get/Fill and Update. The problem is
that columns from the JOINed table seem to marked as 'read-only' so trying
to update a row results in an exception. BTW, by default a FormView
attached (indirectly through ODS and BLL) to the table adapter did not show
Edit/Insert/Delete buttons. I had to switch its default mode to Edit.

I remember reading about JOINs and TableAdapters and that they do not work
well together. I'm not sure though is this was also applicable to stored
procedures. It was suggested that subqueries be used instead. The problem
is that subqueries are good if used with one field per JOINed table. In my
case, I need to join 3 tables and each of them has about 5 columns.

So, my questions is:
Can table adapters be used with stored procedures that take columns from
multiple tables for update purposes? If yes, could someone please let me
know how to do that? If not, what are the alternatives?

I would really, really appreciate _any_ suggestions.

My example of select and update procs:

SELECT a.c1, a.c2, a.c3,
b.c1, b.c2, bc3,
c.c1, c.c2, c.c3,
d.c1, d.c2, d.c3
FROM A a
INNER JOIN B b ON a.c1= b.c1
LEFT OUTER JOIN C c ON a.c1 = c.c1
LEFT OUTER JOIN D d ON a.c1 = d.c1
WHERE a.c1 = @c1;

[...]

-- Update
@ac2 int,
@ac3 int,
@bc2 int,
@bc3 int,
@cc2 int,
[...]

UPDATE A
SET c2 = @ac2 [...]
UPDATE B
SET c2 = @bc2 [...]

etc.

Thanks,
Bogdan

[Holger Kreissl]

I believe its not possible. The way to go is to design a dataset including
the relations you need... So you can make your view over the datatables and
the update will work because the typed dataset knows about the relations
between the tables.

--
Holger Kreissl
..NET Software Developer
http://kreissl.blogspot.com/

I have a stored procedure that uses JOINs to return columns from multiple
tables. I also have another stored proc that that takes a series of
params and updates multiple tables. I used the framework to auto-generate
a table adapter specifying both stored procs as Get/Fill and Update. The
problem is that columns from the JOINed table seem to marked as
'read-only' so trying to update a row results in an exception. BTW, by
default a FormView attached (indirectly through ODS and BLL) to the table
adapter did not show Edit/Insert/Delete buttons. I had to switch its
default mode to Edit.

I remember reading about JOINs and TableAdapters and that they do not work
well together. I'm not sure though is this was also applicable to stored
procedures. It was suggested that subqueries be used instead. The
problem is that subqueries are good if used with one field per JOINed
table. In my case, I need to join 3 tables and each of them has about 5
columns.

So, my questions is:
Can table adapters be used with stored procedures that take columns from
multiple tables for update purposes? If yes, could someone please let me
know how to do that? If not, what are the alternatives?

I would really, really appreciate _any_ suggestions.

My example of select and update procs:

SELECT a.c1, a.c2, a.c3,
b.c1, b.c2, bc3,
c.c1, c.c2, c.c3,
d.c1, d.c2, d.c3
FROM A a
INNER JOIN B b ON a.c1= b.c1
LEFT OUTER JOIN C c ON a.c1 = c.c1
LEFT OUTER JOIN D d ON a.c1 = d.c1
WHERE a.c1 = @c1;

[...]

-- Update
@ac2 int,
@ac3 int,
@bc2 int,
@bc3 int,
@cc2 int,
[...]

UPDATE A
SET c2 = @ac2 [...]
UPDATE B
SET c2 = @bc2 [...]

etc.

Thanks,
Bogdan

[Bogdan]

Holger,

Thanks for your reply.

When you mentioned 'design dataset [...] make your view over the datatables'
did you mean database level or application level (i.e. asp.net datasets,
etc.)?

I'm just curious why updates of JOINed tables are not allowed/recommended
for stored procs. It seems like this is about passing correct params to the
procedure which in turn takes care of updating the relevant tables.

I did experiment with table adapter's tables by setting the Readonly
attribute to false of the 'problematic' columns before updating them. It
seemed to work but I'm not sure if I'm asking for trouble by doing this.

I might post another question specific to the Readonly attribute and see if
anyone can help.

Thanks,
Bogdan
"Holger Kreissl" <di*******@gmail.comwrote in message
news:0D**********************************@microsof t.com...

>I believe its not possible. The way to go is to design a dataset including
the relations you need... So you can make your view over the datatables and
the update will work because the typed dataset knows about the relations
between the tables.

--
Holger Kreissl
.NET Software Developer
http://kreissl.blogspot.com/

>I have a stored procedure that uses JOINs to return columns from multiple
tables. I also have another stored proc that that takes a series of
params and updates multiple tables. I used the framework to
auto-generate a table adapter specifying both stored procs as Get/Fill
and Update. The problem is that columns from the JOINed table seem to
marked as 'read-only' so trying to update a row results in an exception.
BTW, by default a FormView attached (indirectly through ODS and BLL) to
the table adapter did not show Edit/Insert/Delete buttons. I had to
switch its default mode to Edit.

I remember reading about JOINs and TableAdapters and that they do not
work well together. I'm not sure though is this was also applicable to
stored procedures. It was suggested that subqueries be used instead.
The problem is that subqueries are good if used with one field per JOINed
table. In my case, I need to join 3 tables and each of them has about 5
columns.

So, my questions is:
Can table adapters be used with stored procedures that take columns from
multiple tables for update purposes? If yes, could someone please let me
know how to do that? If not, what are the alternatives?

I would really, really appreciate _any_ suggestions.

My example of select and update procs:

SELECT a.c1, a.c2, a.c3,
b.c1, b.c2, bc3,
c.c1, c.c2, c.c3,
d.c1, d.c2, d.c3
FROM A a
INNER JOIN B b ON a.c1= b.c1
LEFT OUTER JOIN C c ON a.c1 = c.c1
LEFT OUTER JOIN D d ON a.c1 = d.c1
WHERE a.c1 = @c1;

[...]

-- Update
@ac2 int,
@ac3 int,
@bc2 int,
@bc3 int,
@cc2 int,
[...]

UPDATE A
SET c2 = @ac2 [...]
UPDATE B
SET c2 = @bc2 [...]

etc.

Thanks,
Bogdan

[Holger Kreissl]

When you mentioned 'design dataset [...] make your view over the

datatables' did you mean database level or application level (i.e. asp.net
datasets, etc.)?

yes i mean the application level. When you create a Dataset with multiple
Tables and their relations you are able to update the whole dataset with all
its relation tables and data...

I will follow this. Maybe there are better ways like you hope too ;)

Greetings

--
Holger Kreissl
..NET Software Developer
http://kreissl.blogspot.com/

[Patrice]

I'm just curious why updates of JOINed tables are not allowed/recommended
for stored procs. It seems like this is about passing correct params to
the procedure which in turn takes care of updating the relevant tables.

This is general for joins. For example what if you update a column in the
outer join part. There is no actual record to update. If you delete a line
which line in which underlying table are you supposed to delete ? etc...
etc...

[Bogdan]

Patrice,

Thanks for the reply. I absolutely agree with your point but _only_ when
dealing with ad-hoc sql queries. The stored proc case is different - at
least from a non-asp.net guy. The stored proc that I'd like to use for
updates takes care of the concerns that you have raised. If framework
auto-generated code (i.e. table adapter, etc.) could simply treat my stored
proc as a 'black box' that can be trusted when it comes to updates and
simply pass the required parameters then I'd be very happy. But, I guess,
there is more to it that I'm aware of at the moment.

Thanks,
Bogdan
"Patrice" <http://www.chez.com/scribe/wrote in message
news:OJ**************@TK2MSFTNGP04.phx.gbl...

>

>I'm just curious why updates of JOINed tables are not allowed/recommended
for stored procs. It seems like this is about passing correct params to
the procedure which in turn takes care of updating the relevant tables.

This is general for joins. For example what if you update a column in the
outer join part. There is no actual record to update. If you delete a line
which line in which underlying table are you supposed to delete ? etc...
etc...