none
ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. RRS feed

  • Question

  • Respected sir

    i am software engineer. developing vb.net 2010 applications in sql server 2008 r2 database

    i want search some records between Begin and commit transaction. i.e search records before commit the transactions

     i did in many ways for this but the following error ( " ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized. ")

    if anyone knows please send me or answer this question. i am waiting for yours positive reply. its very urgent for me

    Thanks Lot

    Lemudhas.A

     

    • Moved by Min ZhuModerator Wednesday, October 26, 2011 8:10 AM (From:.NET Base Class Library)
    Sunday, October 23, 2011 4:27 PM

Answers

  • Alternatively, create a second connection and query the database in parallel. Depending on your transaction settings, this either works, or complete blocks. So be careful.
    My blog: http://blog.jessehouwing.nl/
    • Proposed as answer by cheong00 Monday, October 24, 2011 2:31 AM
    • Marked as answer by Alan_chenModerator Tuesday, November 1, 2011 8:52 AM
    Sunday, October 23, 2011 9:31 PM

All replies

    1. Call the BeginTransaction method of the SqlConnection object to mark the start of the transaction. The BeginTransaction method returns a reference to the transaction. This reference is assigned to the SqlCommand objects that are enlisted in the transaction.

    2. Assign the Transaction object to the Transaction property of the SqlCommand to be executed. If a command is executed on a connection with an active transaction, and the Transaction object has not been assigned to the Transaction property of the Command object, an exception is thrown.

    3. Execute the required commands.

    4. Call the Commit method of the SqlTransaction object to complete the transaction


    Lingaraj Mishra
    Sunday, October 23, 2011 6:21 PM
  • Alternatively, create a second connection and query the database in parallel. Depending on your transaction settings, this either works, or complete blocks. So be careful.
    My blog: http://blog.jessehouwing.nl/
    • Proposed as answer by cheong00 Monday, October 24, 2011 2:31 AM
    • Marked as answer by Alan_chenModerator Tuesday, November 1, 2011 8:52 AM
    Sunday, October 23, 2011 9:31 PM
  • Thank you very much for your kind reply Lingaraj Mishra.

    Can u explain via vb.net sourcecode????

    i am waiting for your kind reply.

     

    Lemudhas.A


    Monday, October 24, 2011 12:45 AM
  • I think this is the correct response.

    Lemudhas should be in the state of having 2 data object connected to same connection. The first command is modifying data with transaction but doesn't commit, then he want to reuse the same connection to read data without either 1) commit the transaction first; or 2) attach the same transaction to the SqlCommand to indicate you want the data be in the same view.

    (2) have better chance to suit what Lemudhas want though.

    Monday, October 24, 2011 2:37 AM
  • CodeSnipet

     Dim sqlcon As New SqlConnection("Data Source=localhost;Initial Catalog=test;Integrated Security =SSPI;")
            sqlcon.Open()
            Dim sqlTran As SqlTransaction
            sqlTran = sqlcon.BeginTransaction()
            Dim sqlCmd As New SqlCommand
            sqlCmd.Connection = sqlcon
            sqlCmd.CommandText = "Update Sample Set [Name]= 'XYZ'"
            sqlCmd.Transaction = sqlTran
            Dim result As Int32
            result = sqlCmd.ExecuteNonQuery()

            Dim sqlCmd2 As New SqlCommand
            sqlCmd2.Connection = sqlcon
            sqlCmd2.CommandText = "select * from Sample where [Name] ='xyz'"
            sqlCmd2.Transaction = sqlTran
            Dim dr As SqlDataReader
            dr = sqlCmd2.ExecuteReader
            While dr.Read
                MessageBox.Show(dr.Item(0))
            End While


            sqlTran.Commit()
            sqlcon.Close()

     


    Lingaraj Mishra

     


    Monday, October 24, 2011 7:29 AM
  • I suspect if you create a new Connection object and use it, you don't need a Transaction for select action.

    Anyway, your select statement may need ReadUncommitted table hint as there're clear evidence another transaction is running. Such table hint can reduce the chance of being a deadlock victim.

    Monday, October 24, 2011 7:41 AM
  • Hi Lemudhas.A,

    I have moved this thread to ADO.NET forum where you can get better support.

    Best regards,


    Min Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 26, 2011 8:11 AM
    Moderator