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...)
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.