ADO

[Colin Haig]

Hi All

I have the following code working - connecting to a MYOB ( accounting software)
but I would like to add a record to the table.

#include "fivewin.ch"
function main()
local cStr,oCon,oRecSet,oRs,cn,nCnt := 0

cStr := "Driver={MYOAU0901}; TYPE=MYOB; UID=Administrator; PWD=;DATABASE=C:\Premier125\Clearwtr.myo; HOST_EXE_PATH=C:\Premier125\MYOBP.exe;NETWORK_PROTOCOL=NONET; DRIVER_COMPLETION=DRIVER_NOPROMPT"

if oCon == nil
oCon := TOleAuto():new("ADODB.Connection")
oCon:ConnectionString := cStr
TRY
oCon:Open()
lConnect := .t.
CATCH
oCon := nil
MsgInfo('Connect Fail')
return nil
END
else
lConnect := .t.
endif

if oCon != nil .and. oRs == nil
oRs := TOleAuto():new( "ADODB.RecordSet" )
oRs:ActiveConnection := oCon
oRs:Source := "select * from Cards"
oRs:LockType := 4 // adLockOptimistic
oRs:CursorLocation := 3 //adUseClient
oRs:CacheSize := 100
TRY
oRs:Open()
CATCH
MsgInfo('Table Open Failure')
return nil
END
endif


if oRs:RecordCount() > 0
do while ! oRs:eof()
MsgInfo(oRs:Fields( "LastName" ):Value )
oRs:MoveNext()
enddo
SysRefresh()
endif



return(nil)

[Colin Haig]

Hi All

Would I be better to use the ADORDD if so where to I find it - I am
using xHarbour.

Cheers

Colin

[anserkk]

Dear Mr.Colin,

Update an existing record

Code: Select all  Expand view

cSql:=""
cSql+="UPDATE Invoice SET Bill_Amount = 100 WHERE Invoice_No = 1021"
TRY
   oConnection:Execute(cSql)
CATCH oError
   MsgInfo("Could not update")
   ShowSqlError(oError)
   Return .F.
END 
 

// Now Insert a record

Code: Select all  Expand view

cSql:=""
cSql+="INSERT INTO Invoice ([Bill_No], [Bill_Amount])  VALUES (1022,2000.00)"
TRY
   oConnection:Execute(cSql)
CATCH oError
   MsgInfo("Could not add")
   ShowSqlError(oError)
   Return .F.
END 

There are other ways too.

Regards
Anser

[Colin Haig]

Hi Anser

I tried this code but it failed

if MsgYesNo('add')
cSql:=""
cSql+="INSERT INTO Cards ([Lastname]) VALUES (Techdata Software)"
TRY
oRs:Execute(cSql)
CATCH oError
MsgInfo("Could not add")
//ShowSqlError(oError)
Return .F.
END
endif

Cheers
Colin

[anserkk]

Hi Mr.Colin

Code: Select all  Expand view

cSql+="INSERT INTO Cards ([Lastname]) VALUES ('Techdata Software')"
 

I found that ' ' is missing. Please check the oError, to understand where the problem is. It could be a SQL syntax error. You column last name is supposed to store a character string (Char or VarChar)

Regards
Anser

[Colin Haig]

Hi Anser

Still no luck - where do I find ShowSqlError

regards

Colin

[anserkk]

Dear Mr.Colin

Code: Select all  Expand view

Local oError
TRY
   oCmd:Execute()
CATCH oError
     MsgInfo( " Error No:  "+AllTrim(STR(oError:NativeError))+CRLF+;
          " Description :  "+oError:Description)
END

Regards
Anser

[anserkk]

Code: Select all  Expand view

*---------------------------------------------------*
Function ShowSqlError(oError)
*---------------------------------------------------*
FOR EACH oError IN oApp:oConnection:Errors
   MsgInfo( " Error No:  "+AllTrim(STR(oError:NativeError))+CRLF+;
          " Descripcion :  "+oError:Description, oApp:cAppName)
   oApp:nLastErrorNo:=oError:NativeError
NEXT
RETURN ( NIL )

[Colin Haig]

Hi Anser

Still cant get oErrors to work - I have sent an email to the company that
provides the SDK to see if they can offer some help.

Appreciate you assistance.


Cheers

Colin

[nageswaragunupudi]

Its much happier to deal with ADO directly than to use ADORDD, because we have full control on the code we use.

ADORDD is good when there is not enough time to learn ADO or to quickly convert a legacy application in DBF syntax.

In this case it appears to be an ODBC driver but not a full OLEDB provider. All functionality of ADO may not be available and is restricted by the feautures of ODBC and what the provider chose to provide. We can use ADO with the limitations inherent with the Driver that is provided.