locked
2 db connection on 1 asp page RRS feed

  • Question

  • User-1007217883 posted

    Hi all,

    Now I have problem when make a asp page.

    I use 2 db connection connect to 2 db differrent

    And then have some query execute on db1, some query execute on db2.

    Begintrans

    If true -> Commit

    else -> Rollback

    But this case, db1/db2  still insert records. 

    Thanks for all!

    ---------------------------------------

    db1.execute

    db2.execute

    if use - stock <=0 then

    message -> out of stock

    history.back()

    response.end

    end if

    if err.count <> 0 

    db1.commitTrans

    db2.commitTrans

    else

    db1.RollbackTrans

    db2.RollbackTrans

    enf if

    Friday, June 22, 2018 9:06 AM

All replies

  • User121216299 posted

    Hi Nguyen Hien,

    I suggest you to create 2 separate block of codes for each database or try to check and decide in which database you want to execute this query and create an object for that database only and generate an query for it.

    Here, it looks like if there is no any error in execution of first query then it commit the transection in both database.

    cmd.CommandText = "insert into MyTable values(blah, blah, blah)"   
    cmd.CommandType = adCmdText
    On Error Resume Next
    Set rs = cmd.Execute()
    If Err.number<>0 or  objConnection.Errors.Count <> 0 Then
       'Do something to handle the error
        cmd.RollbackTrans
    else
        cmd.CommitTransaction
    End If
    On Error Goto 0

    Reference:

    A Simplified Parameterized Query Class in Classic ASP

    Regards

    Deepak

    Monday, June 25, 2018 5:29 AM
  • User-1007217883 posted

    Hi Deepak,

    Thanks for your response!

    I already make like that, but now my problem is  have to 2 database connnection: DB1 and DB2

    ON ERROR RESUME NEXT
    DB1.BeginTrans
    DB2.BeginTrans
    
    IF BBT_KIND = "Y" THEN
        SQL1 = "INSERT INTO DB1.TB1( A,B,C) VALUES(....)
        DB1.EXECUTE(SQL1)
    END IF
    
    IF STOCK = 0 THEN
    response.write "<script>alert('OUT OF STOCK!!!'); history.back();  </script>"
    RESPONSE.END
    ELSE
        SQL2 = "INSERT INTO DB2.TB1( A,B,C) VALUES(....)
        DB2.EXECUTE(SQL2)
    END IF
    
    IF Err.number <> 0 THEN
    DB1.CommitTrans
    DB2.CommitTrans
    ELSE
    DB1.RollbackTrans
    DB2.RollbackTrans
    END IF
       

    When STOCK not enough -> message -> response.end -> No query execute

    But SQL1 still INSERT on DB1

    SQL2 not yet

    Regards!

    Monday, June 25, 2018 6:59 AM
  • User-1007217883 posted

    Hi Deepak,

    Thanks for your response!

    I already make like that, but now my problem is  have to 2 database connnection: DB1 and DB2

    ON ERROR RESUME NEXT
    DB1.BeginTrans
    DB2.BeginTrans
    
    IF BBT_KIND = "Y" THEN
        SQL1 = "INSERT INTO DB1.TB1( A,B,C) VALUES(....)
        DB1.EXECUTE(SQL1)
    END IF
    
    IF STOCK = 0 THEN
    response.write "<script>alert('OUT OF STOCK!!!'); history.back();  </script>"
    RESPONSE.END
    ELSE
        SQL2 = "INSERT INTO DB2.TB1( A,B,C) VALUES(....)
        DB2.EXECUTE(SQL2)
    END IF
    
    IF Err.number <> 0 THEN
    DB1.CommitTrans
    DB2.CommitTrans
    ELSE
    DB1.RollbackTrans
    DB2.RollbackTrans
    END IF

    When STOCK not enough -> message -> response.end -> No query execute

    But SQL1 still INSERT on DB1

    SQL2 not yet

    Regards!

    Monday, June 25, 2018 7:01 AM
  • User121216299 posted

    Hi Nguyen Hien,

    By creating a separate code block, what I mean is you need to create a whole new code block for other database.

    So first try to begin transection for DB1 -> match condition and execute query ->commit transection for DB1.

    Then try to begin transection for DB2 -> match condition and execute query ->commit transection for DB2.

    I can see that you execute query for both database in single code block.

    Other thing you had mentioned that," When STOCK not enough -> message -> response.end -> No query execute. But SQL1 still INSERT on DB1. SQL2 not yet"

    I can see that you are matching different conditions to fire an insert query in both database.

    First condition:

     

    Second condition:

    If BBT_Kind="Y" then it will execute query on DB1. It will not care that whether second condition get true or not.

    If you want that both condition get true and then you want to fire query then you can use 'AND' operator and check both conditions together.

    So I suggest you to refer articles for using IF conditions with operators to properly match the conditions for both the database will help you to solve this issue.

    It just a logic issue. Once you figure it out then it will start work correctly.

    Regards

    Deepak

    Tuesday, June 26, 2018 7:26 AM
  • User-1007217883 posted

    Hi Deepak,

    I think on the block 2 that wrong condition -> have to "history.back();" and "response.end"

    So I think not yet running to "CommitTrans"

    => SQL1 not execute, too

    Is it right?

    Tuesday, June 26, 2018 9:40 AM
  • User121216299 posted

    Hi Nguyen Hien,

    You had mentioned that,

    "I think on the block 2 that wrong condition -> have to "history.back();" and "response.end". So I think not yet running to "CommitTrans". => SQL1 not execute, too. Is it right?"

    I hope it can give you a clear idea.

    You can try to debug the code on your side to check the flow of the code.

    Let us know whether it helps you to solve the issue or not.

    We will try to provide further suggestions, If needed.

    Regards

    Deepak

    Wednesday, June 27, 2018 7:59 AM