Connection to Mysql(problem getting the date field)

NaveenReddy
Member Posts: 53
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);
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);
0
Comments
-
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.0 -
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 Reddy0 -
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));
Bye0 -
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));0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions