How-to handlie DB "errors" question

[Gary Richtmeyer]

I've been doing web/database programming for many years using a
mainframe-based server and mainframe-type languages. Now moving to an
environment using ASP, IIS and MS Access on Win XP Pro. After reading a ton
of posts in this newsgroup and browsing the many ASP-related web sites, I
have a couple of should-be-simple questions as I port my mainframe apps to
ASP and ADO/MSAccess..

1. Retrieving a record with intent to update (e.g. an update form) and
"lock" it so somebody else won't try to update the same record
simultaneously. It appears that locking done using the appropriate "cursor
type", true? Also, if the user then decides to not do an update, do I have
to issue some sort of DB call to remove the lock?

2. Determining if a SELECT failed and why. After I do a SELECT, I believe I
immediately check for EOF to see if I got any data back. If it's false, then
I've got data, right? But if it's true and there's no data, how do I
determine *why* there's no data? Such as no data satisfied the SELECT or the
specified record is locked by another user with intent to update.

3. Similar to #2, if I want to simply delete a record, I know I can do a
straight SQL DELETE. But if the record has been deleted already by somebody
else, is there a way to prevent the ugly error screen that IIS generates and
then trap and decode the error condition so I can provide a user-friendly
error message?

That should do it for the moment. Thanks.

-- Gary

[Bob Barrows]

Gary Richtmeyer wrote:

> I've been doing web/database programming for many years using a
> mainframe-based server and mainframe-type languages. Now moving to an
> environment using ASP, IIS and MS Access on Win XP Pro. After
> reading a ton of posts in this newsgroup and browsing the many
> ASP-related web sites, I have a couple of should-be-simple questions
> as I port my mainframe apps to ASP and ADO/MSAccess..

You're porting from a mainframe to a desktop database??? Egads! You may wish
to reconsider this. Check out this article:
[url]http://www.aspfaq.com/show.asp?id=2195[/url]

>
> 1. Retrieving a record with intent to update (e.g. an update form) and
> "lock" it so somebody else won't try to update the same record
> simultaneously. It appears that locking done using the appropriate
> "cursor type", true? Also, if the user then decides to not do an
> update, do I have to issue some sort of DB call to remove the lock?

My advice is not to try to lock specific records, especially when using ASP.
This requires the use of cursor-based updates, which are the most expensive
type of updates to use, and will severely impact the scalabiltiy of your
application. In addition, locking a record and waiting for a user to make
inputs violates the principle of keeping transactions as short as possible.
You don't want a record to be locked while a user goes on his lunch break do
you? In addition, it isn't clear that you can do row-level locking with an
Access database via ADO, given that the Jet engine applies locks at the page
level (a page can contain several rows of data) by default.

Instead, use the default optimistic locking behavior. Use SQL statements for
all updates/inserts/deletes. I covered this issue in depth a while back.
Here is the thread:
[url]http://tinyurl.com/mg9s[/url]

>
> 2. Determining if a SELECT failed and why. After I do a SELECT, I
> believe I immediately check for EOF to see if I got any data back. If
> it's false, then I've got data, right?

Right. A cursor will be pointing to the first record immediately after it is
opened, so if it contains records, EOF will never be true immediately after
being opened. If you need to check whether a recordset contains records
after any navigation or record deletions have occurred, then you need to
check both EOF and BOF.

> But if it's true and there's
> no data, how do I determine *why* there's no data? Such as no data
> satisfied the SELECT or the specified record is locked by another
> user with intent to update.

Again, if you follow my recommendation, there will never be a record with a
write lock, so the only reason will be that no records satisfied your search
criteria. However, an error will be generated if the record is locked, which
can be trapped by use of On Error Resume Next.

>
> 3. Similar to #2, if I want to simply delete a record, I know I can
> do a straight SQL DELETE. But if the record has been deleted already
> by somebody else, is there a way to prevent the ugly error screen
> that IIS generates and then trap and decode the error condition so I
> can provide a user-friendly error message?

You get an error?? A SQL Delete statement should not return an error if it
does not find a record to delete. Let me try it now just to be sure ... nope
no error. This code:

dim cn,lRecs
set cn=server.CreateObject("adodb.connection")

cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & _
server.MapPath("db7.mdb")
'using dynamic sql for the sake of this example - don't use
'dynamic sql in production code - use stored procedures
'(saved parameter queries if Access) instead
lRecs = 0
cn.Execute "delete from tblTable where ID = 3",lRecs,129
Response.Write lRecs & " records were deleted.<br>"
lRecs = 0
cn.Execute "delete from tblTable where ID = 7244",lRecs,129
Response.Write lRecs & " record was deleted.<br>"
cn.close
set cn=nothing

