Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Unanswered: SQLServer2000 vs ADO VB6

    Hi, I have a little question regarding
    SQLServer2000 vs ADO of Visual Basic 6

    In a code snipplet a have something like:


    Set rec_tmp = Conn1.Execute("SELECT * FROM IDENTIFICATIONS;")

    Conn1.Execute "DELETE FROM CARS WHERE CAR_ID = 8;"

    and whe VB tries to execute the line with the
    DELETE sentence the Err object says something like:
    "cannot create a new connection because you are in
    distributed or manual transaction mode"

    However, if I remove the line that executes the sentence
    SELECT * FROM IDENTIFICATIONS the error never shows up.
    Seems like I cant execute a SELECT before a DELETE/UPDATE
    even if I execute the SELECT in some other table.
    Ive tried changing the IsolationLevel, Mode and Cursor
    Location properties and the error always occurs.
    Furthermore, I tried putting the SELECT out of the
    transaction and the error continues showing up.

    Why does this occurs? Is there a way to solve it?

    NOTE: I need forcely to execute that SELECT before the DELETE.

    Thanks for the time

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    The problem is that the command object that contains the SELECT statement is still active when you attempt to re-use its connection object for the DELETE. If you change your ADO cursortype to ADStatic, you should be fine.


  3. #3
    Join Date
    Jun 2004
    Hi, I solved the problem, but other problem came up.

    I have a global variable called rec_1 as New ADODB.Recordset

    Before the transaction rec_1 has some information,

    The problem is: after the execution of Conn1.CommitTrans
    I lose the information in rec_1, because the error is as

    A call has made to ITransaction::Commit or ITransaction::Abort
    and the object is unstable

    Does this means that after any call to commit transaction I
    will lose the information on all recordsets including
    global ones?

    Do I need to change the Cursor Type or other similar property
    to solve this?

    Really thanks for the time,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts