SQL命令无法在ASP web应用程序中执行

Question

我正在使用ASP-classic制作一个web应用程序，并且我正在尝试更新Oracle数据库中的一些值。这是我到目前为止所拥有的。

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

它生成一个看起来像update tp07_roll_inventory_row set roll_lockout_yn = 'N', product_pattern_depth_avg = 2.6 where roll_id = 8502;的sql查询，然后用SQL查询发出警报(只是为了确保它的格式正确)，打开错误处理，然后执行。

如果更新失败，则应显示"Unable to Perform update：“和所有错误代码。

现在，它输出"Unable to Perform Update：“，但没有打印任何错误代码。而且它肯定没能更新。

编辑:我添加了在点击更新后生成新URL的javascript，所以这里可能有问题。

最初，这个脚本位于下面，但是现在我将它移到了上面并注释掉了Error，在Set RS = dbConn.Execute(sqlQry, RowsAffected)行上出现了一个Object required: ''错误

编辑:下面是打开数据库连接的子例程和打开连接并查询数据库以填充表的数据的代码

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

​

​

Answer 1

将您的代码更改为以下内容，并查看是否打印出任何错误:我已将err.description添加到消息中。

<%

    ' 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
%>