Transactions with ASP and MS Access

Discussion in 'ASP General' started by deadfish, Nov 12, 2007.

  1. deadfish

    deadfish Guest

    Hi,

    I have a ASP application with MS Access. I need to use transaction as there
    are several insert and delete statement.

    oConn.beginTrans

    commUpdate.CommandText = "delete from tableA ..."
    set rs = commUpdate.Execute()

    commUpdate.CommandText = "insert into tableA ....." set rs =
    commUpdate.Execute()

    if Err.Number <> 0 then
    oConn.RollBackTrans
    oConn.close
    else
    oConn.CommitTrans
    oConn.close
    set oConn =nothing

    end if

    Is it a correct way to use transaction? Would the server hang if there are
    around 10 (or less) concurrent users. Would the whole database being locked
    while a user is performing transactioin?


    Thanks in advance!
     
    deadfish, Nov 12, 2007
    #1
    1. Advertising

  2. deadfish

    Mike Brind Guest

    "deadfish" <> wrote in message
    news:...
    > Hi,
    >
    > I have a ASP application with MS Access. I need to use transaction as
    > there
    > are several insert and delete statement.
    >
    > oConn.beginTrans
    >
    > commUpdate.CommandText = "delete from tableA ..."
    > set rs = commUpdate.Execute()
    >
    > commUpdate.CommandText = "insert into tableA ....." set rs =
    > commUpdate.Execute()
    >
    > if Err.Number <> 0 then
    > oConn.RollBackTrans
    > oConn.close
    > else
    > oConn.CommitTrans
    > oConn.close
    > set oConn =nothing
    >
    > end if
    >
    > Is it a correct way to use transaction? Would the server hang if there are
    > around 10 (or less) concurrent users. Would the whole database being
    > locked
    > while a user is performing transactioin?
    >
    >
    > Thanks in advance!
    >


    The Jet Oledb provider doesn't support transactions.

    --
    Mike Brind
     
    Mike Brind, Nov 12, 2007
    #2
    1. Advertising

  3. Mike Brind wrote:
    >
    > The Jet Oledb provider doesn't support transactions.
    >


    That's news to me Mike. Got a cite?

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Nov 12, 2007
    #3
  4. deadfish wrote:
    > Hi,
    >
    > I have a ASP application with MS Access. I need to use transaction as
    > there are several insert and delete statement.
    >
    > oConn.beginTrans
    >
    > commUpdate.CommandText = "delete from tableA ..."
    > set rs = commUpdate.Execute()


    no,no,no - this query does not return records. There is no need for a
    recordset object. Do this instead to make sure ADO doesn't create an
    unnecessary recordset object:

    commUpdate.Execute ,,129
    >
    > commUpdate.CommandText = "insert into tableA ....."
    > set rs =commUpdate.Execute()
    >


    See above

    > if Err.Number <> 0 then
    > oConn.RollBackTrans
    > oConn.close
    > else
    > oConn.CommitTrans
    > oConn.close
    > set oConn =nothing
    >
    > end if
    >
    > Is it a correct way to use transaction?


    Outside of the mistake of using a recordset object to run queries that
    don't retrieve records, this seems reasonable. Typically, you would
    check for errors after each statement.
    See tip 9 on this page:
    http://www.windowsdevcenter.com/pub/a/oreilly/windows/news/ado_0601.html?page=last

    PS. Most of the tips on that page are applicable to VB/VBA, not vbscript
    being used in ASP.

    > Would the server hang if
    > there are around 10 (or less) concurrent users.


    I don't know. You'll have to test it in your environment.

    > Would the whole
    > database being locked while a user is performing transactioin?
    >

    Possibly, but probably not. Again, testing will answer this question.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Nov 12, 2007
    #4
  5. deadfish

    Mike Brind Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Mike Brind wrote:
    >>
    >> The Jet Oledb provider doesn't support transactions.
    >>

    >
    > That's news to me Mike. Got a cite?
    >


    I stand corrected.

    Mike
     
    Mike Brind, Nov 12, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mythran

    ADO.Net, Transactions, and ASP.Net

    Mythran, Apr 11, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    2,926
    Mythran
    Apr 12, 2005
  2. Neven Klofutar

    Access DB & transactions

    Neven Klofutar, May 27, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    415
    Neven Klofutar
    May 27, 2005
  3. =?Utf-8?B?U3JpZGhhcg==?=

    implementing sql transactions in data access layers

    =?Utf-8?B?U3JpZGhhcg==?=, Oct 10, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    556
    =?Utf-8?B?U3JpZGhhcg==?=
    Oct 11, 2006
  4. Shaun Stuart
    Replies:
    6
    Views:
    270
    Shaun Stuart
    Sep 27, 2004
  5. Anders

    ASP 3 transactions and SSI

    Anders, Sep 4, 2006, in forum: ASP General
    Replies:
    3
    Views:
    142
    Mike Brind
    Sep 6, 2006
Loading...

Share This Page