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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
rinkelCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.