SQL command will not execute in ASP web app

Question

I'm making a web app using ASP-classic, and I'm trying to update some values in an Oracle database. Here is what I have so far.

<script>
function getUpdateHTML()
{
    var lockoutcheck;
    if (document.getElementById("cellRollLockoutYN").checked)
    {
        lockoutcheck = "'Y'";
    }
    else
    {
        lockoutcheck = "'N'";
    }
    var updatestring = "RollInventoryViewDev.asp?updatelockout=";
    updatestring = updatestring + lockoutcheck + "&updatepatterndepth=";
    updatestring = updatestring + document.getElementById("cellProductPatternDepthAvg").value + "&";
    updatestring = updatestring + "action=update&sort=roll_id&sortdir=<%=intSortDir%>&id=<%=intRollID%>&iddt=<%=StrRollIdDt%>&seqnum=<%=intRollSeqNum%>&findesc=<%=strRollFinishDescription%>&fincd=<%=strRollFinishCD%>&diam=<%=dblRollDiameter%>&crown=<%=dblRollCrown%>&crownaim=<%=dblRollCrownAim%>&prosrough=<%=intRollProsRoughness%>&peaksrough=<%=intRollPeaksRoughness%>&hardness=<%=intRollHardness%>&metalcd=<%=strRollMetalCD%>&rolltype=<%=strRollType%>&lockout=<%=chrRollLockoutYN%>&depthavg=<%=dblProductPatternDepthAvg%>";
    <!--alert("Attempting to Update Record with Lockout: " + lockoutcheck + " and Pattern Depth: " + document.getElementById("cellProductPatternDepthAvg").value);-->
    window.open(updatestring,"_self")
}
</script>





<% 
'If update selected, then update information
If Request.QueryString("action") = "update" Then

    sqlQry = "update tp07_roll_inventory_row set roll_lockout_yn = "&chrUpdateLockout&", product_pattern_depth_avg = "&dblUpdateDepthAvg&" where roll_id = "&intRollID&""%>
    <script>alert("<%=sqlQry%>");</script>

    <%

    ' Turn error handling on.  If an error is generated, our program will continue to execute and 
    '  the error code will be stored in Err.number.
    'On Error Resume Next

    ' Execute SQL code
    Set RS = dbConn.Execute(sqlQry, RowsAffected)
    ' If an error occured then construct an error message to display to the user
    If err<>0 then
        message="Unable to Perform Update: "
        for each objErr in dbConn.Errors
            message = message & objErr.Description & "<br>"

        next
        ' Set message color to red to indicate failure
        messageColor = "#DD2222"
    ' If there was no error then generate a "success" message to display to the user
    Else
        message="Update Successful: " & rowsAffected & " row(s) updated."
        ' Set message color to green to indicate success
        messageColor = "#22DD22"
    End If
    Response.write(message)


End If
%>

It generates an sql query which will look like update tp07_roll_inventory_row set roll_lockout_yn = 'N', product_pattern_depth_avg = 2.6 where roll_id = 8502; It then alerts with the SQL Query (just so I know it's formatted correctly), turns on error handling and then executes.

If it fails to update, it should print "Unable to Perform Update: " and all of the error codes.

Right now, it prints "Unable to Perform Update: " but none of the error codes print. And it's definitely failing to update.

EDIT: I added my javascript that generated the new URL after update is clicked, so maybe there's a problem in here.

Originally, this script was down below, but now that I've moved it above and commented out On Error, There's a Object required: '' error on the Set RS = dbConn.Execute(sqlQry, RowsAffected) line

EDIT: Here's the subroutine that opens that database connection and the code that opens the connection and queries the database for data to fill the tables

%>
<%Sub dbConnect()
    Set dbConn=Server.CreateObject("ADODB.Connection")
    dbConn.Open "Provider=MSDAORA.1;Password=database;User ID=dba_prd;Data Source=devtm2tcp"
End Sub

Sub dbDisconnect()
    dbConn.Close
    Set dbConn = Nothing
End Sub
%>




<% ' 
    boolDetailTable = false
    Call dbConnect()
    sqlQry = "SELECT * FROM TP07_ROLL_INVENTORY_ROW"
    if Len(strSort) > 0 then
        sqlQry = sqlQry + " ORDER BY " & strSort
        if intSortDir <> "1" then
            sqlQry = sqlQry + " DESC"
        end if
    end if

    getRS sqlQry
%>

enter image description here

What database driver are you using? Does it produces an Errors collection? — Jeff
– Jeff, Commented Jun 26, 2013 at 18:11
This code is vulnerable to sql injection attacks. It's practically begging to get hacked. — Joel Coehoorn
– Joel Coehoorn, Commented Jun 26, 2013 at 18:40
remove On Error Resume Next for the time being and see if you get any errors. — Flakes
– Flakes, Commented Jun 27, 2013 at 5:31

Flakes · Accepted Answer · 2013-06-27 11:43:33Z

1

change your code to this and see if see any errors printed: I have added err.description to the message.

<%

    ' Turn error handling on.  If an error is generated, our program will continue to execute and 
    '  the error code will be stored in Err.number.
    On Error Resume Next

    ' Execute SQL code
    Call dbConnect()
    Set result = dbConn.Execute(sqlQry, rowsAffected)
    ' If an error occured then construct an error message to display to the user
    If err.Number <> 0 then
        message="Unable to Perform Update: "
    'get the error description from err object
        message = message & Err.Description & "<br>"

    'get errors, if any, from connection object
        for each objErr in dbConn.Errors
            message = message & objErr.Description & "<br>"
        next
        ' Set message color to red to indicate failure
        messageColor = "#DD2222"
    ' If there was no error then generate a "success" message to display to the user
    Else
        message="Update Succssfull: " & rowsAffected & " row(s) updated."
        ' Set message color to green to indicate success
        messageColor = "#22DD22"
    End If
    Call dbDisconnect()
            Response.Write(message)
End If
%>

edited Jun 27, 2013 at 11:43

answered Jun 27, 2013 at 11:28

Flakes

2,3608 gold badges32 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Alex Kibler Over a year ago

Unable to Perform Update: Object Required

Flakes Over a year ago

what is dbconn ? if that is a connection string, you have to first opne a connection object using that

Alex Kibler Over a year ago

I think it might be because dbConn didn't exist yet. I just realized that my database connection code wasn't happening until after the update code (for some stupid reason). I moved the connection code above the update code, and now it just hangs whenever I click update.

Alex Kibler Over a year ago

dbConn is the connection object. I just tried moving the update code to the bottom of the program (so it wouldn't execute until after all of the HTML is executed), and now I'm getting Object Required again

Flakes Over a year ago

it does not matter if you put the asp code before the html, the server side code is executed first anyway. Only thing is you have to create a connection object and open it before you do the execute

|

Collectives™ on Stack Overflow

SQL command will not execute in ASP web app

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related