Deleting Records

[crjunk]

I was asked to update an ASP web page that is executed by an ASP.NET
aspx web form. I have no control over modifying the ASPX page, so I
cannot see any meaningful error message from my ASP page that could
help determin what is going on.

The following code will delete any existing records from a Progress
Database and then add the records back to the Progress Database from
an XML file.

My code successfully deletes the records, but as soon as it deletes the
records, it crashes. I'm not sure what I'm doing wrong. Can anyone
help guide me in the right directions?

Thanks,
crjunk

<%@ Language=VBScript %>
<%
Response.Expires = -1000
Response.ContentType = "text/xml"
dim xRequestDOM, iLoadErr, iParseErr
set xRequestDom = Server.CreateObject("MSXML.FreeThreadedDomDocument")
iLoadErr = xRequestDom.load(Request)
iParseErr = xRequestDom.parseError.errorCode


if iLoadErr <> 0 and iParseErr = 0 then
'**** Constants copied
Const
CONNECTIONSTRING="DSN=SHREDTEST;uid=sysprogress;pwd=sysprogress"
Const adOpenKeyset = 1
Const adLockPessimistic = 2
Const adCmdText = &H0001


sSQL = "Delete from pub.companies where agencyid = " & sAgencyCode

Set oRecordset = Server.CreateObject("ADODB.Recordset")

''THE CODE CRASHES AFTER IT RUNS THE UPCOMING oRecordset.OPEN
STATEMENT
oRecordset.Open sSQL, CONNECTIONSTRING, adOpenKeyset,
adLockPessimistic, adCmdText
''THE CODE HAS NOW DELETED THE RECORD, BUT HAS CRASHED.

oRecordSet.Update

oRecordset.Close
Set oRecordset = Nothing

''THE REST OF THE CODE WAS OMITTED FROM THIS NEWSGROUP POST. THE
OMITTED CODE GOES ON TO ADD THE RECORDS FROM THE XML FILE BACK TO THE
TABLE THAT THEY WERE JUST DELETED FROM.
end if
%>

 

[Mark Schupp]

put an error trap in (on error resume next, then check for err.number <> 0
after each operation) and write the error message to a log file so that you
can see what is happening.

 

[crjunk]

I added an on error resum next statement with error trapping that
writes the errors to a text file. When I did this the code worked
correctly. I then took the code out and the program crashed again.

I played around with the code a little bit and added the following:

Set oRecordset = Server.CreateObject("ADODB.Recordset")
oRecordset.Open sSQL, CONNECTIONSTRING, adOpenKeyset,
adLockPessimistic, adCmdText
On Error Resume Next
oRecordSet.Update

As long as I keep the On Error Resume Next, it works correctly. If I
take it out, it will crash the program. Any thoughts or suggestions?

 

[Bob Barrows [MVP]]

I added an on error resum next statement with error trapping that
writes the errors to a text file. When I did this the code worked
correctly. I then took the code out and the program crashed again.

I played around with the code a little bit and added the following:

Set oRecordset = Server.CreateObject("ADODB.Recordset")
oRecordset.Open sSQL, CONNECTIONSTRING, adOpenKeyset,
adLockPessimistic, adCmdText
On Error Resume Next
oRecordSet.Update

As long as I keep the On Error Resume Next, it works correctly. If I
take it out, it will crash the program. Any thoughts or suggestions?

Let's see the code for logging the errors to the log file

 

[crjunk]

suggestions?

Let's see the code for logging the errors to the log file


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

Here is what I have added. With this code in the web page, the program
works correctly but if I take it out it will crash. Also, because it
runs correctly, my text file is not created.

sSQL = "Delete from pub.companies where agencyid = " & sAgencyCode

Set oRecordset = Server.CreateObject("ADODB.Recordset")
oRecordset.Open sSQL, CONNECTIONSTRING, adOpenKeyset,
adLockPessimistic, adCmdText
On Error Resume Next
if len(Err.Source) <> 0 Then
'"*** Create Txt Error Message Log ***''
Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("c:\temp\mytest.txt", True)
MyFile.WriteLine(Err.Source)
MyFile.WriteLine(Err.Number)
MyFile.WriteLine(Err.Description )
MyFile.Close
set MyFile = nothing
Err.Clear
end if

oRecordSet.Update

oRecordset.Close
Set oRecordset = Nothing

 

[Mark Schupp]

You are checking for the error before it happens.
you need to put the error logging code after every operation that could be
causing an error. If you check your database you will probably find that the
update is not happening. Also you should use err.number <> 0 for your test.

 

[crjunk]

