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.CreateOb ject("ADODB.Con nection")
set rs = Server.CreateOb ject("ADODB.Rec ordset")
Conn.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" &
serverr.MapPath ("LAUSDNutriCou nter.mdb")
Rs.CursorType = 2
Rs.LockType = 3

strSQL = "SELECT * FROM NutriCounter"
Rs.Open strsql, Conn
unlockednum = rs.fields("Unlo cked")
rs.fields("Unlo cked") = 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]]

Re: DB not unlocking?

Have you tried a much simpler approach?

set conn = CreateObject("A DODB.Connection ")
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
Server.MapPath( "LAUSDNutriCoun ter.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" <bjlepley@napud a.com> wrote in message
news:2a878637.0 409090905.65ddd 4b5@posting.goo gle.com...[color=blue]
> I have a simple asp page that opens an mdb, reads it, updates 1
> record, closes it. Here's the code:
>
> Set Conn = Server.CreateOb ject("ADODB.Con nection")
> set rs = Server.CreateOb ject("ADODB.Rec ordset")
> Conn.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" &
> serverr.MapPath ("LAUSDNutriCou nter.mdb")
> Rs.CursorType = 2
> Rs.LockType = 3
>
> strSQL = "SELECT * FROM NutriCounter"
> Rs.Open strsql, Conn
> unlockednum = rs.fields("Unlo cked")
> rs.fields("Unlo cked") = 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[/color]

[Brandon Lepley]

Re: DB not unlocking?

That worked beautifully except 1 thing. I need to set the variable
"unlockednu m" = 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]" <ten.xoc@dnartr eb.noraa> wrote in message news:<uGs6IGplE HA.1904@TK2MSFT NGP09.phx.gbl>. ..[color=blue]
> Have you tried a much simpler approach?
>
> set conn = CreateObject("A DODB.Connection ")
> conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
> Server.MapPath( "LAUSDNutriCoun ter.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" <bjlepley@napud a.com> wrote in message
> news:2a878637.0 409090905.65ddd 4b5@posting.goo gle.com...[color=green]
> > I have a simple asp page that opens an mdb, reads it, updates 1
> > record, closes it. Here's the code:
> >
> > Set Conn = Server.CreateOb ject("ADODB.Con nection")
> > set rs = Server.CreateOb ject("ADODB.Rec ordset")
> > Conn.Open "DRIVER={Micros oft Access Driver (*.mdb)}; DBQ=" &
> > serverr.MapPath ("LAUSDNutriCou nter.mdb")
> > Rs.CursorType = 2
> > Rs.LockType = 3
> >
> > strSQL = "SELECT * FROM NutriCounter"
> > Rs.Open strsql, Conn
> > unlockednum = rs.fields("Unlo cked")
> > rs.fields("Unlo cked") = 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[/color][/color]