R
Rob Meade
Hi all,
I thought I was being clever - no I'm doubting my abilities
(
My project reads in the content from an XML file, this is made up of a
series of "courses", each "course" has a series of items.
As I bring in the data I map it all off to my own classes, and add each
Course class to my collection of courses.
once I have them all I need to dump them to SQL Server.
I create a connection, command, transaction and assign them all accordingly,
however, my problem is that as I iterate through each Course in the
collection I dont want to "commit" the transaction until I've gone through
them all (incase anything screws up), so at the end of the iteration if
everything is ok - it commits, if not - it'll rollback.
My problem is on the second iteration, it realises that the calls to my
stored procedures have already used parameter names which are now trying to
be used again, the result is a "The SqlParameter is already contained by
another SqlParameterCollection." error message.
Anyone got any ideas on how to approach this problem? I'm calling 4 stored
procedures multiple times, they are:
AddCourse (adds the basic components of a course to the Course table)
AddObjecives (adds a product code and text item to the Objectives table)
AddFeatures (adds a product code and text item to the Features table)
AddPreRequisites (adds a product code and text item to the PreRequisites
table)
It all seems ok - until it starst the second iteration of the Course
collection - where presumably there is already a @ProductCode parameter in
my parameters collection...
If I was to comit after each "Course" instead of after all of them this
would obviously get around this problem, but my understanding is that then,
if out of the 1588 courses that should be written to the database, if
something screwed up around the 800 mark, I'd have 799 in the database and
the last lot wouldn't go in...
I could of course write code to check to see if they are already in the
database first, but I'd rather do a "wipe the lot, insert the lot" kind of
operation.
Any information would be appreciated.
Regards
Rob
I thought I was being clever - no I'm doubting my abilities
(My project reads in the content from an XML file, this is made up of a
series of "courses", each "course" has a series of items.
As I bring in the data I map it all off to my own classes, and add each
Course class to my collection of courses.
once I have them all I need to dump them to SQL Server.
I create a connection, command, transaction and assign them all accordingly,
however, my problem is that as I iterate through each Course in the
collection I dont want to "commit" the transaction until I've gone through
them all (incase anything screws up), so at the end of the iteration if
everything is ok - it commits, if not - it'll rollback.
My problem is on the second iteration, it realises that the calls to my
stored procedures have already used parameter names which are now trying to
be used again, the result is a "The SqlParameter is already contained by
another SqlParameterCollection." error message.
Anyone got any ideas on how to approach this problem? I'm calling 4 stored
procedures multiple times, they are:
AddCourse (adds the basic components of a course to the Course table)
AddObjecives (adds a product code and text item to the Objectives table)
AddFeatures (adds a product code and text item to the Features table)
AddPreRequisites (adds a product code and text item to the PreRequisites
table)
It all seems ok - until it starst the second iteration of the Course
collection - where presumably there is already a @ProductCode parameter in
my parameters collection...
If I was to comit after each "Course" instead of after all of them this
would obviously get around this problem, but my understanding is that then,
if out of the 1588 courses that should be written to the database, if
something screwed up around the 800 mark, I'd have 799 in the database and
the last lot wouldn't go in...
I could of course write code to check to see if they are already in the
database first, but I'd rather do a "wipe the lot, insert the lot" kind of
operation.
Any information would be appreciated.
Regards
Rob