You are checking for the error before it happens.
you need to put the error logging code after every operation that could be
causing an error. If you check your database you will probably find that the
update is not happening. Also you should use err.number <> 0 for your test.

--
--Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

I played around with my code and moved it up above my oRecordSet.Open
statement so that it looks like this:

sSQL = "Delete from pub.companies where agencyid = " & sAgencyCode

Set oRecordset = Server.CreateObject("ADODB.Recordset")

On Error Resume Next
if Err.Num <> 0 Then
'"*** Create Txt Error Message Log ***''
Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("c:\temp\ErrLog.txt", True)
MyFile.WriteLine(Err.Source)
MyFile.WriteLine(Err.Number)
MyFile.WriteLine(Err.Description )
MyFile.Close
set MyFile = nothing
Err.Clear
end if

oRecordset.Open sSQL, CONNECTIONSTRING, adOpenKeyset,
adLockPessimistic, adCmdText
oRecordSet.Update
oRecordset.Close

When I checked the Error Log, it actuall recorded something. This is
what was in it:
Microsoft VBScript runtime error
438
Object doesn't support this property or method

I'm not exactly sure what this is refering to.

crjunk

 

[Bob Barrows [MVP]]

I played around with my code and moved it up above my oRecordSet.Open
statement so that it looks like this:

sSQL = "Delete from pub.companies where agencyid = " & sAgencyCode

Set oRecordset = Server.CreateObject("ADODB.Recordset")

On Error Resume Next
if Err.Num <> 0 Then

There is no such thing as "Err.Num".

'"*** Create Txt Error Message Log ***''
Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("c:\temp\ErrLog.txt", True)
MyFile.WriteLine(Err.Source)
MyFile.WriteLine(Err.Number)
MyFile.WriteLine(Err.Description )
MyFile.Close
set MyFile = nothing
Err.Clear
end if

oRecordset.Open sSQL, CONNECTIONSTRING,

Never do this (use a connection string) - you kill performance because this
disables connection pooling. Use an explicit connection object instead.

adOpenKeyset,
adLockPessimistic, adCmdText

??? Why are you opening a recordset on a query that does not return
records?!?!?

oRecordSet.Update

There is nothing to update! Your query did not return records.

oRecordset.Close

When I checked the Error Log, it actuall recorded something. This is
what was in it:
Microsoft VBScript runtime error
438
Object doesn't support this property or method

I'm not exactly sure what this is refering to.

I am. It refers to your attempt to read a nonexistent property: err.Num. The
name of the property is "Number", not "Num".

crjunk

Sigh.
You have to check the err object after EVERY line that could cause an error.
It's best to encapsulate this in a sub. Also, get rid of the recordset
object. It is not needed in this situation. Lastly, stop using dynamic sql,
it leaves you open to hackers using the SQL Injection exploit::
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

As you can see from these articles, the surest way to prevent sql injection
is to use parameters instead of dynamic sql. My preference is to use stored
procedures, but I do not know if Progress supports them. Without stored
procedures, the only way to use parameters is via the Command object:

****************************************************************************************
On Error Resume Next

sSQL = "Delete from pub.companies where agencyid = ?"

if CStr(sAgencyCode) <> "" then
Set cn= Server.CreateObject("ADODB.Connection")
cn.Open CONNECTIONSTRING
Set cmd= Server.CreateObject("ADODB.Command")
cmd.CommandText = sSQL
cmd.Execute ,array(sAgencyCode),129
if Err.Number <> 0 Then
LogError Err
Response.Write "An error occurred. Check log file"
Response.End
End if
End if

Sub LogError(pErr)
Const ForAppending = 8
'"*** Create Txt Error Message Log ***''
Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
'use OpenTextFile instead of CreateTextFile
Set MyFile = fspenTextFile("c:\temp\ErrLog.txt", _
ForAppending, True)
'I assume the IUSR account has permissions for this folder ...

MyFile.WriteLine(pErr.Source)
MyFile.WriteLine(pErr.Number)
MyFile.WriteLine(pErr.Description )
MyFile.Close
set MyFile = nothing
pErr.Clear
end if
End Sub


HTH,
Bob Barrows

 

[Mark Schupp]

See bob's response first.

I think it may be easier for you to debug this if you create a test form
that simulates what the calling client is doing. Then remove the error traps
and call the page from your form. That will give you the standard ASP error
messages.

It would still be a good idea to include an error log in the page because it
is being called in the background by another server. You will need to check
for errors after every statement.

--
--Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

 

[crjunk]

Thanks for you help. I finally was able to get the errors associated
with my code to be recorded to the log file.

Crjunk