SQL Update Response

Post Reply
User avatar
cdmmaui
Posts: 653
Joined: Fri Oct 28, 2005 9:53 am
Location: The Woodlands - Dallas - Scottsdale - London
Contact:

SQL Update Response

Post by 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
*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
User avatar
Rick Lipkin
Posts: 2397
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: SQL Update Response

Post by Rick Lipkin »

Darrell

This url may help devine your error codes..

https://stackoverflow.com/questions/13 ... ror-codes

Rick Lipkin
User avatar
cdmmaui
Posts: 653
Joined: Fri Oct 28, 2005 9:53 am
Location: The Woodlands - Dallas - Scottsdale - London
Contact:

Re: SQL Update Response

Post by cdmmaui »

Dear Rick,

Thank you. What property of oSql will contain this response?
*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
User avatar
Rick Lipkin
Posts: 2397
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: SQL Update Response

Post by 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

// 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
User avatar
cdmmaui
Posts: 653
Joined: Fri Oct 28, 2005 9:53 am
Location: The Woodlands - Dallas - Scottsdale - London
Contact:

Re: SQL Update Response

Post by cdmmaui »

Hi Rick,

Ok, I will just use the CATCH to handle the error.
*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
User avatar
Rick Lipkin
Posts: 2397
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: SQL Update Response

Post by 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
User avatar
cdmmaui
Posts: 653
Joined: Fri Oct 28, 2005 9:53 am
Location: The Woodlands - Dallas - Scottsdale - London
Contact:

Re: SQL Update Response

Post by 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?
*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
User avatar
cdmmaui
Posts: 653
Joined: Fri Oct 28, 2005 9:53 am
Location: The Woodlands - Dallas - Scottsdale - London
Contact:

Re: SQL Update Response

Post by 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
*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
User avatar
Rick Lipkin
Posts: 2397
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: SQL Update Response

Post by Rick Lipkin »

Darrell

Found this on Ado properties

https://docs.microsoft.com/en-us/sql/a ... rver-2017

Rick Lipkin
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: SQL Update Response

Post by 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

#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
 
Image

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
Regards

G. N. Rao.
Hyderabad, India
User avatar
cdmmaui
Posts: 653
Joined: Fri Oct 28, 2005 9:53 am
Location: The Woodlands - Dallas - Scottsdale - London
Contact:

Re: SQL Update Response

Post by cdmmaui »

Dear Rao,

Thank you!
*~*~*~*~*~*~*~*~*~*
Darrell Ortiz
CDM Software Solutions, Inc.
https://www.cdmsoft.com
Post Reply