I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:
exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8'
Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)
CREATE PROCEDURE proc_Add_Product_Files_v2
Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a row
into productfiles for each product that the file being uploaded pertains to.
DECLARE @FileID INT
SET NOCOUNT ON
Insert into the files table and retrieve the primary key of the new record using @@identity
INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate)
(@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)
Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.
DECLARE @ProductFilesInsert varchar(2000)
SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')'
I created your stored procedure locally, and did a PRINT of @ProductFilesInsert and all looks good to me. Setting @FileID = 0 instead of selecting its value to be @@Identity, this is what @ProductFilesInsert contains, and that is syntactically correct:
INSERT INTO ProductFiles (FileID, ProductID) SELECT 0, Product1ID FROM Products WHERE Product1ID IN (10,11,8)
Your stored procedure is named proc_Add_Product_Files_v2, yet you are executing proc_Add_Product_Files. Is the problem simply that your are executing an old version of your stored procedure?
Engagement Manager, Neudesic
How to ask a question
Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)
Kevin.McPhail wrote:Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)
It wasn't obvious to me. The only reason I noticed was that exec proc_Add_Product_Files failed failed for me because I didn't have the original in place :-) I can't tell you how many times I've been burned by the very same thing.
For what it's worth, I am not a big fan of dynamic SQL, especially when an alternate methodology is possible. You could use this approach instead:
dbo.Split(@ProductID,',') AS A ON Products.Product1ID = A.Element
There are many variations of a "split" function. Here's one that Dinakar provided in this thread: http://forums.asp.net/989365/ShowPost.aspx:
CREATE FUNCTION [dbo].[Split] ( @vcDelimitedString nVarChar(4000),
@vcDelimiter nVarChar(100) )
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
@vcDelimitedString - The string to be split
@vcDelimiter - String containing the delimiter where
delimited string should be split
Table data type containing array of strings that were split with
the delimiters removed from the source string
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------
RETURNS @tblArray TABLE
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)
INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
Engagement Manager, Neudesic
How to ask a question
Thanks again Terri! I had been looking for a good understandable (not a sql guru) way to pass a delimited string or array to sql for inserts. I read through a couple articles i found that left my head spinning and decided to go with the old dynamic sql method since i at least understood what it did. Your example(and Dinakar and Karen's ) is exactly what i had been looking for.