Professional Web Applications Themes

Recordset Update Problems - ASP Database

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

  1. #1

    Default Recordset Update Problems

    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!
    Dave B Guest

  2. #2

    Default Re: Recordset Update Problems

    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!

    Aaron [SQL Server MVP] Guest

Similar Threads

  1. Replies: 0
    Last Post: September 9th, 12:54 AM
  2. Problems with Recordset (Query) in Dreamweaver BindingsPanel
    By ChuckRWD in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 24th, 05:53 PM
  3. Recordset SQL problems from a newbie
    By skolnick in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 15th, 10:07 AM
  4. Recordset problems
    By bennettian in forum Dreamweaver AppDev
    Replies: 4
    Last Post: May 26th, 09:16 AM
  5. using eval to update recordset
    By Mark Greenway in forum ASP Database
    Replies: 4
    Last Post: August 9th, 07:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139