How to tell if a call to SQL Execute() using ADO fails in classic ASP

Question

I have the following shortened classic ASP code that makes a SQL insert call...

cmd.CommandText = "insert into MyTable values(blah, blah, blah)"   
cmd.CommandType = adCmdText

Set rs = cmd.Execute()

Using the recordset that Execute() returns how can I tell whether the insertion was successful or not?

Answer 1

Check the Err object

cmd.CommandText = "insert into MyTable values(blah, blah, blah)"   
cmd.CommandType = adCmdText
On Error Resume Next
Set rs = cmd.Execute()
If Err.number<>0 or  objConnection.Errors.Count <> 0 Then
   'Do something to handle the error
End If
On Error Goto 0

Also have a look at this link over at 15Seconds

Answer 2

If you do not want to check for errors, you can:

cmd.CommandText = "insert into MyTable values(blah, blah, blah) SELECT @@Rowcount"
cmd.CommandType = adCmdText

Set rs = cmd.Execute()
RecordsAffected = rs(0)

In the same waw, if you have a identity column, you can get the resulted id using

cmd.CommandText = "insert into MyTable values(blah, blah, blah) SELECT @@Identity"
cmd.CommandType = adCmdText

Set rs = cmd.Execute()
NewID = rs(0)