How to insert, update from a inner join tables when in gridview

Hi I have a gridview showing me a data from two tables using an inner join.

Problem is how do I update and insert into both

There are 3 tables
Products (ProductID, Desc)
ProductImageMap (MapID, ProductID, ImageID, DisplayOrder)
ProductImages (ImageID, Image)

When you open a product it then opens a inner join of the ProductImageMap and ProductImages by the productID

I want to be able to add insert delete and delete from the datatable but cannot work out how to do this using a tableadapter or any other way for that matter.

I know I need to save the ProductImage table first to get the ImageID and then use the ImageID to insert the productimagemap but where do I start and is there an easy way of doing this?
LVL 3
taz8020Asked:
Who is Participating?
 
UnifiedISCommented:
There's not an easy way.
As you stated, you'll need to insert the ProductImage and use SCOPE_IDENTIY() to retrieve the new ProductImageId. Then send additional commands to the database for inserting the other records.
"INSERT INTO Table ()
VALUES ()
;SELECT SCOPE_IDENTITY()"
Use an ExecuteScalare which will return the new product image id from the insert.

With delete, you'll need to send 3 commands to "DELETE FROM Table where ProductImageID = ...
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
It is simple, you can pass all your data mean complete row of interest to some DAL Method, then you may first insert data in master table, then you may perform related operation in child table.

e.g If you have Identity column then you may get new row id with @@Identity. Or you may use any of your logic to get newly inserted row id. But using Identity is recommended.

You can apply this approach to any of custom SP, EF or else.
0
 
taz8020Author Commented:
what is a DAL Method do you have an example
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.