Debug stored procedure that uses comma delimited list to insert multiple records
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.
*/
@FileName varchar(150),
@FileDescription varchar(150),
@FileSize int,
@LanguageID int,
@DocumentCategoryID int,
@ComplianceID int,
@SubmittedBy varchar(50),
@SubmittedDate datetime,
@ProductID varchar(150)AS
BEGIN
DECLARE @FileID INTSET 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)
Values
(@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)Select @FileID=@@Identity
/*
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 + ')'
exec(@ProductFilesInsert)
End
GO
|
0 |
|
Kevin
1/19/2006 4:54:12 AM
asp.net.sql-datasource
29906 articles. 
0 followers.
4 Replies
783 Views
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?
Terri Morton
Engagement Manager, Neudesic
How to ask a question
|
|
0 |
|
tmorton
1/19/2006 1:51:56 PM
Terri:
Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)
Thanks again,
Kevin
|
|
0 |
|
Kevin
1/19/2006 2:29:06 PM
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:
INSERT INTO
ProductFiles
(
FileID,
ProductID
)
SELECT
@FileID,
Product1ID
FROM
Products
INNER JOIN
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
PARAMETERS:
@vcDelimitedString - The string to be split
@vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------
***************************************************************************/
RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
ENDRETURN
END
Terri Morton
Engagement Manager, Neudesic
How to ask a question
|
|
0 |
|
tmorton
1/19/2006 6:02:37 PM
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.
Thanks,
Kevin
|
|
0 |
|
Kevin
1/22/2006 5:57:16 PM
Similar Artilces:
Insert multiple records using one stored procedure call. SQL SERVER 7
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance? Thanks, GaryGary Gary, I'm not clear on exactly what you're asking. 28 dropdowns equates to 28 parameters in your stored procedure if you're just inserting a record. Can you provide more details? BruceBrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. Ok Chuck, never mind about the dropdowns. All Im ask is how to insert multiple records into a database u...
View Inserted Record Using Object Datasource, Details View and Stored Procedure
I am trying to display the record just inserted into a database using an object datasource, a stored procedure and a details view Below is the stored procedureALTER PROCEDURE [dbo].[Employees_Insert]( @LastName varchar(50), @FirstName varchar(30) = null, @JobTitle varchar(50))AS SET NOCOUNT OFF;INSERT INTO [dbo].[Employees] ([LastName], [FirstName], [JobTitle])VALUES (@LastName, @FirstName, @JobTitle); SELECT LastName, FirstName, JobTitleFROM Employees WHERE (EmployeeID = SCOPE_IDENTITY()) ________________________________________________________________...
Use Stored Procedure or insert template to insert a new record?
Hi there, I am using stored procedure for all my database website before I use .net 2. When I come to .net 2, I found almost all the videoes are using SP for select functions, and using insert template to add new ones. What's the differenence and which way is better? Thanks! >which way is better?Whenever you can, use stored procedures - preferably your own, not some of the autogenerated horrors! If you want some help in generating them - see my post in http://forums.asp.net/p/1091006/1635955.aspx#1635955 plus the helper bits in http://forums.asp.net/p/1089533/1631113.a...
How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...
sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...
Using stored procedures to insert recorders
Hi I am not familiar with stored procedures, but I tried to use it with this code and it worked fine ALTER PROCEDURE dbo.p_insert_shortleave @Leave_Date datetime, @Start_time datetime, @End_time datetime, @Reason varchar(150), @Submission_date datetime, @L_D_id_Priority varchar(20)AS INSERT INTO HR_Leave_request (Leave_Date, Start_time, End_time, Reason) ...
Using a stored Procedure to insert a new record
ok I have a stored procedure...... I pass in the variables that are requried....What is the best way to add a record using my stored procedure in VB.net code in a button click event...... How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn CREATE procedure dbo.Appt_AddAppt ( @ClinicID int, @AccountNum nvarchar(10), @DOS nvarchar(12), @POSCODE nvarchar(5) ) as Insert ...
Debugging Stored procedure in .net using ASE
Hi, I'm not a developer, so this is probably a stupid question. One of my developers claims that it is not possible to debug down to the stored procedure level using Sybase ASE as the backend of a .NET setup. In other words, I'd like to know if the ADO.NET driver here provides my developers the same "debug" capabitliy in ASE (via the usual Visual Studio ..NET tools) as they would have with MSSQL. Hi Kevin, Could you elaborate on this? How would you do it with MS SQL Server? Sounds like this might for an enhancement request. pv "Sherlock, Kevin&q...
How to insert multiple rows using stored procedure
How to insert multiple rows with using a single stored procedure and favourably as an atomic process? You need to inlude SAVE POINT in your T-SQL code so a rollback or interruption will not take the Transaction back to the beginnning. Like the sample below. Hope this helps. SAVE TRANSACTION SavepointName IF @@error= some Error BEGIN ROLLBACK TRANSACTION SavepointName COMMIT TRANSACTION END Kind regards, Gift PeddieKind regards,Gift Peddie You have a couple of options here: 1) created a delimited key,value pair and parse it in the proc 2) package the v...
Problem with inserting a Record using textbox and stored procedure
Below is the code for a form with several textboxes that I would like to use the values from to insert a record using a stored procedure. I am not having any sucess. After entering all the values and clicking the submit button no record gets entered. Any help would be greatly appreciated. Thanks in advance Gary <%@ Page Language="VB" Debug="true" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Register_Click(ByVal sender A...
Debug Stored Procedure using Visual Studio.NET
I am trying to debug a stored procedure following the instruction at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316549. But when I set the breakpoint in the stored procedure and run the web app, the breakpoint has little white question mark when I mouse over the break point I get this message: "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." Has anyone been able do this successfully?...
Inserting Multiple rows in loop with a Sql stored Procedures
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue? Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String) 'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data S...
regarding sql stored procedures debugging from VC# .Net
i have done all the required changes for debugging the sql stored procedure from VC# .net application.On debugging , the debugger goes into stored procedure from VC# code , but after debugging the stored procedure debugger donot return back to VC# coding even when i have used some checkpoints in VC# application....
Debug Stored Procedure using Visual Studio.NET
I am trying to debug a stored procedure following the instruction at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316549. But when I set the breakpoint in the stored procedure and run the web app, the breakpoint has little white question mark when I mouse over the break point I get this message: "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." Has anyone been able do this successfully? I haven't done this, since our dba's don't allow us to install the components needed...
Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance? Thanks, GaryGary Gary, I'm not clear on exactly what you're asking. 28 dropdowns equates to 28 parameters in your stored procedure if you're just inserting a record. Can you provide more details? BruceBrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. Ok Chuck, never mind about the dropdowns. All Im ask is how to insert multiple records into a database u...
View Inserted Record Using Object Datasource, Details View and Stored Procedure
I am trying to display the record just inserted into a database using an object datasource, a stored procedure and a details view Below is the stored procedureALTER PROCEDURE [dbo].[Employees_Insert]( @LastName varchar(50), @FirstName varchar(30) = null, @JobTitle varchar(50))AS SET NOCOUNT OFF;INSERT INTO [dbo].[Employees] ([LastName], [FirstName], [JobTitle])VALUES (@LastName, @FirstName, @JobTitle); SELECT LastName, FirstName, JobTitleFROM Employees WHERE (EmployeeID = SCOPE_IDENTITY()) ________________________________________________________________...
Use Stored Procedure or insert template to insert a new record?
Hi there, I am using stored procedure for all my database website before I use .net 2. When I come to .net 2, I found almost all the videoes are using SP for select functions, and using insert template to add new ones. What's the differenence and which way is better? Thanks! >which way is better?Whenever you can, use stored procedures - preferably your own, not some of the autogenerated horrors! If you want some help in generating them - see my post in http://forums.asp.net/p/1091006/1635955.aspx#1635955 plus the helper bits in http://forums.asp.net/p/1089533/1631113.a...
How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...
sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...
Using stored procedures to insert recorders
Hi I am not familiar with stored procedures, but I tried to use it with this code and it worked fine ALTER PROCEDURE dbo.p_insert_shortleave @Leave_Date datetime, @Start_time datetime, @End_time datetime, @Reason varchar(150), @Submission_date datetime, @L_D_id_Priority varchar(20)AS INSERT INTO HR_Leave_request (Leave_Date, Start_time, End_time, Reason) ...
Using a stored Procedure to insert a new record
ok I have a stored procedure...... I pass in the variables that are requried....What is the best way to add a record using my stored procedure in VB.net code in a button click event...... How might i do this with a data reader,,data adapter.....OR What.......................Do I need to declare all my varaibles I am adding to this new record in the line after POSCODE or can vb.net do this without a parameter statemetn CREATE procedure dbo.Appt_AddAppt ( @ClinicID int, @AccountNum nvarchar(10), @DOS nvarchar(12), @POSCODE nvarchar(5) ) as Insert ...
Debugging Stored procedure in .net using ASE
Hi, I'm not a developer, so this is probably a stupid question. One of my developers claims that it is not possible to debug down to the stored procedure level using Sybase ASE as the backend of a .NET setup. In other words, I'd like to know if the ADO.NET driver here provides my developers the same "debug" capabitliy in ASE (via the usual Visual Studio ..NET tools) as they would have with MSSQL. Hi Kevin, Could you elaborate on this? How would you do it with MS SQL Server? Sounds like this might for an enhancement request. pv "Sherlock, Kevin&q...
How to insert multiple rows using stored procedure
How to insert multiple rows with using a single stored procedure and favourably as an atomic process? You need to inlude SAVE POINT in your T-SQL code so a rollback or interruption will not take the Transaction back to the beginnning. Like the sample below. Hope this helps. SAVE TRANSACTION SavepointName IF @@error= some Error BEGIN ROLLBACK TRANSACTION SavepointName COMMIT TRANSACTION END Kind regards, Gift PeddieKind regards,Gift Peddie You have a couple of options here: 1) created a delimited key,value pair and parse it in the proc 2) package the v...
Problem with inserting a Record using textbox and stored procedure
Below is the code for a form with several textboxes that I would like to use the values from to insert a record using a stored procedure. I am not having any sucess. After entering all the values and clicking the submit button no record gets entered. Any help would be greatly appreciated. Thanks in advance Gary <%@ Page Language="VB" Debug="true" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <script runat="server"> Sub Register_Click(ByVal sender A...
Debug Stored Procedure using Visual Studio.NET
I am trying to debug a stored procedure following the instruction at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316549. But when I set the breakpoint in the stored procedure and run the web app, the breakpoint has little white question mark when I mouse over the break point I get this message: "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." Has anyone been able do this successfully?...
Inserting Multiple rows in loop with a Sql stored Procedures
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue? Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String) 'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data S...
regarding sql stored procedures debugging from VC# .Net
i have done all the required changes for debugging the sql stored procedure from VC# .net application.On debugging , the debugger goes into stored procedure from VC# code , but after debugging the stored procedure debugger donot return back to VC# coding even when i have used some checkpoints in VC# application....
Debug Stored Procedure using Visual Studio.NET
I am trying to debug a stored procedure following the instruction at: http://support.microsoft.com/default.aspx?scid=kb;EN-US;316549. But when I set the breakpoint in the stored procedure and run the web app, the breakpoint has little white question mark when I mouse over the break point I get this message: "The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded." Has anyone been able do this successfully? I haven't done this, since our dba's don't allow us to install the components needed...
Web resources about - Debug stored procedure that uses comma delimited list to insert multiple records - asp.net.sql-datasource
Parliamentary procedure - Wikipedia, the free encyclopedia... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...
Procedure is more a snap than a snipA QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...
Rushed cosmetic procedures a 'recipe for disaster'​When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...
Jetstar procedures under investigation after planes took off too heavyJetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...
Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?
What is some of the worst behavior you've witnesses by attorneys?
NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher ReportThe NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...
We need to reform the culture of law enforcement, not just the proceduresWe need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...
Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...
TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...
'Painless' dental cavity procedure regrows tooth enamel... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...
Resources last updated: 1/5/2016 7:11:36 PM