DB not unlocking?

Discussion in 'ASP General' started by Brandon Lepley, Sep 9, 2004.

  1. I have a simple asp page that opens an mdb, reads it, updates 1
    record, closes it. Here's the code:

    Set Conn = Server.CreateObject("ADODB.Connection")
    set rs = Server.CreateObject("ADODB.Recordset")
    Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
    serverr.MapPath ("LAUSDNutriCounter.mdb")
    Rs.CursorType = 2
    Rs.LockType = 3

    strSQL = "SELECT * FROM NutriCounter"
    Rs.Open strsql, Conn
    unlockednum = rs.fields("Unlocked")
    rs.fields("Unlocked") = unlockednum - 1
    rs.update
    rs.close
    Conn.Close
    set rs = nothing
    Set Conn = Nothing

    This page works fine the first time its ran. But if you run it within
    60 seconds again it comes up with the error "An error occurred on the
    server when processing the URL". I've tracked down where it errors
    and it is the line "Conn.Open ...". However, if i wait 60 seconds or
    more between running the page it works.

    Is there some kind of lock on the db that is not letting go for 60
    seconds?

    Please help and thanks in advance,

    Brandon
     
    Brandon Lepley, Sep 9, 2004
    #1
    1. Advertising

  2. Have you tried a much simpler approach?

    set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    Server.MapPath("LAUSDNutriCounter.mdb")
    ' are you sure you don't want a WHERE clause here?
    ' just update the whole table? why?
    sql = "UPDATE NutriCounter SET Unlocked = Unlocked - 1"
    conn.execute sql, , 129
    conn.close
    set conn = nothing

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Brandon Lepley" <> wrote in message
    news:...
    > I have a simple asp page that opens an mdb, reads it, updates 1
    > record, closes it. Here's the code:
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > set rs = Server.CreateObject("ADODB.Recordset")
    > Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
    > serverr.MapPath ("LAUSDNutriCounter.mdb")
    > Rs.CursorType = 2
    > Rs.LockType = 3
    >
    > strSQL = "SELECT * FROM NutriCounter"
    > Rs.Open strsql, Conn
    > unlockednum = rs.fields("Unlocked")
    > rs.fields("Unlocked") = unlockednum - 1
    > rs.update
    > rs.close
    > Conn.Close
    > set rs = nothing
    > Set Conn = Nothing
    >
    > This page works fine the first time its ran. But if you run it within
    > 60 seconds again it comes up with the error "An error occurred on the
    > server when processing the URL". I've tracked down where it errors
    > and it is the line "Conn.Open ...". However, if i wait 60 seconds or
    > more between running the page it works.
    >
    > Is there some kind of lock on the db that is not letting go for 60
    > seconds?
    >
    > Please help and thanks in advance,
    >
    > Brandon
     
    Aaron [SQL Server MVP], Sep 9, 2004
    #2
    1. Advertising

  3. That worked beautifully except 1 thing. I need to set the variable
    "unlockednum" = dbfield "unlocked" so I can make sure it is greater
    than 0. Can you help me out with that? Do I need to use a RS object
    or can that be done with the Conn object?

    Thanks!!!!

    Brandon

    "Aaron [SQL Server MVP]" <> wrote in message news:<>...
    > Have you tried a much simpler approach?
    >
    > set conn = CreateObject("ADODB.Connection")
    > conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    > Server.MapPath("LAUSDNutriCounter.mdb")
    > ' are you sure you don't want a WHERE clause here?
    > ' just update the whole table? why?
    > sql = "UPDATE NutriCounter SET Unlocked = Unlocked - 1"
    > conn.execute sql, , 129
    > conn.close
    > set conn = nothing
    >
    > --
    > http://www.aspfaq.com/
    > (Reverse address to reply.)
    >
    >
    >
    >
    > "Brandon Lepley" <> wrote in message
    > news:...
    > > I have a simple asp page that opens an mdb, reads it, updates 1
    > > record, closes it. Here's the code:
    > >
    > > Set Conn = Server.CreateObject("ADODB.Connection")
    > > set rs = Server.CreateObject("ADODB.Recordset")
    > > Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
    > > serverr.MapPath ("LAUSDNutriCounter.mdb")
    > > Rs.CursorType = 2
    > > Rs.LockType = 3
    > >
    > > strSQL = "SELECT * FROM NutriCounter"
    > > Rs.Open strsql, Conn
    > > unlockednum = rs.fields("Unlocked")
    > > rs.fields("Unlocked") = unlockednum - 1
    > > rs.update
    > > rs.close
    > > Conn.Close
    > > set rs = nothing
    > > Set Conn = Nothing
    > >
    > > This page works fine the first time its ran. But if you run it within
    > > 60 seconds again it comes up with the error "An error occurred on the
    > > server when processing the URL". I've tracked down where it errors
    > > and it is the line "Conn.Open ...". However, if i wait 60 seconds or
    > > more between running the page it works.
    > >
    > > Is there some kind of lock on the db that is not letting go for 60
    > > seconds?
    > >
    > > Please help and thanks in advance,
    > >
    > > Brandon
     
    Brandon Lepley, Sep 10, 2004
    #3
    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. =?Utf-8?B?U3VuZGFyYXJhamFu?=

    Unlocking db objects in Session end

    =?Utf-8?B?U3VuZGFyYXJhamFu?=, Jul 22, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    367
    Andy Fish
    Jul 22, 2005
  2. schwerdy
    Replies:
    1
    Views:
    370
    Terry Reedy
    Nov 10, 2004
  3. NaeiKinDus
    Replies:
    1
    Views:
    595
    Jack Klein
    Apr 14, 2007
  4. NaeiKinDus
    Replies:
    3
    Views:
    625
    James Kanze
    Apr 15, 2007
  5. Justin Dutoit

    Manual Unlocking Users?

    Justin Dutoit, Apr 4, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    395
    Cowboy \(Gregory A. Beamer\)
    Apr 4, 2008
Loading...

Share This Page