SQL Return Codes in ADO.command

Hi !!
I am using the ADO command object.
in the command text i am using an SQL
update statement.
after i execute the command how do i get
the sql code returned by the database.
database in question -DB2 UDB
i am using ODBC connection to Access DB2
e.g
set cmd = new ADODB.command
cmd.commandtext="update employee set pay = 200 where emp_cd = 20"
cmd.execute

What happens if the employee_cd = 20 does not exist ,how can i trap it in the next line after the execute.

Any help will be appreciated
rahulkdAsked:
Who is Participating?
 
rinkelConnect With a Mentor Commented:
You can use the function provided by ADO which is "Status" with the format :
ADORecordset.Status
After applying the statement, you may know about the record has been saved or not.

And the second way of doing this is, adding "On error goto label_name" to catch the error.  E.g.

On Error Goto Error_executing
set cmd = new ADODB.command
cmd.commandtext="update employee set pay = 200 where emp_cd = 20"
cmd.execute
....
Exit Sub
Error_executing:
MsgBox Err.Description
End Sub

In addition to the status function, the returning status can be obtained as following:
adRecOK-- The record was successfully updated.
adRecNew-- The record is new.
adRecModified-- The record was modified.
adRecDeleted-- The record was deleted.
adRecUnmodified-- The record was not modified.
adRecInvalid-- The record was not saved because its bookmark is invalid.
adRecMultipleChanges-- The record was not saved because it would have affected multiple records.
adRecPendingChanges-- The record was not saved because it refers to a pending insert.
adRecCanceled-- The record was not saved because the operation was canceled.
adRecCantRelease-- The new record was not saved because of existing record locks.
adRecConcurrencyViolation-- The record was not saved because optimistic concurrency was in use.
adRecIntegrityViolation-- The record was not saved because the user violated integrity constraints.
adRecMaxChangesExceeded-- The record was not saved because there were too many pending changes.
adRecObjectOpen-- The record was not saved because of a conflict with an open storage object.
adRecOutOfMemory-- The record was not saved because the computer has run out of memory.
adRecPermissionDenied-- The record was not saved because the user has insufficient permissions.
adRecSchemaViolation-- The record was not saved because it violates the structure of the underlying database.
adRecDBDeleted-- The record has already been deleted from the data source.
0
 
leesssCommented:
I normally use recordset to do this, so this is not a direct answer but an alternative:

dim dbConn as ADODB.Connection
dim adoRS as ADODB.RecordSet
dim strSQL as string

set dbConn = new ADODB.Connection
set adoRS = new ADODB.RecordSet

dbConn.Open "< your connection string >"

'make a dummy select
strSQL = "Select pay from employee where emp_cd = 20"

adoRS.Open strSQL, dbConn, adOpenStatic, adLockOptimistic

if adoRS.RecordCount = 1 then
'found a record, process your record, say do you update here
elseif adoRS.RecordCount > 1 then
'found multiple records, process your records, say do you update here
elseif adoRS.RecordCount = 0 then
'no record found
else
'db error
end if

adoRS.Close
set adors = nothing
dbConn.Close
set dbConn = nothing

If you do use this method, I would appreciate for some points.
0
All Courses

From novice to tech pro — start learning today.