How to tell if a db update was successful?

2019-02-14 14:46发布

站内问答 / 前端开发
1544 4 6

I'm trying to modify some legacy ASP classic code, and I don't know much about ASP. How do I tell if a database Insert, Update, or Delete failed? By 'failed', I mean it either threw an error or affected zero rows.

Here's the code that was already in the ASP file that sets up the database connection:

On Error Resume Next
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=MSDAORA;Data Source=dbname;User Id=dbuser;Password=dbpw;"

There are a few Select statements that work like:

qry =  "select stuff here..."
Set objRs = objConn.Execute(qry)

I understand how to get the results from the Select statements, but how do I get the results of a non-Select statement? Using a result set doesn't seem like it'd be the right way to do it. Or is it?

4条回答
不美不萌又怎样
2楼-- · 2019-02-14 15:02

You may also use:

con.execute Sql,RecordsAffected

where, con is Connection
Sql is your Command String
RecordsAffected is count of Records Affected

查看更多
0人赞 添加讨论(0) 举报
家丑人穷心不美
3楼-- · 2019-02-14 15:12

Use the first Execute method parameter to get the rows affected:

On Error resume next
Dim RecordsAffected as long
Dim cmd
Set cmd = server.createobject("ADODB.Command")

cmd.ActiveConnection = GetConnectionString()
cmd.CommandText = "Select stuff here"
cmd.CommandType = adCmdText

cmd.Execute RecordsAffected, , adExecuteNoRecords
If err.number > 0 or RecordsAffected = 0 then
    Response.Write "No record affected or SQL error or something"
end if

Using adExecuteNoRecords will gain performance according to several sources

Source: Execute method in MSDN

查看更多
0人赞 添加讨论(0) 举报
劳资没心，怎么记你
4楼-- · 2019-02-14 15:15

You could use a transaction.

<%@Transaction="REQUIRES_NEW"  Language="VBSCRIPT" %>
<%Option Explicit%>
<HTML>
<HEAD>
</HEAD>
<BODY>
<%

'Perform Work
'Database Operation #1
'Database Operation #2

Sub OnTransactionCommit
    Response.Write "<H1>Success!</H1>"
End Sub

Sub OnTransactionAbort
    Response.Write "<H1>Failure!</H1>"
End Sub
%>

</BODY>
</HTML>
查看更多
0人赞 添加讨论(0) 举报
姐就是有狂的资本
5楼-- · 2019-02-14 15:24

For INSERTs and UPDATEs the execution will return the number of rows affected, unless you set NO COUNT ON in your stored procedures. When you define a Command object calling its ExecuteNonQuery method will return that number to you. I use the return of those numbers (and whether they're non-zero) as an indication that it worked.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)