Advice on using transactions

J

Julia B

All

I want to use sql transactions on one of my forms as I have multiple updates
to tables to perform and want all or none to commit.

The problem I've got is that each update to a table is done using a
different class and the try, catch block is in a separate database connection
class, which means (as I understand it) transactions won't work. Here's a
simplified version of my code:

Form code:

If deptrecordValid = True and adminrecordValid = True Then
dim currentDept as New Department
currentDept.Dept = Me.tbDept.Text
currentDept.DeptCurrent = Me.cbCurrent.Checked
'this is the first table save
currentDept.SaveRecord()
dim currentDeptAdmin as New DeptAdmin
currentDeptAdmin.Dept = Me.tbDept.Text
currentDeptAdmin.User = Me.ddlAdmin.SelectedValue
'this is the second table save
currentDeptAdmin.SaveRecord()
End If

currentDept.SaveRecord() looks like this (currentDeptAdmin.SaveRecord()
follows the same format).

With New DBConnection
'firstly add the parameters
.AddParameter("@dept", SqlDbType.VarChar, 50, dept)
.AddParameter("@deptcurrent", SqlDbType.Bit, 0, deptCurrent)
'then commit the data
.ExecNonQuerySP("sp_InsertDept")
End With

DBConnection looks like this:

Public Sub AddParameter(ByVal ParamName As String, ByVal paramType As
Data.SqlDbType, ByVal Paramlenght As Int16, ByVal ParamValue As String)
_MyComm.Parameters.Add(ParamName, paramType, Paramlenght)
_MyComm.Parameters(ParamName).Value = ParamValue
End Sub

Public Sub ExecNonQuerySP(ByVal SPName As String)

Dim ConnectionStr As String = _ConnectionStr
Dim Myconn As New SqlConnection(ConnectionStr)
Dim MyAdapter As New SqlDataAdapter(_MyComm)
_MyComm.CommandType = CommandType.StoredProcedure
_MyComm.CommandText = SPName

Myconn.Open()
_MyComm.Connection = Myconn
Try
_MyComm.ExecuteNonQuery()
Catch ex As Exception
System.Web.HttpContext.Current.Session("DBError") = "True"
System.Web.HttpContext.Current.Session("DBEx") = ex.ToString
Finally
If Myconn.State = ConnectionState.Open Then
Myconn.Close()
End If
End Try
End Sub

Has anyone got any suggestions on how to handle this?

Julia
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,930
Messages
2,570,072
Members
46,522
Latest member
Mad-Ram

Latest Threads

Top