Page 1 of 1
SQL Update Response
Posted: Sat Aug 03, 2019 8:06 pm
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
Re: SQL Update Response
Posted: Mon Aug 05, 2019 1:49 pm
by Rick Lipkin
Darrell
This url may help devine your error codes..
https://stackoverflow.com/questions/13 ... ror-codes
Rick Lipkin
Re: SQL Update Response
Posted: Mon Aug 05, 2019 2:29 pm
by cdmmaui
Dear Rick,
Thank you. What property of oSql will contain this response?
Re: SQL Update Response
Posted: Mon Aug 05, 2019 3:45 pm
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
Re: SQL Update Response
Posted: Mon Aug 05, 2019 3:50 pm
by cdmmaui
Hi Rick,
Ok, I will just use the CATCH to handle the error.
Re: SQL Update Response
Posted: Mon Aug 05, 2019 6:57 pm
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
Re: SQL Update Response
Posted: Mon Aug 05, 2019 8:24 pm
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?
Re: SQL Update Response
Posted: Mon Aug 05, 2019 8:47 pm
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
Re: SQL Update Response
Posted: Tue Aug 06, 2019 3:50 pm
by Rick Lipkin
Re: SQL Update Response
Posted: Thu Aug 08, 2019 12:17 am
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
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
Re: SQL Update Response
Posted: Thu Aug 08, 2019 11:15 pm
by cdmmaui
Dear Rao,
Thank you!