none
(Visual Studio 2008) Utiliser les transactions SQL SERVER RRS feed

  • Question

  • Bonjour,

    J'ai beau chercher sur le NET, et consulter les documentations, je n'arrive pas à comprendre, donc à corriger cette erreur

    Elle se produit dès que je clique sur "butAjout" ou "butModif" ou "butSup"

    Pour Ajout ,  c'est dans la procédure "VerifSaisie", sur l'ordre "SqlDR = SqlCmd.ExecuteReader"

    Pour Modif ou Sup c'est dans la procédure "SqlMaJ", sur l'ordre "SqlCmd.ExecuteNonQuery()"

    Merci pour votre aide

    Voici le code source

    Imports System.Data.SqlClient
    Public Class frmClient
        '   Code trouvé sur http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqltransaction(v=vs.80).aspx
        ' Start a local transaction.
        Dim sqlTran As SqlTransaction
        Private Sub frmClient_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
            If wOk = False Then
                '   Restauration de la transaction en cas d'erreur
                '   Code trouvé sur http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqltransaction(v=vs.80).aspx
                sqlTran.Rollback()
            Else
                sqlTran.Commit()
            End If
        End Sub
        Private Sub frmClient_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Call Liberation()
            Call afficher_grid("select * from tabClient", Me.dgwCli)
            '   Pour gérer les transactions
            '   Code trouvé sur http://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqltransaction(v=vs.80).aspx
            ' Start a local transaction Début de la transaction
            sqlTran = SqlConx.BeginTransaction()
            ' Enlist the command in the current transaction.    Association de la commande avec la transaction
            SqlCmd = SqlConx.CreateCommand()
            SqlCmd.Transaction = sqlTran
        End Sub
        Private Sub butAjout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butAjout.Click
            Try
                Call VerifSaisie(True)
                If wOk = False Then Exit Sub
                Call SqlMaJ("insert into tabClient (NomCli, NumCli) values ('" & Me.txtNom.Text & "' , " & Me.txtNumCli.Text & ")")
                Call Clear()
                Call afficher_grid("select * from tabClient", Me.dgwCli)
            Catch ex As Exception
                MsgBox("butAjout_Click " & ex.Message)
                wOk = False
            End Try
        End Sub
        Private Sub butModif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butModif.Click
            Try
                Call VerifSaisie(False)
                If wOk = False Then Exit Sub
                Call SqlMaJ("update tabClient set NomCli = '" & Me.txtNom.Text & "' where NumCli = " & Me.txtNumCli.Text)
                Call Clear()
                Call afficher_grid("select * from tabClient", Me.dgwCli)
            Catch ex As Exception
                MsgBox("butModif_Click " & ex.Message)
                wOk = False
            End Try
        End Sub
        Private Sub butSup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butSup.Click
            Try
                If Me.txtNumCli.Text <> "" Then
                    Call SqlMaJ("delete from tabClient where NumCli = " & Me.txtNumCli.Text)
                    Call Clear()
                    Call afficher_grid("select * from tabClient", Me.dgwCli)
                End If
            Catch ex As Exception
                MsgBox("butSup_Click " & ex.Message)
                wOk = False
            End Try
        End Sub
        Private Sub VerifSaisie(ByVal wInsert As Boolean)
            Try
                wOk = True
                If ((Val(Me.txtNumCli.Text) < 1) Or (Me.txtNumCli.Text = "")) Then
                    wOk = False
                    MessageBox.Show("Vérifier N°")
                    Exit Sub
                End If
                wOk = True
                If Me.txtNom.Text = "" Then
                    wOk = False
                    MessageBox.Show("Vérifier Nom")
                    Exit Sub
                End If
                If wInsert = True Then
                    sqlRequete = "select * from tabClient where NumCli = " & Val(Me.txtNumCli.Text)
                    SqlCmd = New SqlCommand(sqlRequete, SqlConx)
                    SqlDR = SqlCmd.ExecuteReader
                    Dim schemaTable As DataTable = SqlDR.GetSchemaTable()
                    If SqlDR.HasRows Then
                        MessageBox.Show("Client déja existant")
                        wOk = False
                    End If
                    Call Liberation()
                End If
            Catch ex As Exception
                MsgBox("VerifSaisie " & ex.Message)
                wOk = False
            End Try
        End Sub
        
        Private Sub dgwCli_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgwCli.DoubleClick
            Try
                Dim ligne As Integer = Me.dgwCli.CurrentRow.Index
                Me.txtNumCli.Text = Me.dgwCli(0, ligne).Value.ToString
                Me.txtNom.Text = Me.dgwCli(1, ligne).Value.ToString
            Catch ex As Exception
                MsgBox("dgwCli_DoubleClick " & ex.Message)
                wOk = False
            End Try
        End Sub
        Private Sub Clear()
            Me.txtNumCli.Clear()
            Me.txtNom.Clear()
        End Sub
    End Class

    Imports System.Data.SqlServerCe
    Imports System.Data.SqlClient
    Module wModBaseDonne
        Dim MaBase As String = My.Application.Info.DirectoryPath & "\" & NomBase
        Dim strcon As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & MaBase & ";Integrated Security=True;User Instance=True"
        Public wNbrENRG As Integer  '   Nombre enregistrements ou ligne trouvé
        Public wIndERR As Integer   '   Indice pour faire une boucle sur type des erreurs
        Public sqlRequete As String '   Pour les SELECT
        Public SqlDR As SqlDataReader
        Public SqlConx As New SqlConnection(strcon)
        Public SqlCmd As SqlCommand
        Public wDataSet As New DataSet
        Public wSqlDatAda As SqlDataAdapter
        Public Sub ConnectionBase()
            Try
                If SqlConx.State = ConnectionState.Closed Then
                    SqlConx.Open()
                End If
            Catch ex As SqlException
                For wModBaseDonne.wIndERR = 0 To ex.Errors.Count - 1
                    MsgErreur = "ConnectionBase - Index #" & wModBaseDonne.wIndERR.ToString() & ControlChars.NewLine _
                        & "Message: " & ex.Errors(wModBaseDonne.wIndERR).Message & ControlChars.NewLine _
                        & "LineNumber: " & ex.Errors(wModBaseDonne.wIndERR).LineNumber & ControlChars.NewLine _
                        & "Source: " & ex.Errors(wModBaseDonne.wIndERR).Source & ControlChars.NewLine _
                        & "Procedure: " & ex.Errors(wModBaseDonne.wIndERR).Procedure & ControlChars.NewLine
                    MessageBox.Show(MsgErreur)
                Next wModBaseDonne.wIndERR
            Catch ex As Exception
                MsgBox("ConnectionBase " & ex.Message)
            End Try
        End Sub
        Public Sub SqlMaJ(ByVal queryString As String)
            Try
                SqlCmd = New SqlCommand(queryString, SqlConx)
                SqlCmd.ExecuteNonQuery()
            Catch ex As SqlException
                For wModBaseDonne.wIndERR = 0 To ex.Errors.Count - 1
                    MsgErreur = "SqlMaJ - Index #" & wModBaseDonne.wIndERR.ToString() & ControlChars.NewLine _
                        & "Message: " & ex.Errors(wModBaseDonne.wIndERR).Message & ControlChars.NewLine _
                        & "LineNumber: " & ex.Errors(wModBaseDonne.wIndERR).LineNumber & ControlChars.NewLine _
                        & "Source: " & ex.Errors(wModBaseDonne.wIndERR).Source & ControlChars.NewLine _
                        & "Procedure: " & ex.Errors(wModBaseDonne.wIndERR).Procedure & ControlChars.NewLine
                    MessageBox.Show(MsgErreur)
                Next wModBaseDonne.wIndERR
            Catch ex As Exception
                MsgBox("SqlMaJ " & ex.Message)
            End Try
        End Sub
        Public Sub afficher_grid(ByVal req_selection As String, ByVal grid As DataGridView)
            Dim wDataTable As New DataTable
            Try
                wDataTable.Rows.Clear()
                grid.DataSource = Nothing
                SqlCmd = New SqlCommand(req_selection, SqlConx)
                SqlDR = SqlCmd.ExecuteReader
                wDataTable.Load(SqlDR)
                grid.DataSource = wDataTable
                SqlDR.Close()
            Catch ex As SqlException
                For wModBaseDonne.wIndERR = 0 To ex.Errors.Count - 1
                    MsgErreur = "afficher_grid - Index #" & wModBaseDonne.wIndERR.ToString() & ControlChars.NewLine _
                        & "Message: " & ex.Errors(wModBaseDonne.wIndERR).Message & ControlChars.NewLine _
                        & "LineNumber: " & ex.Errors(wModBaseDonne.wIndERR).LineNumber & ControlChars.NewLine _
                        & "Source: " & ex.Errors(wModBaseDonne.wIndERR).Source & ControlChars.NewLine _
                        & "Procedure: " & ex.Errors(wModBaseDonne.wIndERR).Procedure & ControlChars.NewLine
                    MessageBox.Show(MsgErreur)
                Next wModBaseDonne.wIndERR
            Catch ex As Exception
                MsgBox("afficher_grid " & ex.Message)
            End Try
        End Sub
        Public Sub Liberation()
            Try
                If Not SqlDR Is Nothing Then
                    SqlDR.Close()
                End If
                If Not wDataSet Is Nothing Then
                    wDataSet.Clear()
                End If
            Catch ex As SqlException
                For wModBaseDonne.wIndERR = 0 To ex.Errors.Count - 1
                    MsgErreur = "Liberation - Index #" & wModBaseDonne.wIndERR.ToString() & ControlChars.NewLine _
                        & "Message: " & ex.Errors(wModBaseDonne.wIndERR).Message & ControlChars.NewLine _
                        & "LineNumber: " & ex.Errors(wModBaseDonne.wIndERR).LineNumber & ControlChars.NewLine _
                        & "Source: " & ex.Errors(wModBaseDonne.wIndERR).Source & ControlChars.NewLine _
                        & "Procedure: " & ex.Errors(wModBaseDonne.wIndERR).Procedure & ControlChars.NewLine
                    MessageBox.Show(MsgErreur)
                Next wModBaseDonne.wIndERR
            Catch ex As Exception
                MsgBox("Liberation " & ex.Message)
            End Try
        End Sub
        Public Sub Fermeture()
            Try
                If Not SqlDR Is Nothing Then
                    SqlDR.Close()
                    SqlDR = Nothing
                End If
                If Not SqlCmd Is Nothing Then
                    SqlCmd.Dispose()
                    SqlCmd = Nothing
                End If
                If Not wDataSet Is Nothing Then
                    wDataSet.Dispose()
                    wDataSet = Nothing
                End If
                If Not wSqlDatAda Is Nothing Then
                    wSqlDatAda.Dispose()
                    wSqlDatAda = Nothing
                End If
    
                If Not SqlConx Is Nothing Then
                    If SqlConx.State = ConnectionState.Open Then
                        SqlConx.Close() 'possibilité de vérifier l'état de connexion pour rendre + robuste
                        SqlConx.Dispose()
                        SqlConx = Nothing
                    End If
                End If
                GC.Collect()
                Dim s As GCNotificationStatus = GC.WaitForFullGCApproach
                While s = GCNotificationStatus.Succeeded
                    GC.WaitForFullGCComplete()
                End While
                '''Dim engine As New SqlCeEngine("Data Source = " & MaBase)
                '''If False = engine.Verify() Then
                '''    MessageBox.Show("Database is corrupted.")
                '''    engine.Repair(Nothing, RepairOption.RecoverCorruptedRows)
                '''End If
                '''engine.Compact(Nothing)
            Catch ex As SqlException
                For wModBaseDonne.wIndERR = 0 To ex.Errors.Count - 1
                    MsgErreur = "Fermeture - Index #" & wModBaseDonne.wIndERR.ToString() & ControlChars.NewLine _
                        & "Message: " & ex.Errors(wModBaseDonne.wIndERR).Message & ControlChars.NewLine _
                        & "LineNumber: " & ex.Errors(wModBaseDonne.wIndERR).LineNumber & ControlChars.NewLine _
                        & "Source: " & ex.Errors(wModBaseDonne.wIndERR).Source & ControlChars.NewLine _
                        & "Procedure: " & ex.Errors(wModBaseDonne.wIndERR).Procedure & ControlChars.NewLine
                    MessageBox.Show(MsgErreur)
                Next wModBaseDonne.wIndERR
            Catch ex As Exception
                MsgBox("Fermeture " & ex.Message)
            End Try
        End Sub
    End Module


    Cordialement Sauveur CONSALVI

    vendredi 16 novembre 2012 09:41

Réponses

Toutes les réponses