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

    Page fields not updating to DB ...

    Hi, The following bug was reported from the work of a previous programmer.

    On a form called 'update_form.asp' there serveral input fields including text boxes and dropboxes ....etc.
    When i put the form in edit mode, and update a few fields, and then press the 'Update' button, the changes are not saved to the DB table.

    Appreciate if anyone can point me in right direction.

    The code run behind the 'Update' button is as follows ....


    Code:
    <% 
    <!--#include virtual="/include/ErrorHandler.asp"--> 
    On Error Resume Next 
    
    '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 serverName
    Dim RS_DROP
    Dim serverownername(10)
    Dim soarray(10)
    Dim BUIDsql
    Dim addSQL
    Dim serverbusownerSQL
    Dim myDynArray(19)
    Dim ccomments(10)
    Dim j
    Dim i
    Dim check
    Dim delcount
    Dim x
    i = 0
    j = 0
    check = 0
    delcount = 0
    x = 0
    
    'Server.ScriptTimeout = 999999999
    response.write "2"
    'Read in the record number to be updated
    searchID = Request.Form("SERVER_ID")
    serverName = Request.Form("SERVERNAME")
    strDes = Request.Form("DESCRIPTION")
    strUser = request.servervariables("logon_user")
    strIP = Request.Form("IP_ADDRESS")
    
    strNotify = Request.Form("NOTIFY")
    
    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")
    response.write "3"
    'Set an active connection to the Connection object using a DSN connection
    'adoCon.Open Application("connectAdmin")
    adoCon.Open Application("connectUser")
    response.write "4"
    'Create an ADO recordset object
    Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
    response.write "5"
    Set RS_DROP = Server.CreateObject("ADODB.Recordset")
    
    'Set the cursor type we are using so we can count the recordset
    RS_DROP.CursorType = 1
    'RS_DROP.CursorType = 2
    
    'Set the lock type so that the record is locked by ADO when it is updated
    RS_DROP.LockType = 3
    
    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT servers.SERVERNAME, IP_ADDRESS, DESCRIPTION, SUPPORT_CONTACT, LOCATION, RACK, NOTES, HARDWARE_SUPPORT_DETAILS, OPERATING_SYSTEM, SERVICE_PACK, LOGON_DOMAIN, MISC_COMPONENTS, APPLICATIONS, ALLOWED_THROUGH_PROXY, BACKUPS, FAILOVER_SERVER, CHARGEABLE, SERVER_OWNER, CLUSTERED_SYSTEM, MAKE_MODEL, SERIALNUMBER, ARRAY_CONTROLLER, DISKS, MEMORY, CPU, NETWORK_CARDS, MAC_ADDRESS, HOTFIX_UP_TO_DATE, REBOOT_GROUP, HOTFIX_REBOOT_PROCEDURES, PATCHED_BY, SMS_INSTALLED, SMS_COLLECTION, WTS_CONCERN, CLIENT_CONCERN, UPDATED, LICENCE_TYPE, EMAIL_SMS_ALERT, PREVIOUS_NAME, BEHIND_FIREWALL FROM servers WHERE SERVER_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 
    
       for each x in rsUpdateEntry.Fields
       
       	StrName = Trim(Ucase(x.name))
    
    	'IF record is checkbox value: convert "on" to "Yes" and "off" to "No"
    	If (inStr (StrName,"WTS_CONCERN")=1) or (inStr (StrName,"CLIENT_CONCERN")=1) Then 
    	
    	StrTitle=Request.Form(StrName)
    	if StrTitle = "on" Then
    	    rsUpdateEntry.Fields(StrName) = "Yes"
    	Else
    	    rsUpdateEntry.Fields(StrName) = "No"
    	End If
    	
    	Else
    	rsUpdateEntry.Fields(Strname) = Trim(Request.Form(Strname))
    	End If
    	
    	rsUpdateEntry.Update
    	
    	Next
    
    'Write the updated recordset to the database
    
    'Reset server objects
    rsUpdateEntry.Close
    
    
    
    serverbusownerSQL = "SELECT [BUID] FROM [systemsource].[dbo].[SERVER_BUSINESS_UNIT] WHERE server_id ='" & searchID & "'"
    
    
    'Get IDs from database
    RS_DROP.Open serverbusownerSQL, adoCon
    response.write RS_DROP.RecordCount & "::"
    If RS_DROP.RecordCount = 0 Then
    RS_DROP.close
    Else
    i = 0
    do until RS_DROP.EOF
    myDynArray(i) = RS_DROP.Fields("BUID")
    i = i + 1
    If (i = 19)Then Exit do
    RS_DROP.movenext
    loop
    RS_DROP.close
    End If
    
    
    'Get number of records from SERVER_BUSINESS_UNIT table
    RS_DROP.Open serverbusownerSQL, adoCon 
    sonum = RS_DROP.RecordCount
    RS_DROP.close
    
    'Get business unit names from page
    j=1
    serverownername(0) = Request.Form("SERVER_OWNER")
    ccomments(0) = Request.Form("cc")
    For i = 0 To 8 'sonum - 2
    If Request.Form("SERVER_OWNER" & i) = "Select..." Then
    
    End If
    serverownername(j) = Request.Form("SERVER_OWNER" & i)
    ccomments(j) = Request.Form("cc" & i)
    j = j + 1
    Next
    
    
    'get business unit IDs based on names from page
    For i = 0 To 8 'sonum - 1
    If serverownername(i) = Null Then
    soarray(i) = 0
    Exit For
    Else
    BUIDsql = "SELECT [BUID], [Name] FROM BUSINESS_UNITS WHERE Name = '" & serverownername(i) & "'"
    RS_DROP.Open BUIDsql, adoCon
    
    soarray(i) = RS_DROP.Fields("BUID")
    RS_DROP.close
    End If
    Next
    
    
    'Delete every link to business unit
    serverbusownerSQL = "SELECT [Server_ID],[BUId] FROM [systemsource].[dbo].[SERVER_BUSINESS_UNIT] WHERE server_id ='" & searchID & "'"
    RS_DROP.Open serverbusownerSQL, adoCon
    delcount = RS_DROP.recordcount
    If RS_DROP.RecordCount = 0 Then
    RS_DROP.close
    Else
    x = 1
    Do Until x > delcount
    RS_DROP.delete
    RS_DROP.MoveNext
    x = x + 1
    Loop
    			'Causing an infinite loop
    			'Do Until RS_DROP.EOF
    			'response.write rs_drop.fields("Buid") & "-"
    			'response.write rs_drop.fields("Server_id") & "::"
    			'RS_DROP.delete
    			'RS_DROP.MoveNext
    			'Loop
    RS_DROP.close
    End If
    
    serverbusownerSQL = "SELECT [server_id], [BUID] FROM [systemsource].[dbo].[SERVER_BUSINESS_UNIT]"
    
    
    
    'Put new fields into database
    addSQL = "SELECT [server_id], [BUID],[Charge_Comment] FROM [systemsource].[dbo].[SERVER_BUSINESS_UNIT]"
    For i = 0 To 9
    If soarray(i) <> Null Then
    
    rsUpdateEntry.Open addSQL, adoCon
    rsUpdateEntry.AddNew
    rsUpdateEntry.Fields("server_id") = searchID
    rsUpdateEntry.Fields("BUID") = soarray(i)
    rsUpdateEntry.Fields("Charge_Comment") = ccomments(i)
    rsUpdateEntry.Update
    rsUpdateEntry.Close
    Else
    End If
    Next
    Set rsUpdateEntry = Nothing
    
    Set RS_DROP = Nothing
    Set adoCon = Nothing
    
    if strNotify = "Yes" then
    
    	Set myMail=CreateObject("CDO.Message")
    
    	myMail.Subject = "Server : " & serverName & " has been updated on SystemSource."	
    	myMail.From = "SystemSource@esb.ie"	
    	
    	If Request.ServerVariables("server_name") = "systemsource" then
    		myMail.To = "SystemSourceServerNotifications@esb.ie"
    		myMail.Cc = "SystemSource@esb.ie"
    	else
    		myMail.To = "peter.veale@esb.ie"
    	end if
    	
    			
    	strTemp = ("<font face='Verdana' color=blue><i>This email is an automated response from <a href=http://SystemSource>SystemSource</a>.<br><br><br>The server : </i></font><b>"& serverName &"</b><font face='Verdana' color=blue><i> has been updated.</i></font>" & "<br><font face='Verdana' color=blue><i>Server's IP : </i></font>" & "<b>" & strIP & "</b>"  & "<br>" & "<font face='Verdana' color=blue><i>Description : </i></font><b>" & strDes & "</b><br>" & "<font face='Verdana' color=blue><i>Server Updated By : </i></font><b>" & strUser & "</b><font face='Verdana' color=blue><i> at </i></font><b>" & Time & "</b><font face='Verdana' color=blue><i> on</i></font><b> " & Date & "</b><br><br><br><font face='Verdana' color=blue><i>To view full server information <a href='http://systemsource/main.asp?page=server&id=" & searchID & "'>click here</a>.<br>Enterprise Operations <a href='http://systemsource/main.asp?page=update_entop/update_form_entop&team=entop&sort=servername&id=" & searchID & "'>click here</a>.</i></font>") 
    
    	myMail.HTMLBody = strTemp
    	myMail.Send
    end if
    
    'Return to the server page to display updated server info
    Response.Redirect "../server.asp?id=" & searchID
    %>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    Don't make us guess. WHICH PART of that code is not working? *ALL* of it? I don't think I believe that. Tell us which of the tables is not correctly being updated.
    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
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry about that. (I did this in a rush on Friday evening.)

    The table name is 'dbo.Servers'

    I tried to update the following 8 fields as a test but they did not update:

    SERVERNAME
    DESCRIPTION
    OPERATION_SYSTEM
    SERVICE_PACK
    LICENCE_TYPE
    APPLICATIONS
    LOGON_DOMAIN
    MAKE_MODEL

    As no error message is given after the attempted update it does not point me to any particular line in the code that is in error.
    It just returns to the previouse screen.
    The result is just that changes to fields not saved.

    I am guessing the problem maybe in the last 40 lines or so,
    possibly from the comment line 'Put new fields into database

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    Well, you have one error in the code that I know of and one possible error.

    And you are making one TERRIBLE mistake: You are using
    Code:
    On Error Resume Next
    at the top of the page, so you are SIMPLY HIDING all errors ANYWHERE on the page.

    GET RID of that "On Error" until you track down the problem! And then I can show you how and where you can use it. But *NEVER* use it throughout a page. It's POISON to do that.

    Now...


    First of all, when kind of database is this? Access? SQL Server? What?

    You are trying to update a table after doing
    Code:
    strSQL = "SELECT servers.SERVERNAME, IP_ADDRESS, DESCRIPTION, ...
    That is, after selecting only *SOME* of the fields in the table. Some databases don't support this and insist, instead, that you do SELECT *

    But the one major error I do see is that you do
    Code:
        for each x in rsUpdateEntry.Fields
            ...
            rsUpdateEntry.Update
    
        Next
        rsUpdateEntry.Close
    NO! You should only call UPDATE once per record.

    So the sequence should instead be
    Code:
        for each x in rsUpdateEntry.Fields
            ...
        Next
        rsUpdateEntry.Update
        rsUpdateEntry.Close
    Another possible problem (though probably not) is that you are using
    Code:
    rsUpdateEntry.CursorType = 2
    Cursor type 2 is *NOT* properly supported by either ASP or VBScript, because it requires the you supply a CALLBACK function which is not possible in either ASP or VBScript.

    Use Cursor type 3 instead.

    So change the cursor type an move the UPDATE call and try again. If it still doesn't work, let me know. Oh, and tell me the kind of DB your are using.

    And one more: Show me the schema of your Servers table. Especially I want to know the data type of WTS_CONCERN and CLIENT_CONCERN.
    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.

  • #5
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Database
    The Database I am using is SQL Server 2008.

    Schemas
    [WTS_CONCERN] [varchar](50) NULL,
    [CLIENT_CONCERN] [varchar](50) NULL,
    (Full list in schemas.txt attached.)


    Your suggested changes:

    I have commented out On Error Resume Next
    I have amended CursorType to read as follows:
    rsUpdateEntry.CursorType = 3
    I have amended the following block of code per your suggestion:

    Code:
    for each x in rsUpdateEntry.Fields
       
       	StrName = Trim(Ucase(x.name))
    
    	'IF record is checkbox value: convert "on" to "Yes" and "off" to "No"
    	If (inStr (StrName,"WTS_CONCERN")=1) or (inStr (StrName,"CLIENT_CONCERN")=1) Then 
    	
    		StrTitle=Request.Form(StrName)
    	
    	If StrTitle = "on" Then
    	    rsUpdateEntry.Fields(StrName) = "Yes"
    	Else
    	    rsUpdateEntry.Fields(StrName) = "No"
    	End If
    	
    	Else
    		rsUpdateEntry.Fields(Strname) = Trim(Request.Form(Strname))
    	End If
    		
    	Next
    		rsUpdateEntry.Update
    		rsUpdateEntry.Close
    
    
    I have commented out the existing strSQL = SELECT ....

    and replaced it with:

    strSQL = "SELECT * FROM servers WHERE SERVER_ID=" & searchID

    When I save the code changes and try an update I get the following message:

    Microsoft OLE DB Provider for SQL Server error '80040e09'
    The UPDATE permission was denied on the object 'Servers', database 'SystemSource', schema 'dbo'
    /update_nt/update_entry.asp, line 116


    In this instance Line 116 = rsUpdateEntry.Update

    Alternative

    If I re-instate the full original strSQL SELECT line, save code and try another update I get the following message:

    Microsoft OLE DB Provider for SQL Server error '80040e21'
    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
    /update_nt/update_entry.asp, line 114


    In this instance Line 114 =

    rsUpdateEntry.Fields(Strname) = Trim(Request.Form(Strname))
    Attached Files Attached Files

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,084
    Thanks
    80
    Thanked 4,552 Times in 4,516 Posts
    Well, that's a pretty clear error message.

    The userid you used when making your connection to this database does not have UPDATE permissions on the given table. Period.

    So you need to find out (a) whether your ASP code *should* have permissions to update that table and (b) assuming it should, why it doesn't.

    A job for the database administrator.
    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.


  •  

    Posting Permissions

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