1 Attachment(s)
Error when performing a transaction with command object
I try to create a transaction with DB, when I execute the code without transaction, record are created correctly.
But when I use transaction, at the first executenonquery, the catch exception is raised with message in attachment
Does anyone know the reason ???
Thanks ;-)
Dim KeyExpediteur As Integer
Dim KeyDossier As Integer
Dim cString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=C:\Documents and Settings\Sebastien\Mes documents\Visual Studio Projects\Politis Project\politis3.mdb"
Dim TempKEY As String
Dim cnPolitis As OleDbConnection = New OleDbConnection(cString)
Dim Param As OleDbParameter
Dim OleReader As OleDbDataReader
cnPolitis.Open()
' 1- New transaction
Dim OleDbTrans As OleDbTransaction
Dim CmdOleDb As New OleDbCommand()
OleDbTrans = cnPolitis.BeginTransaction()
CmdOleDb.Transaction = OleDbTrans
Try
' 2- Create Expediteur if new
If DSCourrier1.EXPEDITEURS.Item(0).RowState = DataRowState.Added Then
CmdOleDb = New OleDbCommand("AjoutExpediteur", cnPolitis)
CmdOleDb.CommandType = CommandType.StoredProcedure
CmdOleDb.Parameters.Clear()
Param = New OleDbParameter()
Param.ParameterName = "@RegistreNational"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurRegistreNational.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Nom"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurNom.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Prenom"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurPrenom.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Adresse"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurRue.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Numero"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurNumero.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Localite"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurLocalite.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@CodePostal"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurCodePostal.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Telephone"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurTelephone.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Fax"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurFax.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@Email"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxExpediteurEmail.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@TempKEY"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
TempKEY = Form_Main.Key_Utilisateur & " # " & Now
Param.Value = TempKEY
CmdOleDb.Parameters.Add(Param)
CmdOleDb.ExecuteNonQuery()
CmdOleDb = New OleDbCommand("FindExpediteurTempKEY", cnPolitis)
CmdOleDb.CommandType = CommandType.StoredProcedure
CmdOleDb.Parameters.Clear()
Param = New OleDbParameter()
Param.ParameterName = "@TempKEY"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TempKEY
CmdOleDb.Parameters.Add(Param)
KeyExpediteur = CmdOleDb.ExecuteScalar
If KeyExpediteur = 0 Then
MsgBox("Erreur lors de la création de l'expéditeur, veuillez réessayer ou contacter l'administrateur si le problème persiste", MsgBoxStyle.Critical, "DB error")
Exit Sub
End If
End If
' 3- Create Dossier if new
If TextBoxNumDossier.Text = "" Then
CmdOleDb = New OleDbCommand("AjoutDossier", cnPolitis)
CmdOleDb.CommandType = CommandType.StoredProcedure
CmdOleDb.Parameters.Clear()
Param = New OleDbParameter()
Param.ParameterName = "@TYPEDOSSIER"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = ComboBoxTypeDossier.SelectedValue
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@NUM_AGENT"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = ComboBoxAgentTraitant.SelectedValue '???????????
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@NUM_EXPEDITEUR"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = KeyExpediteur
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@OBJET"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxObjet.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@DUREE"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TextBoxDuree.Text
CmdOleDb.Parameters.Add(Param)
Param = New OleDbParameter()
Param.ParameterName = "@TempKEY"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
TempKEY = Form_Main.Key_Utilisateur & " # " & Now
Param.Value = TempKEY
CmdOleDb.Parameters.Add(Param)
CmdOleDb.ExecuteNonQuery()
CmdOleDb.Parameters.Clear()
Param = New OleDbParameter()
Param.ParameterName = "@TempKEY"
Param.Direction = ParameterDirection.Input
Param.OleDbType = OleDbType.VarChar
Param.Value = TempKEY
CmdOleDb.Parameters.Add(Param)
KeyDossier = CmdOleDb.ExecuteScalar
If KeyDossier = 0 Then
MsgBox("Erreur lors de la création du dossier, veuillez réessayer ou contacter l'administrateur si le problème persiste", MsgBoxStyle.Critical, "DB error")
Exit Sub
End If
Else
KeyDossier = TextBoxNumDossier.Text
End If
' 4- Create courrier with foreign keys
' 5- Commit transaction
OleDbTrans.Commit()
Catch exc As Exception
OleDbTrans.Rollback()
MsgBox(exc.Message, MsgBoxStyle.Critical, "Error DB")
'MsgBox("Erreur lors de la creation de l'enregistrement, veuillez réessayer. |Si le problème persiste, contactez l'administrateur", MsgBoxStyle.Critical, "DB Error")
Finally
cnPolitis.Close()
End Try
Me.Close()