SQL return code in VBscript

[Agro42]

Ok, I've searched the web with Google, and I've read over 100 Q&As on this forum, but I can't find anywhere how to get the SQL return code out of a recordset after an insert.
I am doing inserts of records that come from an outside vendor, and they often send records with duplicate keys.
I want to do an insert into DB2, then get the return code and check for timeouts, duplicate keys or any of the other possible errors.
How do I get the SQLCode?

 

[ralphtrent]

have you set on error resume next and then create an error handler? That is what I do. After my execute step, I say

if err.number <> 0 then
wscript.echo err.description
end if

Try that, but make sure you have on error resume next inside the sub/function (if you are using one) that executes the db request.

 

[PHV]

If you do the insert with DAO, you may take a look at the RecordsAffected property of the object used for the Execute method.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Agro42]

I managed to get what I needed. This is what my code looks like:

dim sqlstring
dim err
sqlstring = "insert into atpa...."
Set rsATPA = CreateObject("ADODB.REcordSet")
On Error Resume Next
set rsATPA = Conn.Execute(SQLstring)
retcode=0
on error goto 0
If conn.Errors.count <> 0 Then 
   retcode = Conn.Errors(0).NativeError
End If