returned this result in the browser window:
0 records were deleted.
1 record was deleted.

You WILL get an error if you attempt to use a cursor (recordset) to attempt
to delete a record that no longer exists )yet another reason to use cursors
to retrieve readonly data for display only).

To trap errors in vbscript, use On Error Resume Next, and test the Err
object for an error number not equal to 0 on the line following the
statement.

HTH,
Bob Barrows

[Bite My Bubbles]

You could run ASP against your mainframe databases. But you have probably
considered that
1. The lock is removed when the page finishes executing, or when the command
finishes. I'm not an expert on locks.
2. IF a SELECT statement fails, ASP or MS Access will give you an error
message.
3. Yes, you can ignore errors like that. But usually you will do "delete
from mytable where IDNO = 1234".
If there is no longer an IDNO with 1234, so be it, there is no error.

Good luck, and you've done good research


"Gary Richtmeyer" <glricht2@imailbox.com> wrote in message
news:uta55bHdDHA.828@TK2MSFTNGP11.phx.gbl...

> I've been doing web/database programming for many years using a
> mainframe-based server and mainframe-type languages. Now moving to an
> environment using ASP, IIS and MS Access on Win XP Pro. After reading a

ton

> of posts in this newsgroup and browsing the many ASP-related web sites, I
> have a couple of should-be-simple questions as I port my mainframe apps to
> ASP and ADO/MSAccess..
>
> 1. Retrieving a record with intent to update (e.g. an update form) and
> "lock" it so somebody else won't try to update the same record
> simultaneously. It appears that locking done using the appropriate

"cursor

> type", true? Also, if the user then decides to not do an update, do I

have

> to issue some sort of DB call to remove the lock?
>
> 2. Determining if a SELECT failed and why. After I do a SELECT, I believe

I

> immediately check for EOF to see if I got any data back. If it's false,

then

> I've got data, right? But if it's true and there's no data, how do I
> determine *why* there's no data? Such as no data satisfied the SELECT or

the

> specified record is locked by another user with intent to update.
>
> 3. Similar to #2, if I want to simply delete a record, I know I can do a
> straight SQL DELETE. But if the record has been deleted already by

somebody

> else, is there a way to prevent the ugly error screen that IIS generates

and

> then trap and decode the error condition so I can provide a user-friendly
> error message?
>
> That should do it for the moment. Thanks.
>
> -- Gary
>
>

[Troy Stark]

you can catch iis errors with the On Error Resume Next
in vbscript it works differentely then vb so there is no next statement (as
you would expect) you just catch the error with an if or case statement.

On Error Resume Next
do some code..like a db
iError = err.Number
If iError <> 0 then
If iError = 1 then
Response.Write "error number is not right, but you didnt have a record
to delete!"
iError = 0 ' erase the error if you were to continue the program with
the error.
Else
Response.Write "Uncaught error. " & err.Description
iError = 0
Else
Continue code...
End If

err is the keyword to get the error information.

this may help?

"Gary Richtmeyer" <glricht2@imailbox.com> wrote in message
news:uta55bHdDHA.828@TK2MSFTNGP11.phx.gbl...

> I've been doing web/database programming for many years using a
> mainframe-based server and mainframe-type languages. Now moving to an
> environment using ASP, IIS and MS Access on Win XP Pro. After reading a

ton

> of posts in this newsgroup and browsing the many ASP-related web sites, I
> have a couple of should-be-simple questions as I port my mainframe apps to
> ASP and ADO/MSAccess..
>
> 1. Retrieving a record with intent to update (e.g. an update form) and
> "lock" it so somebody else won't try to update the same record
> simultaneously. It appears that locking done using the appropriate

"cursor

> type", true? Also, if the user then decides to not do an update, do I

have

> to issue some sort of DB call to remove the lock?
>
> 2. Determining if a SELECT failed and why. After I do a SELECT, I believe

I

> immediately check for EOF to see if I got any data back. If it's false,

then

> I've got data, right? But if it's true and there's no data, how do I
> determine *why* there's no data? Such as no data satisfied the SELECT or

the

> specified record is locked by another user with intent to update.
>
> 3. Similar to #2, if I want to simply delete a record, I know I can do a
> straight SQL DELETE. But if the record has been deleted already by

somebody

> else, is there a way to prevent the ugly error screen that IIS generates

and

> then trap and decode the error condition so I can provide a user-friendly
> error message?
>
> That should do it for the moment. Thanks.
>
> -- Gary
>
>