Connection to Mysql(problem getting the date field)

NaveenReddy

Hi Guys
From navision(sql database version) i connected to mysql databse using ADO automations and getting the data in to navision. Everything works fine but when i try to get the date field from the mysql it giving me a error message saying (this data type is not supported by C/Side. you can access data from any of the following data types: vt_void,....). I am getting the error message when navision tries to execute the message statement in the below code, navision tries to get the date from mysql datababe. (Createdon is data type of date in mysql). how to get the date fields into navision from mysql ? i have no problem with other data types.

//where ADOrs is a automation datatype with microsoft activex data object.recordset as subtype

My code:

OpenMethod := 1; // 1=adOpenKeyset; 2=adOpenDynamic; 3= adOpenStatic
LockMethod := 3; // 1=dLockreadonly; 2=adLockPessimistic; 3=adLockOptimistic; 4=adLockBatchOptimistic
CREATE(ADOConn);
ConnStr := 'Driver={MySQL ODBC 3.51 Driver}; SERVER=192.168.16.50; PORT=3307;' +
'Database=stdb; User=root; Password=; Option=3';;
ADOConn.Open(ConnStr);
CREATE(ADOrs);
ADOrs.Open('Select RecordNo,BranchID,FirstName,MiddleName,LastName,SSLimitMetYN,CurrentEmployeeYN,CreatedOn from employees'
,ADOConn,OpenMethod,LockMethod);


ADOrs.MoveFirst;
MESSAGE(FORMAT((ADOrs.Fields.Item('CreatedOn').Value)));

ADOrs.UpdateBatch;
ADOrs.Requery;
ADOrs.Close; CLEAR(ADOrs);
ADOConn.Close; CLEAR(ADOConn);

EmiliaMeier

Perhaps there are NULL-Values in the database for CreatedOn-column.
You can use the function ADOrs.Fields.Item('CreatedOn').Value.ISDATE to try out if the date is valid.

NaveenReddy

HI
Thanks for the reply, your right the createdon has null once i deleted the null records it worked fine.

Do you have any idea how to get around with the null values, bcoz some fields which i am getting into navision will be null sometimes.

Thanks
Naveen Reddy

EmiliaMeier

Hi,

you can find out the null-values with help of the ISDATE-function.
If the value is null the IF-statement carries on with the else-sector and no error occurs.

exp.:

IF rs.Fields.Item('CreatedOn').Value.ISDATE THEN
dDate := rs.Fields.Item('CreatedOn').Value
ELSE
dDate := 0D;
MESSAGE(FORMAT(dDate));

Bye

kmkaot

Is there any syntax for

'Text' and Number Checking in same Style of ISDATE?

F rs.Fields.Item('CreatedOn').Value.ISDATE THEN
dDate := rs.Fields.Item('CreatedOn').Value
ELSE
dDate := 0D;
MESSAGE(FORMAT(dDate));