Bonjour, je souhaite insérer des données d'une table à une autre. En utilisant Transaction, j'obtiens un message d'erreur :
ExecuteReader implique que la commande possède une transaction lorsque la connexion affectée à la commande est en attente d'une transaction locale. La propriété Transaction de la commande n'a pas été initialisée.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
  Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click        
        oleCon.Close()
        oleCon.Open()
        Dim strCategory As String = " SELECT Category.ID, Category.Label FROM Category WHERE Category.ID = 1 "
        Dim olecmd As New OleDbCommand(strCategory, oleCon)
        Dim trans As OleDbTransaction = oleCon.BeginTransaction
        Dim dreaderCategory As OleDbDataReader = olecmd.ExecuteReader
        Dim intID As Integer
        Dim strLabel As String = String.Empty
        While dreaderCategory.Read
            intID = dreaderCategory("ID")
            strLabel = dreaderCategory("Label")
        End While
        dreaderCategory.Close()
        Dim strCategory_2 As String = "INSERT INTO Category_2 (ID,Label) VALUES (@ID,@Label)"
        Dim olecomCategory_2 As New OleDbCommand(strCategory_2, oleCon)
        olecomCategory_2.Parameters.AddWithValue("@ID", intID)
        olecomCategory_2.Parameters.AddWithValue("@Label", strLabel)
        olecmd.Transaction = trans
        olecomCategory_2.ExecuteNonQuery()
        oleCon.Close()
 
 
        Dim strProduct As String = "SELECT Product.ID, Product.Label, Product.ID_Cat FROM Category, Product WHERE Product.ID_Cat = 1 "
        Dim intIDProduct As Integer
        Dim strLabelProduct As String
        Dim intID_CatProduct As Integer
        Dim olecmdProduct As New OleDbCommand(strProduct, oleCon)
        oleCon.Open()
        Dim dreaderProduct As OleDbDataReader = olecmdProduct.ExecuteReader
        While dreaderProduct.Read
            intIDProduct = dreaderProduct("ID")
            strLabelProduct = dreaderProduct("Label")
            intID_CatProduct = dreaderProduct("ID_Cat")
            Dim strProduct_2 As String = "INSERT INTO Product_2 (ID,Label,ID_Cat) VALUES (@ID,@Label,@ID_Cat)"
            Dim olecomProduct_2 As New OleDbCommand(strProduct_2, oleCon)
            olecomProduct_2.Parameters.AddWithValue("@ID", intIDProduct)
            olecomProduct_2.Parameters.AddWithValue("@Label", strLabelProduct)
            olecomProduct_2.Parameters.AddWithValue("@ID_Cat", intID_CatProduct)
            'olecomDetailCmd.Transaction = tran
            olecmd.Transaction = trans
            olecomProduct_2.ExecuteNonQuery()
        End While
        trans.Commit()
    End Sub