unbounded inserts in transaction

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 2005 Forums

Transact-SQL (2005)

unbounded inserts in transaction

Author

Topic

Angate
Starting Member

24 Posts

Posted - 2009-12-19 : 00:17:51

I am using SQL Server Express 2005 and VB2008 with ASP.NET 3.5.

I am building a web site that allows users to select products and select options for those products. Each option is (usually) itself, a product that can be selected separately. "I want a car with floor mats and fog lights.", each can be selected separately, but I also need to be able to associate the options (i call them child products) with the primary product (parent product).

Currently, I have it set up to run one stored procedure to insert the parent product, return SCOPE_IDENTITY(), and then I loop through each child product and run a separate stored procedure that insert the child and associates it with the parent through a "asChildOf" column that stores the parent's ID, and is left null for the parent itself.

The issue I have is mostly in theory. This is a web based application, and do not know if i should entirely trust SCOPE_IDENTITY(), also, I would like this all to work as one large transaction if possible so I do not miss child products, as for some parent products, there are a requirements that the options must meet, like a minimum number of options.

What does everyone think about this? Is there a way I can put it all into one Stored Procedure? Should I? Is there a way to insure that it all gets inserted and correctly?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-20 : 04:01:13

how are you passing the product and option info to db from application? i think it might be better to pass it as xml, then parse it and do inserts

Angate
Starting Member

24 Posts

Posted - 2009-12-20 : 22:15:54

[code]
Try
comm = New SqlClient.SqlCommand("InsertCartItem", conn)

With comm
.CommandText = "InsertCartItem"
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@customerID", customerID)
.Parameters.AddWithValue("@productID", parentProductIDString)
.Parameters.AddWithValue("@quantity", 1)
.Parameters.AddWithValue("@dateUpdated", Now)
End With
conn.Open()
parentCartIDString = comm.ExecuteScalar.ToString

comm = New SqlClient.SqlCommand("InsertMMChildCartItem", conn)
With comm
.CommandText = "InsertCartItem"
.CommandType = CommandType.StoredProcedure
.Parameters.AddWithValue("@customerID", customerID)
.Parameters.AddWithValue("@asChildOf", parentCartIDString)
.Parameters.AddWithValue("@dateUpdated", Now)
End With
For i = 2 To stringArray.Count - 1

With comm
.Parameters.AddWithValue("@productID", stringArray(i).Substring(0, stringArray(i).IndexOf(":")))
.Parameters.AddWithValue("@quantity", stringArray(i).Substring(stringArray(i).IndexOf(":") + 1))
End With
comm.ExecuteNonQuery()
Next

Return 1
Catch ex As Exception
Return 0
Finally
conn.Close()
End Try
[/code]

I will look into parsing out the string in the procedure in XML

Angate
Starting Member

24 Posts

Posted - 2009-12-21 : 14:51:48

Ok, so I'm working on this XML way of doing it and am running into problems. I see lots of stuff online about importing XML files and what not, but I am unable to reverse engineer them to my needs.

This is what I have so far, If possible, please let me know what is wrong with it, or perhaps a better way of going about it.


USE [Hosted]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[InsertMMChildCartItemtest]
(
	@customerID int,
	@productID int,
	@quantity smallint,
	@dateUpdated smalldatetime,
	@children xml /****** <Products><product><id>11</id><quantity>2</quantity><date>10/10/10</date></product><product><id>12</id><quantity>3</quantity>date>10/10/10</date></product></Products> ******/
)
AS
	DECLARE @id int
	SET NOCOUNT OFF;
	BEGIN	

/****** Insert the parent per the parameters and grab its new ID ******/

	INSERT INTO [dbo].[cartTable] ([customerID], [productID], [quantity], [dateUpdated]) 
		 VALUES (@customerID, @productID, @quantity,  @dateUpdated)
	 
	SET @id = SCOPE_IDENTITY()

/****** Insert the child rows, with some values comming from the parameters, some from the XML ******/

	INSERT INTO [dbo].[cartTable] ([customerID], [productID], [quantity], [asChildOf], [dateUpdated])
		SELECT @customerID, ParamValues.ID.value('.','int'), ParamValues.quantity.value('.','int'), @id, @dateUpdated
		FROM @children.nodes('/Products/product/id') as ParamValues(ID),  @children.nodes('/Products/product/quantity') as ParamValues(quantity)

	END

When creating the procedure, I get the following error, "Msg 1011, Level 16, State 1, Procedure InsertMMChildCartItemtest, Line 19
The correlation name 'ParamValues' is specified multiple times in a FROM clause."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources