SQL Update Response

[cdmmaui]

Hello Everyone, can someone tell what response code I would get if UPDATE succeeded and failed? I want to determine if UPDATE FAILED so I can INSERT record.

TRY
oSql:=TOleAuto():New("ADODB.Recordset")
CATCH
MsgAlert("It seems that your PC does not have MDAC installed OR MDAC is corrupted.")
RETURN (.F.)
END
oSql:CursorType := 1 // opendkeyset
oSql:CursorLocation := 3 // local cache
oSql:LockType := 3 // lock opportunistic

cSqlIns := [UPDATE sailing SET ]
cSqlIns += [scheduleid='] + ALLTRIM( cScheduleId ) + [',]
cSqlIns += [carrier='] + ALLTRIM( cCarrier ) + [',]
cSqlIns += [vessel='] + ALLTRIM( _VesClean( cVessel ) ) + [',]
cSqlIns += [voyage='] + ALLTRIM( cVoyage ) + [']
cSqlIns += [ WHERE scheduleid='] + ALLTRIM( cScheduleId ) + [']

TRY
oSql:Open( cSqlIns, xCONNECT )
CATCH oError
MsgAlert( "Unable to Perform Table Update, Process Aborted." + cEol + cSqlIns + cEol + oError:Description, "XML to SQL" )
RETURN (.F.)
END

[Rick Lipkin]

Darrell

This url may help devine your error codes..

[url]
https://stackoverflow.com/questions/135 ... rror-codes
[/url]

Rick Lipkin

[cdmmaui]

Dear Rick,

Thank you. What property of oSql will contain this response?

[Rick Lipkin]

Darrell

DO not know if there would be an error code for the connection string .. as you ( and I ) use it .. it either works or fails .. but you can evaluate oErr in the break trap when you create commands or recordsets.

Code: Select all  Expand view  RUN

// global connection string

xString := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD  // sql server

oCN := CREATEOBJECT( "ADODB.Connection" )

TRY
   oCn:Open( xString )
CATCH oErr
   Saying := "Could not open a Global Connection to Database "+xSource
   MsgInfo( Saying )
   RETURN(.F.)
END TRY
 

Rick Lipkin

[cdmmaui]

Hi Rick,

Ok, I will just use the CATCH to handle the error.

[Rick Lipkin]

Darrell

You might create an intentional error and Try Catch and eval oErr with xBrowse(oErr) and see what you get.

Rick Lipkin

[cdmmaui]

Rick,

The ERROR only occurs when the SQL statement fails to process correctly. If the SQL processes correctly but no record is updated, the ERROR catch does not work. I need to submit the SQL UPDATE statement and I need to get the response code that indicates number of records updated (should be one or more for success or ZERO for failed to located record).

Is there a comprehensive list of properties related to ADO recordset open in addition to :RecordCount, :EOF and :BOF?

[cdmmaui]

Rick,

I found a Status property, however when I use it, the system errors out.

Error occurred at: 08/05/2019, 15:32:35
Error description: (DOS Error -2147352567) WINOLE/1007 Operation is not allowed when the object is closed. (0x800A0E78): ADODB.Recordset

[Rick Lipkin]

Darrell

Found this on Ado properties

[url]
https://docs.microsoft.com/en-us/sql/ad ... erver-2017
[/url]

Rick Lipkin

[nageswaragunupudi]

Mr. Darrell Ortiz

We suggest this approach:
Please copy this program to fwh\samples folder and build with buildh.bat or buildx.bat

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "adodef.ch"

function Darriel()

   local oCn, cSql, cCode, cName, dDate

   ? "Connect to FWH MSSQL DemoServer"
   oCn   := FW_OpenAdoConnection( "MSSQL,208.91.198.196,gnraore3_,fwhmsdemo,fwh@2000#", .t. )
   if oCn == nil
      ? "Failed to connect"
      return nil
   endif
   ? "Connected"

   cCode    := "NY"
   cName    := "New Name"
   dDate    := STOD( "20090810" )

   cSql     := "UPDATE [STATES] SET NAME = ?, DATE = ? WHERE CODE = ?"
   cSql     := FW_AdoApplyParams( cSql, { cName, dDate, cCode } )

   ? cSql
   /* Result cSql is
   *  UPDATE [STATES] SET NAME = 'New Name', DATE = '2009-08-10' WHERE CODE = 'NY'
   *
   */
   
   TRY
      oCn:Execute( cSql )
   CATCH
      FW_ShowAdoError( oCn )
   END

   oCn:Close()

return nil
 

1) For INSERT,UPDATE,DELETE statements, use oCn:Execute( cSql )
2) Use FW_ShowAdoError( oCn ) for display of any ado error.
3) FW_AdoApplyParams( cSql, aParams ) simplifies conversion of harbour variables into SQL literals

[cdmmaui]

Dear Rao,

Thank you!