using SqlTransaction in ADO.NET Problem

[Ric Pullen]

I am writing a data upload routine, i need to split 1 row of data into 3
tables.

I am tring to do the followin

Insert Patient Details
Insert Sample
Loop
if Old_Sample <> Current_Sample then Insert_Sample
Loop
Insert_Resuilts
end loop
end loop

Each of these insert commands is a stored procedure wich is associated with
the same tranasaction.

If the data successfully inserts without problems the data is commited.
IF it fails the application generates and unhandled exception in my Catch
Code where i tell it to Rollback. My code is shown below so if anyone can
help me out because even the ADO.NET book by MS Press only does simple 1
simple tranascations.

Dim objCmdPatient As SqlCommand = SqlConnection.CreateCommand

Dim objCmdSample As SqlCommand = SqlConnection.CreateCommand

Dim objCmdResult As SqlCommand = SqlConnection.CreateCommand

Dim myTrans As SqlTransaction

BuildCmdPatient(objCmdPatient)

BuildCmdSample(objCmdSample)

BuildCmdResult(objCmdResult)

myTrans = SqlConnection.BeginTransaction

objCmdPatient.Transaction = myTrans

objCmdSample.Transaction = myTrans

objCmdResult.Transaction = myTrans

Try

With objCmdPatient

.Parameters("@Unitno").Value = FormatUnitNo(strarray(0))

.Parameters("@Surname").Value = formatName(strarray(1), True)

.Parameters("@Forename").Value = formatName(strarray(1), False)

.Parameters("@DOB").Value = formatDOBDate(strarray(2).Trim)

.Parameters("@Sex").Value = strarray(3).Trim

.ExecuteNonQuery()

iPatient = .Parameters("@Patient_ID").Value

End With

strSample = strarray(6).Trim

With objCmdSample

..Parameters("@PatientFK").Value = iPatient

..Parameters("@Referrer").Value = strarray(4).Trim

..Parameters("@Location").Value = strarray(5).Trim

..Parameters("@SampleNo").Value = strSample

..Parameters("@SpeciminDate").Value = formatSpecDate(strarray(9))

..ExecuteNonQuery()

iSample = .Parameters("@SampleID").Value

End With

strLastSample = strarray(6).Trim

For i = 0 To arrPatient.Count - 1

strarray = arrPatient(i).ToString.Split("|")

If strLastSample <> strSample Then

strSample = strarray(6).Trim

With objCmdSample

..Parameters("@Referrer").Value = strarray(4).Trim

..Parameters("@Location").Value = strarray(5).Trim

..Parameters("@SampleNo").Value = strarray(6).Trim

..Parameters("@SpeciminDate").Value = formatSpecDate(strarray(9))

..ExecuteNonQuery()

iSample = .Parameters("@SampleID").Value

End With

strLastSample = strSample

End If

With objCmdResult

..Parameters("@Sample_FK").Value = iSample

..Parameters("@Test").Value = strarray(7).Trim

..Parameters("@Result").Value = strarray(8).Trim

objCmdResult.ExecuteNonQuery()

End With

Next

myTrans.Commit()

Catch objErr As Exception

MessageBox.Show(objErr.Message)

WriteToErrorFile(arrPatient)

myTrans.Rollback() <--- Unhandled exception occurs

End Try

End Function

 

[Guest]

I suspect the error being thrown to your handler is firing before your execute commands. You may be chasing down a bug with your ransaction logic, when the problem might well be an unitialized object or something, which seems more likely. Why not put a break point in your Catch section and see what the ex.Message is before the Rollback? Or Debug.Write(ex.Message)

Good luck

Joh

 

[Chris B]

...............................

myTrans.Commit()

Catch objErr As Exception

MessageBox.Show(objErr.Message)

WriteToErrorFile(arrPatient)

myTrans.Rollback() <--- Unhandled exception occurs

...............................................

What exception?
Has the actual rollback failed?
-- In my limited experience the rollback call above can timeout and throw an
exception but the database rollback will succeed.