Last post Jun 25, 2011 09:30 AM by DotNetTim
Feb 23, 2011 09:39 AM|DotNetTim|LINK
I've got an app that I'm going to possibly need to allow my client to insert info that belongs to more than one table at a time? Is this possible with the data controls and if so, what controls? The web site is about Books and lists books and catagories
such as hard back, first edition, autographed, etc,. The client needs to be able to fill out a text box (while adding the book to a remote hosted web / SQL server database..) and populate the books table and the books catagories table or a many to many table..
I hope that makes sense.. I also asked about the design in the SQL server forum.. Anyway, I'm wondering what's the best approach to a problem such as this? What is the easiest grid or data control to use? I'm using Visual Studio 2010 Pro.. Just got it.. Thanks
so much in advance..
Feb 23, 2011 09:49 AM|ddelella|LINK
If they need to edit the items and add new ones my choice would be a combination of GridView and Form. The grid would simply display the books in the collection with an edit button for each book. When they click the button they are forward to a form view
of the data to edit. As far as the multiple inserts...those each need to be done manually. Since data in cross tables is dependent on the parent the data needs to be inserted in order and not be done concurrently. You can perform all the inserts on the
same transaction using the same open connection to avoid multiple open and closes but that is the best you can do. The cost is usually minimal depending on the amount of data being inserted. If there is a lot of tables involved you may want to check your
database structure to make sure you are properly normalized.
Jun 03, 2011 11:37 PM|DotNetTim|LINK
Thank you so much. I've been really ill, and just saw this post. I forgot I had posted this. God speed, and thanks, even if it is late. I mean that.
Jun 22, 2011 11:24 PM|DotNetTim|LINK
I'm still a little confused. Why can't I have a form for the user that allows them to enter into controls all the information for the Books table, to do an insert, and at the same time on the same form have a control for the Catagory for that book, and a
input text box for uploading a picture for a pictures table, etc, and do the insert in a transaction? Is that possible? I would of course need to get the ID values from the parent table though for each mutliple insert.. I havn't figured this out yet, but I
have a link to a web site on how to use TSQL properly to get the ID.. It's not using the @@ Identity.. Says there's something better.. Anyway, just wanted your opinion if you don't mind? I understand what you said, and I appreciate it..
Jun 22, 2011 11:41 PM|Naom|LINK
You can have any type of controls to insert records into multiple tables. I have this blog post showing how to do so from SQL Server perspective:
How to insert information into multiple related tables and return ID using SQLDataSource
Jun 23, 2011 10:13 PM|DotNetTim|LINK
Thanks so much. I really appreciate your help. I'm going to your blog now.. Thanks again.
Jun 25, 2011 09:30 AM|DotNetTim|LINK
Hi Naom. Hey, I love your SQL code, but it's not what I'm doing. You're doing an insert into a flat file it looks like? Anyway, I'm just wanting to do an insert, then grabbing the ID field from that insert, use it to do another insert into a catagory table,
and of course use the ID that I just grabbed from the first insert to associate the catagory.book_id_FK field with the Book_Id field. Hope that makes sense.. Love your code though! Thanks so much