Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cursor operation conflict - SQL Server error '80040e23'

    I am working on some existing code from a previous programmer.
    There is a form called 'update_form.asp'
    On the form i have added a new dropdown box.

    When I attempt to change the entry on the dropdown list and click an Update button i get the following error message ......

    Microsoft OLE DB Provider for SQL Server error '80040e23'
    Cursor operation conflict
    /update_software/update_entry.asp, line 74


    Line 74 = rsUpdateEntry.Update

    Thanks in advance for any suggestions ....

    Full code of 'update_entry.asp' ...........


    Code:
    <% 'Dimension variables
    Dim adoCon            'Holds the Database Connection Object 
    Dim rsUpdateEntry   'Holds the recordset for the record to be updated
    Dim strSQL             'Holds the SQL query to query the database 
    Dim searchID      'Holds the record number to be updated
    Dim StrName
    
    'Read in the record number to be updated
    searchID = Request.Form("id")
    
    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")
    
    'Set an active connection to the Connection object using a DSN connection
    adoCon.Open Application("connectAdmin")
    
    'adoCon.Open("Provider=SQLOLEDB.1;Password=sourceadm;Persist Security Info=True;User ID=SystemSource_adm;Initial Catalog=SystemSource;Data Source=headsql001s")
     
    'Create an ADO recordset object
    Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
    
    'char(34) is a "
    quote=chr(34)
    
    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT APPLICATION_NAME, APPROVIAL_REQUIRED, HARDWARE, LICENSE_REQUIRED, BUSINESS_UNIT, SOFTWARE_OWNER, COST, BusinessUnit_ID, VERSION, PACKAGED_SOFTWARE, SOFTWARE_INSTALLED_MANUALLY, SW_HW_REQUIREMENTS, LATEST_PREVIOUS_VERSION, SETUP, INSTALLATION_COMMAND_LINE, INTEGRATION_DOCUMENTATION, INSTALLATION_DOCUMENTATION, NOTES, SMS_PACKAGE_ID, SMS_PLATFORM, SMS_INSTALLATION_ACCOUNT, SMS_SERVERS, SOFTWARE_OWNER, COST FROM Software WHERE APP_ID = " & searchID 
    
    'Set the cursor type we are using so we can navigate through the recordset
    rsUpdateEntry.CursorType = 2
    
    'Set the lock type so that the record is locked by ADO when it is updated
    rsUpdateEntry.LockType = 3
    
    'Open the recordset with the SQL query 
    rsUpdateEntry.Open strSQL, adoCon 
    
    'Response.Write(SearchID)
    do until rsUpdateEntry.EOF
       
       for each x in rsUpdateEntry.Fields
       
       	StrName = Ucase(x.name)
    
    	rsUpdateEntry.Fields(Strname) = Request.Form(Strname)
    	
    	rsUpdateEntry.Update
    	
    	Next
        rsUpdateEntry.MoveNext
        
    loop
    
    'Write the updated recordset to the database
    
    'Reset server objects
    rsUpdateEntry.Close
    Set rsUpdateEntry = Nothing
    Set adoCon = Nothing
    
    'Return to the server page to display updated server info
    Response.Redirect "../software.asp?id=" & searchID
    %>  
    

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    This statement:
    Code:
    'Set the cursor type we are using so we can navigate through the recordset
    rsUpdateEntry.CursorType = 2
    is an invalid cursor type to use with ASP and/or VBScript. It requires that you set a CALLBACK function, but callback is not supported by either ASP-the-platform or VBScript-the-language.

    In place of the 3 statements:
    Code:
    rsUpdateEntry.CursorType = 2
    rsUpdateEntry.LockType = 3
    rsUpdateEntry.Open strSQL, adoCon
    Just do this for simplicity:
    Code:
    rsUpdateEntry.Open strSQL, adoCon, 3, 3
    *********

    HOWEVER...

    This might not be the only problem.

    Because you are only selecting *SOME* of the fields from that record, this kind of UPDATE may not work.

    And by the way, on top of that, *WHY* are you using a LOOP, expecting to change MANY records, when I would *hope* that your SELECT is only getting one record??

    You may need to rework this.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Maybe try this:
    Code:
    <% 
    'Read in the record number to be updated
    ' in the process, ensure that it *IS* a NUMBER!
    searchID = 0
    On Error Resume Next
        searchID = CLNG( Request.Form("id") )
    On Error GoTo 0
    If searchID = 0 Then
        Response.Write "No valid search id passed!"
        Response.End
    End If
    
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open Application("connectAdmin")
    Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
    
    strSQL = "SELECT * FROM FROM Software WHERE APP_ID = " & searchID 
    rsUpdateEntry.Open strSQL, adoCon, 3, 3
    If Not rsUpdateEntry.EOF
        for each x in rsUpdateEntry.Fields
      	StrName = Ucase(x.name)
            ' only change DB fields if there is a matching Form field:
            If Not IsEmpty( Request.Form(Strname) ) 
      	    rsUpdateEntry(Strname) = Request.Form(Strname)
            End If
        Next
        rsUpdateEntry.UpDate
    End If
    rsUpdateEntry.Close
    adoCon.Close
    
    'Return to the server page to display updated server info
    Response.Redirect "../software.asp?id=" & searchID
    %>
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Timba (07-20-2012)

  • #4
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have replaced the code with the block of code you have posted.
    When i ran the page it hung for a minute or so.... Then it crashed with .....

    Active Server Pages error 'ASP 0113'
    Script timed out
    /search.asp
    The maximum amount of time for a script to execute was exceeded. You can change this limit by specifying a new value for the property Server.ScriptTimeout or by changing the value in the IIS administration tools.


    I then replaced the line .....
    strSQL = "SELECT * FROM FROM Software WHERE APP_ID = " & searchID

    with the original

    strSQL = "SELECT APPLICATION_NAME, APPROVIAL_REQUIRED, HARDWARE, LICENSE_REQUIRED, BUSINESS_UNIT, SOFTWARE_OWNER, COST, BusinessUnit_ID, VERSION, PACKAGED_SOFTWARE, SOFTWARE_INSTALLED_MANUALLY, SW_HW_REQUIREMENTS, LATEST_PREVIOUS_VERSION, SETUP, INSTALLATION_COMMAND_LINE, INTEGRATION_DOCUMENTATION, INSTALLATION_DOCUMENTATION, NOTES, SMS_PACKAGE_ID, SMS_PLATFORM, SMS_INSTALLATION_ACCOUNT, SMS_SERVERS, SOFTWARE_OWNER, COST FROM Software WHERE APP_ID = " & searchID

    When i reloaded the form it loaded in normal time, i then changed a record in the dropbox and clicked the update button which gave me the following:

    Microsoft VBScript compilation error '800a03f9'
    Expected 'Then'
    /update_software/update_entry.asp, line 37
    If Not rsUpdateEntry.EOF
    ------------------------^

  • #5
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    actually i have inserted the 'Then' keyword as below and it appears to be working now. Thanks again.

    Code:
    If Not rsUpdateEntry.EOF Then    for each x in rsUpdateEntry.Fields
      	StrName = Ucase(x.name)
            ' only change DB fields if there is a matching Form field:
            If Not IsEmpty( Request.Form(Strname) ) Then  	    rsUpdateEntry(Strname) = Request.Form(Strname)
            End If
        Next
        rsUpdateEntry.UpDate
    End If

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Sorry about that...sloppy of me. It comes from trying to type code into these silly little <textarea> windows.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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