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
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