Transaction problem (open datareader exception)

[HichSkill]

Hi, I want to insert data from one table to another. Using Transaction, I get an error message:

ExecuteReader implies that the command has a transaction when the connection assigned to the command is in a pending local transaction. The command's Transaction property has not been initialized.

  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

[jmcilhinney]

The error message literally tells you what the problem is:

The command's Transaction property has not been initialized.

Here is the relevant parts of your code:

Dim olecmd As New OleDbCommand(strCategory, oleCon)
Dim trans As OleDbTransaction = oleCon.BeginTransaction
Dim dreaderCategory As OleDbDataReader = olecmd.ExecuteReader

'...

olecmd.Transaction = trans

What's the point of initialising that Transaction property after you have already executed the command? You need to set the Transaction property BEFORE you call ExecuteReader.

BTW, you should always use Using statements where you can to close/dispose objects that support it. That includes data readers. Use them like this:

Using myDataReader = myCommand.ExecuteReader()
    'Use myDataReader here.
End Using

The data reader will be implicitly closed at the end of the Using block. Connections should be treated similarly:

Using myConnection As New OleDbConnection(connectionString)
    myConnection.Open()

    '...
End Using

[HichSkill]

Thank you for the answer. I am a beginner in programming. How can I fix the program?

[jmcilhinney]

I am a beginner in programming.

But not a beginner in reading, one would expect.

How can I fix the program?

I literally just told you:

You need to set the Transaction property BEFORE you call ExecuteReader.

If you ask for help and then don't bother reading it when it's provided, people will soon tire of providing it.

[HichSkill]

Please? Do you know of any online courses on transactions?

[Shaggy Hiker]

Not on transactions. That's too narrow a topic for there to be online courses. You'd need to look for an online course on working with databases, of which transactions would be a sub-topic. While vitally important, transactions are largely something that happens in the background. You start the transaction, do some set of things, then either commit or rollback the transaction. What happens behind the scenes could be quite complicated, but there isn't much to be said about it in a course.

This might be a good one, as the site generally does a good job:

https://www.homeandlearn.co.uk/NET/nets12p4.html