Insert single Parent and multiple Children

I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table).

My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table.

Thanks for any help

You may use triggers, I mean you can create triggers on the main table, in which you can insert records to other tables. You can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||I don't see how a trigger will help. I need to have a way to add the parent and all its children at once. How will a trigger help?

Insert problem with single quotes

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

blumonde

blumonde:

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

No, it's not a bug with SQL Server 2005. To use that method, you would need to double up any single quotes within the testme string in order to "escape" them so that your INSERT statement works correctly.

However, you should be using parameters to pass UI-supplied values to your SQL statement. Here's the why:

Please, please, please, learn about injection attacks!

And here's the how:

How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

|||

tmorton:

blumonde:

I have a problem with inserting a string with single quotes. For instance,

string testme = "we don't have anything";

insert into tableone (buff) values ("'" + testme + "'");

I get an error with the word "don't" with single quote. But if I delete the single quote "dont" then it inserts okay. Is is a bug in sql 2005? Please help. Thanks.

No, it's not a bug with SQL Server 2005. To use that method, you would need to double up any single quotes within the testme string in order to "escape" them so that your INSERT statement works correctly.

However, you should be using parameters to pass UI-supplied values to your SQL statement. Here's the why:

Please, please, please, learn about injection attacks!

And here's the how:

How To: Protect From SQL Injection in ASP.NET
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net

Thank you Tmorton. I think parameters will have solved the problem.

blumonde

Insert Parent Child records...

Hello,

We have a complex functionality of migrating data from a single record into multiple parent child tables.

To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.

Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.

As of now, we are using the following approach for achieving this functionality.

1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.

2. Use the recordset as the source to a foreachloop , using foreachADO enumerator

3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table

In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)

Thanks,

Satya

If it aint broke, don't fix it. There's no one correct way of acheiving something.

if it were me I would want to process all of the OrderDetails at the same time and it sounds like that's not what you're doing. Like I say though, if it works for you and performs in the required time, stick with it!

-Jamie

|||

As Jamie says, SSIS is a platform tool meaning that the infrastructure typically provides many ways to solve the same problem. However, here are a couple of alternatives that may work for you if you are having issues with the current approach.

1) Wrap this functionality in a stored procedure, and call the SP for every row. The SP would insert the parent item, retrieve the @.@.identity (actually, scope_identity()) and then insert the child. Perf may suck since you're calling the SP for every single row instead of bulk operations.

2) Wrap this functionality in a ScriptTransform that does pretty much the same as the previous bullet, only in VB.Net instead of TSQL. Once again, be careful of perf.

3) Wrap this functionality in a view that utilizes an InsteadOf trigger for the inserts. There are many contraints when going down this route however so your mileage may vary. For instance I cannot recall offhand if table joins are supported in this scenario; BOL has a wealth of info.