inserting multiple children - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

inserting multiple children

Author

Topic

jojo2006
Starting Member

5 Posts

Posted - 2006-05-17 : 15:25:14

I'm trying to insert a parent record and multiple child records, but I'm stuck.

Example: On an asp.net form, the user enters the number of textboxes he needs, then he enters a book code in each of those textboxes. When he submits the form, a stored procedure is called to insert a record into the parent table and to insert the x number of child records (book codes) into the child table.

How do I create the parameter(s) for the child values when it's not known how many will be passed?...and how do I write the statement to insert the x number of children? Can someone help me, please? Thank you so much (in advance)... :)

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2006-05-17 : 16:00:21

Insert Parent record first. If this is successful go to Insert Child records in a loop.
Prepare Dynamic SQL in Stored Procedure to Insert the child data.

Ex: Insert into Table (Col1..... Col100) values ('zzz',BoolID123,dt, NULL, NULL ..)

What you need to do is, while passing parameters Identify which data is going to place in which
column. Thats all...

==================================================================

Solution2: Pass columnName|Data as a string. Break the string into Columns and data. Then Insert.

With Regards
BSR

jojo2006
Starting Member

5 Posts

Posted - 2006-05-17 : 16:10:27

Thanks for the help! Although, I'm a bit of a beginner in SQL...could you show me how to write the dynamic SQL to insert the child data? There's actually only 3 columns in the child table:
ID(PrimaryKey), IDNo(ForeignKey), BookCode

Thanks again!

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-17 : 22:47:35

--considering the code below
IF (SELECT OBJECT_ID('dbo.TextBox')) IS NOT NULL DROP TABLE dbo.TextBox
GO

CREATE TABLE dbo.TextBox(
TextBoxID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
OtherData VARCHAR(100)
)
GO

IF (SELECT OBJECT_ID('dbo.BookCode')) IS NOT NULL DROP TABLE dbo.BookCode
GO

CREATE TABLE dbo.BookCode(
BookCodeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TextBoxID INT REFERENCES TextBox(TextBoxID)
)
GO

IF (SELECT OBJECT_ID('dbo.ParentInsert')) IS NOT NULL DROP PROCEDURE dbo.ParentInsert
GO

CREATE PROCEDURE dbo.ParentInsert(@parentValue VARCHAR(100), @parentID INT OUT)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO TextBox VALUES (@parentValue)
SELECT @parentID=SCOPE_IDENTITY()
END
GO
----------------------------------------------------------
In your ASP code,
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
dim sConn as string
dim parentID as integer

sConn = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;"

Try
sqlConn = new SQLConnection(sConn)
sqlConn.Open()
sqlCmd = New SqlCommand(s, sqlConn)
sqlCmd.Parameters.Add("@parentValue",sqldbtype.varchar).value = "REPLACE THIS WITH VARIABLE"
sqlCmd.Parameters.Add("@parentID", SqlDbType.Int).Direction = ParameterDirection.Output
sqlCmd.ExecuteNonQuery()
parentID = CInt(sqlCmd.Parameters("@parentID").Value)

'repeat the process using the child procedure or if you want combine the parent and
'child process into one stored procedure

sqlCmd.Dispose()
sqlCmd = Nothing
Catch SQLex As SQLException
msgbox SQLex.message
Catch ex As Exception
msgbox ex.message
End Try

May the Almighty God bless us all!

jojo2006
Starting Member

5 Posts

Posted - 2006-05-18 : 13:34:21

Thanks for the help...much appreciated!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources