SqlTransaction problems

microsoft.public.dotnet.framework.adonet

26 Jan 2006 6:27 PM

Michael

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

31 Jan 2006 2:17 AM

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

On Thu, 26 Jan 2006 10:27:04 -0800, "Michael"
<Mich***@discussions.microsoft.com> wrote:

Show quote

>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

31 Jan 2006 1:40 PM

Michael

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

Show quote

"Mary Chipman [MSFT]" wrote:

> 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
>
> On Thu, 26 Jan 2006 10:27:04 -0800, "Michael"
> <Mich***@discussions.microsoft.com> wrote:
>
> >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
>

1 Feb 2006 9:49 PM

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/0201752840/qid=1138829972/sr=2-1/ref=pd_bbs_b_2_1/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

On Tue, 31 Jan 2006 05:40:24 -0800, "Michael"
<Mich***@discussions.microsoft.com> wrote:

Show quote

>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]" wrote:
>
>> 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
>>
>> On Thu, 26 Jan 2006 10:27:04 -0800, "Michael"
>> <Mich***@discussions.microsoft.com> wrote:
>>
>> >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
>>

2 Feb 2006 3:28 PM

Michael

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

Show quote

"Mary Chipman [MSFT]" wrote:

> 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/0201752840/qid=1138829972/sr=2-1/ref=pd_bbs_b_2_1/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
>
> On Tue, 31 Jan 2006 05:40:24 -0800, "Michael"
> <Mich***@discussions.microsoft.com> wrote:
>
> >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]" wrote:
> >
> >> 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
> >>
> >> On Thu, 26 Jan 2006 10:27:04 -0800, "Michael"
> >> <Mich***@discussions.microsoft.com> wrote:
> >>
> >> >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
> >>
>

3 Feb 2006 9:09 PM

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

On Thu, 2 Feb 2006 07:28:11 -0800, "Michael"
<Mich***@discussions.microsoft.com> wrote:

Show quote

>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]" wrote:
>
>> 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/0201752840/qid=1138829972/sr=2-1/ref=pd_bbs_b_2_1/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
>>
>> On Tue, 31 Jan 2006 05:40:24 -0800, "Michael"
>> <Mich***@discussions.microsoft.com> wrote:
>>
>> >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]" wrote:
>> >
>> >> 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
>> >>
>> >> On Thu, 26 Jan 2006 10:27:04 -0800, "Michael"
>> >> <Mich***@discussions.microsoft.com> wrote:
>> >>
>> >> >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
>> >>
>>