DB not unlocking?

[Brandon Lepley]

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]]

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" <bj******@napuda.com> wrote in message
news:2a**************************@posting.google.c om...

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]

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]" <te*****@dnartreb.noraa> wrote in message news:<uG**************@TK2MSFTNGP09.phx.gbl>...

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" <bj******@napuda.com> wrote in message
news:2a**************************@posting.google.c om...

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