SqlTransaction problems

[Guest]

Hi Everyone,
I'm currently working on a form that requires 3 stored procs to run (one of
them several times), but these stored procs are in different functions (see
below). The problem is that after the commit, the database doesn't reflect
any of the changes. I open a new connection before calling a proc, and then
pass the same connection to each function and within the function, I set the
transaction to the Command object. Here is the code shortened:
Private Sub cmdSave_Click( ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click
Dim cnSQL as SqlConnection
Dim Success as Boolean = True
try
cnSQL = OpenConnection()
Success = SaveAdmissionNursingAssessment1(cnSQL)
if Success = True then
Success = SaveFamilyHistory(cnSQL)
if not Success then
SaveTrans.Dispose
else
Success = SaveAdmissionNursingAssessment2(cnSQL)
if not Success then
SaveTrans.Dispose
End If
End If
else
cnSQL.Dispose
end if
cmdAdd.Enabled = True
cmdSave.Enabled = False
cmdCancel.Enabled = False
Catch ex As Exception
msgbox(ex.Message )
End Try
End Sub
Private Function SaveAdmissionNursingAssessment1(cnSQL As SqlConnection) as
Boolean
Dim success as Boolean = true
Dim cmSQL As SqlCommand
Try
SaveTrans = cnSQL.BeginTransaction
if isnew then
cmSQL = New SqlCommand("intake_AddAdminNurseAssess1", cnSQL)
cmsql.Transaction = SaveTrans
else
cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment1", cnSQL)
cmsql.Parameters.Add("KeyID", sqldbtype.Int ).Value = fAssessId
cmsql.Transaction = SaveTrans
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value =
fPatientId
cmsql.Parameters.Add("PreparedDate", sqldbtype.SmallDateTime ).Value =
Now()
cmSQL.ExecuteNonQuery()
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
' Close and Clean up objects
cmSQL.Dispose()
SaveAdmissionNursingAssessment1 = success
End Try
End Function
Private Function SaveAdmissionNursingAssessment2(cnSQL As SqlConnection) as
Boolean
Dim success as Boolean = True
Dim cmSQL As SqlCommand
Try
if isnew then
cmSQL = New SqlCommand("intake_AddAdminNurseAssessment2", cnSQL)
cmsql.Transaction = SaveTrans
isnew = False
else
cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment2", cnSQL)
cmsql.Parameters.Add("KeyID", sqldbtype.Int ).Value = fAssessId
cmsql.Transaction = SaveTrans
isDirty = False
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value =
fPatientId
cmSQL.ExecuteNonQuery()
SaveTrans.Commit
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
' Close and Clean up objects
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
SaveTrans.Dispose
SaveAdmissionNursingAssessment2 = success
End Try
End Function
Private Function SaveFamilyHistory(cnSQL As SqlConnection) as Boolean
'Dim cnSQL As SqlConnection
Dim success as Boolean = true
Dim cmSQL As SqlCommand
Dim i as Integer
Try
For i = 2 To flxFamilyHistory1.Rows.Count - 1
if len(trim(flxFamilyHistory1(i, 1))) > 0 or
len(trim(flxFamilyHistory1(i, 3))) > 0 then
if not isnumeric(flxFamilyHistory1(i, 6)) then
cmSQL = New SqlCommand("intake_AddANFamilyHistory", cnSQL)
cmsql.Transaction = SaveTrans
else
cmSQL = New SqlCommand("intake_UpdANFamilyHistory", cnSQL)
cmsql.Parameters.Add("@HistId", sqldbtype.int).Value =
flxFamilyHistory1(i, 0)
cmsql.Transaction = SaveTrans
end if
cmsql.CommandType = CommandType.StoredProcedure
cmsql.Parameters.Add("@AssessId", sqldbtype.Int ).Value = fAssessId
cmsql.Parameters.Add("@PatientID", sqldbtype.Varchar, 20 ).Value =
fPatientId
cmsql.Parameters.Add("@Age", sqldbtype.Varchar, 5 ).Value =
flxFamilyHistory1(i, 1)
cmsql.Parameters.Add("@Health", sqldbtype.Varchar, 25 ).Value =
flxFamilyHistory1(i, 2)
cmsql.Parameters.Add("@AgeOfDeath", sqldbtype.Varchar, 50 ).Value =
flxFamilyHistory1(i, 3)
cmsql.Parameters.Add("@CauseOfDeath", sqldbtype.Varchar, 50 ).Value
= flxFamilyHistory1(i, 4)
cmsql.Parameters.Add("@Who", sqldbtype.Varchar, 50 ).Value =
flxFamilyHistory1(i, 5)
cmsql.Parameters.Add("@SortOrder", sqldbtype.Varchar, 50 ).Value = i
cmSQL.ExecuteNonQuery()
End If
Next
Catch Exp As SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
SaveTrans.Rollback
success = False
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
SaveTrans.Rollback
success = False
finally
cmSQL.Dispose()
SaveFamilyHistory = success
End Try
end function

Thanks for any info. Is there a better way to handle this when crossing
function with our code. Thanks so much for any info.
Michael

 

[Mary Chipman [MSFT]]

Your best bet is not to try to do this in client code. Create one
stored procedure that implements an explicit transaction and calls the
others as needed. Success/failure information can be returned to the
client via output parameters/return value/result set.

--Mary

 

[Guest]

Hi Mary,
Thank you for the reply. I was wondering if there is a limit to the number
of parameters that a stored proc/VS 2005 can handle? I beleive I hit a little
road block when I first started to put these tables together(I had to split
the table into 2 tables). You see. I'm working with a form that contains
about 300 fields. Currently I have the tables split into 2 main tables and a
support table (mind you, this is only for the screen I'm working on now).
What do you think? Thanks again for the reply.
Michael

 

[Mary Chipman [MSFT]]

No single table or form should have 300 fields. Besides the negative
impact on concurrency, server and network resources, no human can
possibly process that much information at a time.

My personal opinion is that you need to normalize your tables before
you dig yourself in any deeper. You are heading into a coding
nightmare that can have no good outcome. An excellent resource is
Database Design for Mere Mortals by Mike Hernandez,
http://www.amazon.com/gp/product/02...002-4394165-3161628?s=books&v=glance&n=283155.
It is easy to understand and is not overly academic.

After that, get a good book on Transact-SQL programming. Whenever you
need an explicit transaction the best practice is to keep it on the
server in a stored procedure where you can implement error handling
and control the outcome that the client must process. It is not
desirable to extend the transaction boundary scope beyond the server
unless you are doing a distributed transaction, which is not the case
here.

--Mary

 

[Guest]

Hi Mary,
Thank you for the reply. The application I'm currently working on is to
replace medical exam forms our company currently has to fill out by hand. I
usually try to avoid creating forms with so many fields, but with these
medical forms I'm not sure how to reduce the amount of fields. This app will
also allow the user to print out the medical forms (word docs) using the data
that was entered into the program. I have normilized the tables the best I
can for now, for example,
Patients
PatientId
Name
etc....
PhysicalExam
ExamId
PatientId
etc....
Admission
AdminId
PatientId
etc...
There are many other tables. For each of the medical forms I have to create,
I have at least one table. And if there is, for example, a form that contains
a many to one relation to the form itself (if there is a medication listing
within the Physical exam form) I break it out into a seperate table. Luckly
not all the medical forms have so many questions on them. I only have about 4
forms that have alot fields. Would you go about breaking down (normilizing)
the table in a case like this.
I will take a look at your book suggestions and try to get them this week
end. Thanks again for the reply.
Michael

 

[Mary Chipman [MSFT]]

Hi Michael,

If you feel that you are over your head with designing this
application, perhaps you might want to consider hiring a more
experienced SQL Server developer to design it for you. In my
experience, mistakes made during the specification and design phase
can propagate outward so that in the end the entire project fails. I
once worked for a consulting firm that tried to "rescue" a complex
application designed by an inexperienced developer, and failed. They
ended up eating $200,000 in client billing when they had to give the
money back.

--Mary