Vincent Stack
asked on
Begin Transaction Commit Transaction for Loop
Hi,
I have an asp 'survey' application that sends data to a SQL SERVER 2005 Database. The problem is that I send the results to the database in a loop with an Insert for each question (max 20 questions) so how do I ensure data integrity when each transaction is autonomous. The asp code looks something like this:
<%
Get the data (QuestionID and Result) --- result is integer from 0 -5
Do While QuestionCount < NumQuestions
Get Response Details
Insert To SQL Stored Procedure(QuestionID, Result, CourseID, Section, Comments, Instructor...)
Loop
I have a Begin Transaction and Commit Transaction in the stored procedure but if there is a problem, only the current record will not be committed - the previous records have been committed and I am left with an incomplete survey. A problem could and did happen, even though I code for 'SQL' oddities in the comments.
I have thought about holding all 20 of the question results and comments in some sort of an array and passing an array to SQL but that seems very difficult and overly complex. HELP.
Vince Stack
I have an asp 'survey' application that sends data to a SQL SERVER 2005 Database. The problem is that I send the results to the database in a loop with an Insert for each question (max 20 questions) so how do I ensure data integrity when each transaction is autonomous. The asp code looks something like this:
<%
Get the data (QuestionID and Result) --- result is integer from 0 -5
Do While QuestionCount < NumQuestions
Get Response Details
Insert To SQL Stored Procedure(QuestionID, Result, CourseID, Section, Comments, Instructor...)
Loop
I have a Begin Transaction and Commit Transaction in the stored procedure but if there is a problem, only the current record will not be committed - the previous records have been committed and I am left with an incomplete survey. A problem could and did happen, even though I code for 'SQL' oddities in the comments.
I have thought about holding all 20 of the question results and comments in some sort of an array and passing an array to SQL but that seems very difficult and overly complex. HELP.
Vince Stack
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was fast and it sounds like a reasonable solution; however, how do I return the scope identity and how do I not have my application blow up when a problem occurs. For example, how do I test for an error and simply send a message back to .asp from the stored procedure that there was a problem?
Here is my stored procedure:
ALTER PROCEDURE [dbo].[qInsertDataToTblRes ults]
@QuestionID int,
@InstructorID int,
@Result int,
@ProgramID int,
@Comments varchar(max),
@CourseID nvarchar(50),
@SectionID int,
@LongAnswer int, */ this is 0 or 1 indicating a question that requires a long answer (comment)
@Facility int, */ this is 0 or 1 indicating a question about campus facilites and not about a particular course
@CourseSpecific int,
@SurveyID int,
@InstructorSpecific int,
@surveydate nvarchar(50) */ this is simply a string indicating the semester and year (FALL2006)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Begin Transaction
Insert Into tblSurveyResults (QuestionID, InstructorID, Result, ProgramID, Comments, CourseID, SectionID, LongAnswer, Facility, CourseSpecific, SurveyID, InstructorSpecific, SurveyDate) Values (@QuestionID, @InstructorID, @Result, @ProgramID, @Comments, @CourseID, @SectionID, @LongAnswer, @Facility, @CourseSpecific, @SurveyID, @InstructorSpecific, @SurveyDate)
Commit Transaction
END
Here is my stored procedure:
ALTER PROCEDURE [dbo].[qInsertDataToTblRes
@QuestionID int,
@InstructorID int,
@Result int,
@ProgramID int,
@Comments varchar(max),
@CourseID nvarchar(50),
@SectionID int,
@LongAnswer int, */ this is 0 or 1 indicating a question that requires a long answer (comment)
@Facility int, */ this is 0 or 1 indicating a question about campus facilites and not about a particular course
@CourseSpecific int,
@SurveyID int,
@InstructorSpecific int,
@surveydate nvarchar(50) */ this is simply a string indicating the semester and year (FALL2006)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Begin Transaction
Insert Into tblSurveyResults (QuestionID, InstructorID, Result, ProgramID, Comments, CourseID, SectionID, LongAnswer, Facility, CourseSpecific, SurveyID, InstructorSpecific, SurveyDate) Values (@QuestionID, @InstructorID, @Result, @ProgramID, @Comments, @CourseID, @SectionID, @LongAnswer, @Facility, @CourseSpecific, @SurveyID, @InstructorSpecific, @SurveyDate)
Commit Transaction
END
ASKER
MS SQL Server supports nested transactions? See this is where my competency ends. Do you mean that I could put a Begin Transaction before my loop and a Commit Transaction at the end of the loop in .asp?
The best option in this situation is to build an xml at the front end and pass it to a modified version of the above procedure.This needs only one transaction ..
:)
:)
aneeshattingal's option sounds the cleanest of all, then you only need to process the results once - send them to the database once - then have the stored procedure that accepts the xml just return if it was successful or not.
generally, yes.
i have no enough experience with asp. i believe you can do it in your asp code. however, even in a desktop application i would pass the question Id's to another stored procedure which contains a similar loop that call another stored procedure (the one you mentioned and posted).
regards,
ewilde.
i have no enough experience with asp. i believe you can do it in your asp code. however, even in a desktop application i would pass the question Id's to another stored procedure which contains a similar loop that call another stored procedure (the one you mentioned and posted).
regards,
ewilde.
ASKER
XML - while that sounds great, I have no experience with it.
Hi,
In terms of getting the XML to work the below questions have expamles of how a stored procedure can read the xml and some loose information on how to create it.
http:Q_21602148.html "Passing multiple values to a WHERE IN statement using a parameter"
http:Q_21583322.html "Inserting multiple rows efficiently"
http:Q_21536499.html "Multiple records added to TABLE variable (stored procedure)"
In terms of getting the XML to work the below questions have expamles of how a stored procedure can read the xml and some loose information on how to create it.
http:Q_21602148.html "Passing multiple values to a WHERE IN statement using a parameter"
http:Q_21583322.html "Inserting multiple rows efficiently"
http:Q_21536499.html "Multiple records added to TABLE variable (stored procedure)"
vstack,
> XML - while that sounds great, I have no experience with it.
The XML building process may be bit hard, in sql it is simple. read Boos online
> XML - while that sounds great, I have no experience with it.
The XML building process may be bit hard, in sql it is simple. read Boos online
ASKER
Thanks. I'll read up on it. Sounds like something I should know.
Here is another example using OPENXML:
https://www.experts-exchange.com/questions/21599595/Inserting-multiple-rows-from-a-single-insert-statement.html
Or better still post some sample insert statements, your table structure and we can provide a full solution.
https://www.experts-exchange.com/questions/21599595/Inserting-multiple-rows-from-a-single-insert-statement.html
Or better still post some sample insert statements, your table structure and we can provide a full solution.
ASKER
Wow!
Here is the insert I use and the loop.
Do While QCount < Cint(NumQuestions)
correspondComment = QuestionCountArray(QCount)
CommentCount = Cstr(correspondComment)
theComment = theComment + CommentCount
theComment = Request.Form(theComment)
theComment = SQLFixup(theComment)
correspondResult = QuestionCountArray(QCount)
correspondResult = cstr(correspondResult)
correspondResult = Request.Form(QPrefix + correspondResult)
if correspondResult = "" then
correspondResult = 4
end if
correspondResult = correspondResult - 0
QuestionID = QuestionCountArray(QCount)
QuestionID = QuestionID - 0
if QuestionID > 0 then
Set objRS_instructorspecific = Server.CreateObject("ADODB .Recordset ")
'this tells me whether or not the question is specific to an instructor
'like does instructor show up on time?
'important for calculating instructor results
'I suppose I could somehow pass this from the questions page
objRS_instructorspecific.O pen "Exec qGetInstructorSpecificQues tions " & QuestionID, objConn
else
response.write "This survey has been completed OR you have attempted to submit without using the Submit Survey button."
Session.Abandon
response.end
end if
instructorspecific = objRS_instructorspecific(" Instructor Specific")
instructorspecific = instructorspecific - 0
objRS_instructorspecific.C lose
Set objRS_instructorspecific = Nothing
'new insert into results
CourseSpecific = 1
'indicates a question regarding college facilities like lbrary, cafeteria, etc. In this case always 0
Facility = 0
'I process all short answer (radio button responses here so I know that all Q's are short answer types
LongAnswer = 0
'insert statement
objConn.Execute("qInsertDa taToTblRes ults " & QuestionID & ", " & InstructorID & "," & correspondResult & ", " & ProgramID & ", '" & theComment & "', '" & CourseID & "', " & SectionID & ", " & LongAnswer & ", " & Facility & ", " & CourseSpecific & ", " & SurveyID & "," & instructorspecific & ", '" & surveydate & "'")
theComment = "Comment"
QCount = QCount + 1
Loop
The info is gathered and sent to a results table
Here is the results table structure
CREATE TABLE [dbo].[tblSurveyResults](
[QuestionID] [int] NOT NULL CONSTRAINT [DF__tblSurvey__Quest__7C8 480AE] DEFAULT ((0)),
[Result] [float] NOT NULL CONSTRAINT [DF__tblSurvey__Resul__7D7 8A4E7] DEFAULT ((4)),
[InstructorID] [int] NULL CONSTRAINT [DF__tblSurvey__Instr__7E6 CC920] DEFAULT ((0)),
[CourseID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL,
[Comments] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL CONSTRAINT [DF_tblSurveyResults_Comme nts] DEFAULT (NULL),
[ProgramID] [int] NULL CONSTRAINT [DF__tblSurvey__Progr__7F6 0ED59] DEFAULT ((0)),
[SectionID] [int] NULL CONSTRAINT [DF__tblSurvey__Secti__005 51192] DEFAULT ((1)),
[LongAnswer] [bit] NULL CONSTRAINT [DF__tblSurvey__LongA__014 935CB] DEFAULT ((0)),
[CourseSpecific] [bit] NULL CONSTRAINT [DF__tblSurvey__Cours__023 D5A04] DEFAULT ((0)),
[Facility] [bit] NULL CONSTRAINT [DF__tblSurvey__Facil__033 17E3D] DEFAULT ((0)),
[SurveyID] [int] NOT NULL CONSTRAINT [DF__tblSurvey__Surve__042 5A276] DEFAULT ((0)),
[InstructorSpecific] [bit] NULL CONSTRAINT [DF__tblSurvey__Instr__051 9C6AF] DEFAULT ((0)),
[SurveyDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_ AS NULL
)
Here is the insert I use and the loop.
Do While QCount < Cint(NumQuestions)
correspondComment = QuestionCountArray(QCount)
CommentCount = Cstr(correspondComment)
theComment = theComment + CommentCount
theComment = Request.Form(theComment)
theComment = SQLFixup(theComment)
correspondResult = QuestionCountArray(QCount)
correspondResult = cstr(correspondResult)
correspondResult = Request.Form(QPrefix + correspondResult)
if correspondResult = "" then
correspondResult = 4
end if
correspondResult = correspondResult - 0
QuestionID = QuestionCountArray(QCount)
QuestionID = QuestionID - 0
if QuestionID > 0 then
Set objRS_instructorspecific = Server.CreateObject("ADODB
'this tells me whether or not the question is specific to an instructor
'like does instructor show up on time?
'important for calculating instructor results
'I suppose I could somehow pass this from the questions page
objRS_instructorspecific.O
else
response.write "This survey has been completed OR you have attempted to submit without using the Submit Survey button."
Session.Abandon
response.end
end if
instructorspecific = objRS_instructorspecific("
instructorspecific = instructorspecific - 0
objRS_instructorspecific.C
Set objRS_instructorspecific = Nothing
'new insert into results
CourseSpecific = 1
'indicates a question regarding college facilities like lbrary, cafeteria, etc. In this case always 0
Facility = 0
'I process all short answer (radio button responses here so I know that all Q's are short answer types
LongAnswer = 0
'insert statement
objConn.Execute("qInsertDa
theComment = "Comment"
QCount = QCount + 1
Loop
The info is gathered and sent to a results table
Here is the results table structure
CREATE TABLE [dbo].[tblSurveyResults](
[QuestionID] [int] NOT NULL CONSTRAINT [DF__tblSurvey__Quest__7C8
[Result] [float] NOT NULL CONSTRAINT [DF__tblSurvey__Resul__7D7
[InstructorID] [int] NULL CONSTRAINT [DF__tblSurvey__Instr__7E6
[CourseID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_
[Comments] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_
[ProgramID] [int] NULL CONSTRAINT [DF__tblSurvey__Progr__7F6
[SectionID] [int] NULL CONSTRAINT [DF__tblSurvey__Secti__005
[LongAnswer] [bit] NULL CONSTRAINT [DF__tblSurvey__LongA__014
[CourseSpecific] [bit] NULL CONSTRAINT [DF__tblSurvey__Cours__023
[Facility] [bit] NULL CONSTRAINT [DF__tblSurvey__Facil__033
[SurveyID] [int] NOT NULL CONSTRAINT [DF__tblSurvey__Surve__042
[InstructorSpecific] [bit] NULL CONSTRAINT [DF__tblSurvey__Instr__051
[SurveyDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_
)
If you want to pursue using Xml, than you should understand that there are two stages to this.
1. Make sure that the Stored Procedure functions as you would like.
2. Make sure the ASP builds the Xml documents correctly and calls the Stored procedure appropriately.
Here in this Topic Area we can address step 1 and give you general pointers for step 2.
So first see if you can "post some sample insert statements" as they stand now (three or four would be adequate), so that we can make sure you understand how the stored procedure works. I can certainly make up some values for the parameters, but it may not be very meaningful.
1. Make sure that the Stored Procedure functions as you would like.
2. Make sure the ASP builds the Xml documents correctly and calls the Stored procedure appropriately.
Here in this Topic Area we can address step 1 and give you general pointers for step 2.
So first see if you can "post some sample insert statements" as they stand now (three or four would be adequate), so that we can make sure you understand how the stored procedure works. I can certainly make up some values for the parameters, but it may not be very meaningful.
To give you an idea, the stored procedure could look like this (using xml attributes for the values):
ALTER PROCEDURE dbo.qInsertXmlDataToTblRes ults
@Questions varchar(max)
AS
Declare @iDoc int
SET NOCOUNT ON
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Questions
Insert tblSurveyResults (
QuestionID, InstructorID, Result, ProgramID, Comments, CourseID, SectionID, LongAnswer,
Facility, CourseSpecific, SurveyID, InstructorSpecific, SurveyDate)
Select QuestionID, InstructorID, Result, ProgramID, Comments, CourseID, SectionID,
LongAnswer, Facility, CourseSpecific, SurveyID, InstructorSpecific, SurveyDate
From OPENXML (@iDoc, 'Questions/Question', 1) WITH (
QuestionID integer '@questionID',
InstructorID integer '@instructorID',
Result int '@result',
ProgramID integer '@programID',
Comments varchar(max) '@comments',
CourseID nvarchar(50) '@courseID',
SectionID int '@sectionID',
LongAnswer int '@longAnswer',
Facility int '@facility',
CourseSpecific int '@courseSpecific',
SurveyID int '@surveyID',
InstructorSpecific int '@instructorSpecific',
SurveyDate nvarchar(50) '@surveyDate')
EXEC sp_xml_removedocument @iDoc
ALTER PROCEDURE dbo.qInsertXmlDataToTblRes
@Questions varchar(max)
AS
Declare @iDoc int
SET NOCOUNT ON
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Questions
Insert tblSurveyResults (
QuestionID, InstructorID, Result, ProgramID, Comments, CourseID, SectionID, LongAnswer,
Facility, CourseSpecific, SurveyID, InstructorSpecific, SurveyDate)
Select QuestionID, InstructorID, Result, ProgramID, Comments, CourseID, SectionID,
LongAnswer, Facility, CourseSpecific, SurveyID, InstructorSpecific, SurveyDate
From OPENXML (@iDoc, 'Questions/Question', 1) WITH (
QuestionID integer '@questionID',
InstructorID integer '@instructorID',
Result int '@result',
ProgramID integer '@programID',
Comments varchar(max) '@comments',
CourseID nvarchar(50) '@courseID',
SectionID int '@sectionID',
LongAnswer int '@longAnswer',
Facility int '@facility',
CourseSpecific int '@courseSpecific',
SurveyID int '@surveyID',
InstructorSpecific int '@instructorSpecific',
SurveyDate nvarchar(50) '@surveyDate')
EXEC sp_xml_removedocument @iDoc
hope you didn't try the XML part
:(
:(
ASKER
I tried it but I could not get anywhere with it. I still appreciate the suggestion.
>I tried it but I could not get anywhere with it.
I think you should post here to see where you went wrong ?
I think you should post here to see where you went wrong ?
>>I tried it but I could not get anywhere with it. <<
Unfortunately this is the MS SQL Server Topic Area not the ESP TA, so it is difficult to help without feedback.
Unfortunately this is the MS SQL Server Topic Area not the ESP TA, so it is difficult to help without feedback.
regards,
ewilde.