Transaction problem (open datareader exception)

Question

1.00/5 (1 vote)

See more:

Hi
I copy data from one table to another without transaction, the program works fine and with the transaction displays a message.
Kindly help to achieve this,
Regards
TIA

What I have tried:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
      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

Posted yesterday

HichSkill

Updated yesterday

v7

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2024-10-14T21:43:00

Solution 2

A better solution would be to do this in the database, assuming it's supported, rather than in code.

For example, with SQL Server, you can do this in a single command:

SQL

INSERT INTO Category_2 (ID, Label)
SELECT ID, Label 
FROM Category 
WHERE ID = @CategoryID;

INSERT INTO Product_2 (ID, Label, ID_Cat)
SELECT ID, Label, ID_Cat 
FROM Product 
WHERE ID_Cat = @CategoryID;

Posted yesterday

Richard Deeming

Comments

HichSkill yesterday

I tried this method, but the problem is that they are not the same ID and it is not auto.

Richard Deeming yesterday

I'm sure that means something to you, but I can't make any sense of what you've said.

The query I posted will do exactly the same thing that your code would, if your code worked:

* Select the details of a specific category;
* Insert the same details into Category_2;
* Select the details of all products in the category;
* Insert the same details into Product_2;

If you're starting from non-working code that wouldn't do what you want it to anyway, then you're missing a lot of information from your question!

HichSkill 23hrs ago

For your example, is it possible to put the ID by another local variable? For example
INSERT INTO Product_2 ('1', Label, ID_Cat)
SELECT ID, Label, ID_Cat
FROM Product
WHERE ID_Cat = @CategoryID;

Richard Deeming 23hrs ago

Yes - for example, if you want the second category to have a different ID:

INSERT INTO Category_2 (ID, Label)
SELECT @Category2ID, Label 
FROM Category 
WHERE ID = @CategoryID;

INSERT INTO Product_2 (ID, Label, ID_Cat)
SELECT ID, Label, @Category2ID 
FROM Product 
WHERE ID_Cat = @CategoryID;

Richard Deeming 23hrs ago

If you want the second product(s) to have a different ID, it's slightly more complicated. You'd need to work out how you want to assign the new IDs.

For example (assuming SQL Server):

DECLARE @MaxProductID int;
SELECT @MaxProductID = MAX(ID) FROM Product_2;
If @MaxProductID Is Null SET @MaxProductID = 0;

INSERT INTO Product_2 (ID, Label, ID_Cat)
SELECT @MaxProductID + ROW_NUMBER() OVER (ORDER BY ID), Label, @Category2ID 
FROM Product 
WHERE ID_Cat = @CategoryID;

ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^]

HichSkill 16hrs ago

Please, is it possible to write this query?
In MS access and vb.net

Richard Deeming 3hrs 5mins ago

MS Access supports the INSERT INTO .. SELECT syntax:
INSERT INTO statement (Microsoft Access SQL) | Microsoft Learn[^]

It doesn't support the ROW_NUMBER function. But you may be able to fake it - eg (untested):

INSERT INTO Product_2 (ID, Label, ID_Cat)
SELECT @MaxProductID + (SELECT COUNT(*) FROM Product As P2 WHERE P2.ID_Cat = @CategoryID And P2.ID < P.ID), Label, @Category2ID 
FROM Product As P
WHERE ID_Cat = @CategoryID;

I'm not sure whether it supports multiple statements in a single command; if not, you'd need to issue three separate queries, preferably within a single transaction:

1. Insert the new category;
2. SELECT MAX(ID) FROM Product_2;
3. Insert the new products;

Dave Kreskowiak · Answer 2 · 2024-10-14T18:07:00

Solution 1

The is pretty explicit. You cannot have an open DataReader and execute other queries ON THE SAME CONNECTION at the same time. You either have to use the DataReader to read all of the data into an appropriate data structure, close the DataReader, then process each record you have cached,

OR

You use TWO connection objects. One for the DataReader and one for the queries you're executing against each of the records your getting from the reader.

Posted yesterday

Dave Kreskowiak

Comments

HichSkill yesterday

Thank you for your answer.
My problem is that I want to copy the details and information from one table to another. For example, transfer a quote to a purchase order in the sales management, do you have an example to modify my code? I am an apprentice in programming. Thank you in advance.