On 9 Apr, 03:44, "Willie Moore" <will...@[EMAIL PROTECTED]
> wrote:
> Ian,
>
> You should wrap your delete in a transaction. That way if something does
w=
ork, you will be able to recover from it and rollback the statement
>
> Regards,
>
> Willie
>
> oDataConn:BeginTrans()
> oCommand =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=A0:=
=3DAdoCommand{}
> oCommand:ActiveConnection =A0 =A0 :=3DoDataConn
> oCommand:CommandText =A0 =A0 =A0 =A0 =A0:=3DcSql
> oCommand:CommandType =A0 =A0 =A0 =A0 :=3DadCmdText
> =A0oCommand:CommandTimeout =A0 :=3D0
> oRS =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 =A0 =A0 =A0 :=3DoCommand:Execute(@[EMAIL PROTECTED]
)
> oDataConn:CommitTrans()
> oRS =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 =A0 =A0 =A0 :=3Dnull_object
> oCommand =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
:=
=3Dnull_object
> lResult =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 =A0 =A0 :=3Dtrue
> ErrorBlock(cbErr)
> RECOVER USING uError
> =A0 =A0 =A0 =A0 oDataConn:RollBackTrans()
> =A0 =A0 =A0 =A0 ErrorBlock(cbErr)
> =A0 =A0 =A0 =A0 IF IsObject(uError)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0cErrMsg :=3D "SQLConnection:ExecCommand
Stateme=
nt Error : " +
> =A0 =A0 =A0 =A0 =A0 =A0 uError:Description +
> =A0 =A0 =A0 =A0 =A0 =A0 =A0CRLF + "Delete Instruction Failed"
> =A0 =A0 =A0 =A0 ELSEIF IsString(uError)
> =A0 =A0 =A0 =A0 =A0 =A0 cErrMsg :=3D uError + CRLF + "Delete Instruction
F=
ailed"
> =A0 =A0 =A0 =A0 ENDIF
> =A0 =A0 =A0 =A0 ErrorBox{,cErrMsg}:show()
> END SEQUENCE
> return nRecords
>
>
>
> "Ian" <ithom...@[EMAIL PROTECTED]
> wrote in
messagenews:9c3ed7e5-c8ba-4f4d-93=
5b-dd62b98db5be@[EMAIL PROTECTED]
> > Hi Willie,
>
> > I took your advice about cascading deletes and set up my tables
> > accordingly. Having tested this it works a treat but can you clarify
> > one thing for me.
>
> > The code I use is below
>
> > lOCAL nRecords =A0 =A0 =A0 =A0 as LONG
> > lOCAL oRS =A0 =A0 =A0 =A0 =A0 =A0 =A0as ADORecordset
> > lOCAL oCommand =A0 =A0 =A0 =A0 as ADOCommand
> > lOCAL cbErr =A0 =A0 =A0 =A0 =A0 =A0as CODEBLOCK
> > local lResult :=3Dfalse =A0as logic
> > locAL uError =A0 =A0 =A0 =A0 =A0 as USUAL
> > local cErrMsg =A0 =A0 =A0 =A0 =A0as string
>
> > cbErr =A0 :=3D ErrorBlock({|oErr|_Break(oErr)})
>
> > BEGIN SEQUENCE
> > =A0 oCommand =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0:=3DAdoCommand{}
> > =A0 oCommand:ActiveConnection :=3DoDataConn
> > =A0 oCommand:CommandText =A0 =A0 =A0:=3DcSql
> > =A0 oCommand:CommandType =A0 =A0 =A0:=3DadCmdText
> > =A0 oCommand:CommandTimeout =A0 :=3D0
> > =A0 oRS =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
:=3DoCommand:Execute=
(@[EMAIL PROTECTED]
)
> > =A0 oRS =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 :=3Dnull_object
> > =A0 oCommand =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0:=3Dnull_object
> > lResult =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 :=3Dtrue
> > =A0 ErrorBlock(cbErr)
> > =A0 RECOVER USING uError
> > =A0 ErrorBlock(cbErr)
> > =A0 IF IsObject(uError)
> > =A0 =A0cErrMsg :=3D "SQLConnection:ExecCommand Statement Error : " +
> > uError:Description +
> > =A0 =A0CRLF + "Delete Instruction Failed"
> > =A0ELSEIF IsString(uError)
> > =A0 cErrMsg :=3D uError + CRLF + "Delete Instruction Failed"
> > =A0ENDIF
> > =A0ErrorBox{,cErrMsg}:show()
> > END SEQUENCE
> > return nRecords
>
> > If there are twenty records in the details file but only ten can be
> > deleted for whatever reason, will this leave all twenty records intact
> > or do I have to do some kind of rollback to recover the ten that were
> > deleted.
>
> > ie If it can not delete all twenty detail records it should not delete
> > any.
>
> > Regards,
> > Ian- Hide quoted text -
>
> - Show quoted text -
Hi Willie,
Thank you for your response. I very much appreciate your assistance.
Many Thanks,
Ian
|
