Recordset Update Problems

[Dave B]

This subroutine is for an error logger, and I'm trying to check if a
similar error already exists, only update a few things (time, number of
occurrences). Unfortunately sometimes it works, sometimes it manages to
delete the old error record and add a new one, even tho they're both the
same error... mystifying. Any help would be appreciated.

<code>

Sub logError(objError, strNotes, strReferrer, strRemoteAddr,
strRemoteHost, strGetData, strPostData)
'Defaults
strUser = ""

'Get webuser name
strUser = userinfo_LoginName

'Recordset
Dim RS
Set RS = Server.CreateObject("ADODB.recordset")

'Delete duplicate errors
Dim strQuery
strQuery = "SELECT * FROM ErrorLog WHERE "
strQuery = strQuery & "Category LIKE '" & padQuotes(objError.Category)
& "' AND "
If objError.Line > -1 Then
strQuery = strQuery & "Line = " & Int(objError.Line) & " AND "
End If
strQuery = strQuery & "Location LIKE '" & padQuotes(objError.File) & "'
"
With RS
.ActiveConnection = Application("ConnStrCatalog")
.Source = strQuery
.CursorType = adOpenStatic
.LockType = adLockPessimistic
.Open
If NOT RS.EOF Then
.Fields("Time") = Now()
intTemp = .Fields("Occurrences")
' If intTemp = NULL Then
' intTemp = 0
' End If
.Fields("Occurrences") = intTemp + 1
.Fields("Fixed") = 0
Else
.AddNew
.Fields("ASPCode") = objError.aspCode
.Fields("ErrorNumber") = objError.Number
.Fields("Category") = padQuotes(objError.Category)
.Fields("Location") = padQuotes(objError.File)
.Fields("Source") = objError.Source
.Fields("Line") = objError.Line
.Fields("Col") = objError.Column
.Fields("Description") = padQuotes(objError.Description)
.Fields("ASPDesc") = padQuotes(objError.ASPDescription)
.Fields("Time") = Now()
.Fields("WebUser") = strUser
.Fields("Notes") = strNotes
.Fields("Fixed") = 0
.Fields("Referrer") = strReferrer
.Fields("RemoteAddr") = strRemoteAddr
.Fields("RemoteHost") = strRemoteHost
.Fields("GetData") = strGetData
.Fields("PostData") = strPostData
.Fields("Occurrences") = 1
End If
.Update
.Close
End With

Set RS = Nothing
End Sub

</code>

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron [SQL Server MVP]]

Ugh, ugh, ugh.

DO NOT USE A RECORDSET TO INSERT / UPDATE DATA!

The typical way to do this is to check if the row exists, and if so, perform
an UPDATE statement, or else perform an INSERT statement. Another approach
is to perform an update, and if the recordsAffected = 0, perform an insert.

Is this Access, SQL Server, Oracle, Paradox, dBase, ...? Should we guess?

If you give us more information, we can show you how to do this right.

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

> This subroutine is for an error logger, and I'm trying to check if a
> similar error already exists, only update a few things (time, number of
> occurrences). Unfortunately sometimes it works, sometimes it manages to
> delete the old error record and add a new one, even tho they're both the
> same error... mystifying. Any help would be appreciated.
>
> <code>
>
> Sub logError(objError, strNotes, strReferrer, strRemoteAddr,
> strRemoteHost, strGetData, strPostData)
> 'Defaults
> strUser = ""
>
> 'Get webuser name
> strUser = userinfo_LoginName
>
> 'Recordset
> Dim RS
> Set RS = Server.CreateObject("ADODB.recordset")
>
> 'Delete duplicate errors
> Dim strQuery
> strQuery = "SELECT * FROM ErrorLog WHERE "
> strQuery = strQuery & "Category LIKE '" & padQuotes(objError.Category)
> & "' AND "
> If objError.Line > -1 Then
> strQuery = strQuery & "Line = " & Int(objError.Line) & " AND "
> End If
> strQuery = strQuery & "Location LIKE '" & padQuotes(objError.File) & "'
> "
> With RS
> .ActiveConnection = Application("ConnStrCatalog")
> .Source = strQuery
> .CursorType = adOpenStatic
> .LockType = adLockPessimistic
> .Open
> If NOT RS.EOF Then
> .Fields("Time") = Now()
> intTemp = .Fields("Occurrences")
> ' If intTemp = NULL Then
> ' intTemp = 0
> ' End If
> .Fields("Occurrences") = intTemp + 1
> .Fields("Fixed") = 0
> Else
> .AddNew
> .Fields("ASPCode") = objError.aspCode
> .Fields("ErrorNumber") = objError.Number
> .Fields("Category") = padQuotes(objError.Category)
> .Fields("Location") = padQuotes(objError.File)
> .Fields("Source") = objError.Source
> .Fields("Line") = objError.Line
> .Fields("Col") = objError.Column
> .Fields("Description") = padQuotes(objError.Description)
> .Fields("ASPDesc") = padQuotes(objError.ASPDescription)
> .Fields("Time") = Now()
> .Fields("WebUser") = strUser
> .Fields("Notes") = strNotes
> .Fields("Fixed") = 0
> .Fields("Referrer") = strReferrer
> .Fields("RemoteAddr") = strRemoteAddr
> .Fields("RemoteHost") = strRemoteHost
> .Fields("GetData") = strGetData
> .Fields("PostData") = strPostData
> .Fields("Occurrences") = 1
> End If
> .Update
> .Close
> End With
>
> Set RS = Nothing
> End Sub
>
> </code>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!