Accessing Access Data in Navision.

aman_mbs
Member Posts: 158
Dear All,
While accessing the data from Access i have written the following code in Navision i have two problems in the following code
1) when the data is pulled the first character of the first field is always missing like my first field is Empcode then if code is MP0001 then only P0001 is pulled same is for all the rows.
2) I have three fields in the table both in the Access and the navision table When Ever i write the query 'Select EmpCode, EmpName From Employee' the following error is flashed out "Call to the member Item Failed Fields written the following message: Item Cannot be found in the corresponding collection to the requested name." But it wrks properly when i select all the fields of the table.
My Code is
Constr := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Access To Navision\Amantest.mdb;' +
'User Id=admin; Password=;';
StrSql := 'Select EmpCode, EmpName From Employee';
CREATE(varRecordSet);
varRecordSet.Open(StrSql, Constr);
varRecordSet.MoveFirst;
RecordVar.OPEN(50001);
REPEAT
varFldCollection := varRecordSet.Fields;
RecordVar.RESET;
RecordVar.INIT;
FOR i := 1 TO RecordVar.FIELDCOUNT DO BEGIN
FieldVar := RecordVar.FIELDINDEX(i);
FieldVar.VALIDATE(varFldCollection.Item(i-1).Value);
END;
RecordVar.INSERT;
cFlag := 1;
varRecordSet.MoveNext
UNTIL varRecordSet.EOF = TRUE;
IF cFlag = 1 THEN
MESSAGE('Data Imported');
varRecordSet.Close;
RecordVar.CLOSE;
CLEAR(varRecordSet);
CLEAR(RecordVar);
Please Help... #-o
While accessing the data from Access i have written the following code in Navision i have two problems in the following code
1) when the data is pulled the first character of the first field is always missing like my first field is Empcode then if code is MP0001 then only P0001 is pulled same is for all the rows.
2) I have three fields in the table both in the Access and the navision table When Ever i write the query 'Select EmpCode, EmpName From Employee' the following error is flashed out "Call to the member Item Failed Fields written the following message: Item Cannot be found in the corresponding collection to the requested name." But it wrks properly when i select all the fields of the table.
My Code is
Constr := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Access To Navision\Amantest.mdb;' +
'User Id=admin; Password=;';
StrSql := 'Select EmpCode, EmpName From Employee';
CREATE(varRecordSet);
varRecordSet.Open(StrSql, Constr);
varRecordSet.MoveFirst;
RecordVar.OPEN(50001);
REPEAT
varFldCollection := varRecordSet.Fields;
RecordVar.RESET;
RecordVar.INIT;
FOR i := 1 TO RecordVar.FIELDCOUNT DO BEGIN
FieldVar := RecordVar.FIELDINDEX(i);
FieldVar.VALIDATE(varFldCollection.Item(i-1).Value);
END;
RecordVar.INSERT;
cFlag := 1;
varRecordSet.MoveNext
UNTIL varRecordSet.EOF = TRUE;
IF cFlag = 1 THEN
MESSAGE('Data Imported');
varRecordSet.Close;
RecordVar.CLOSE;
CLEAR(varRecordSet);
CLEAR(RecordVar);
Please Help... #-o
Aman Kumar Gupta
0
Answers
-
have you tried to use connection string Microsoft Access ODBC Driver instead of Microsoft Jet OLE DB 4.0?
Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;0 -
you could also change the Select statement and add a blank field to the begging of the fields.0
-
Thanks For the Reply, But i have tried the code u mentioned its not working. And for your second solution that i should create a blank field plz tell me is this a comman problem or my code is creating a problem...
Thanks,Aman Kumar Gupta0 -
No this is not a common problem. This is the first time I've heard of it. ADO works pretty good.
Have you tried to do it in VB and see if you get the same problem?0 -
Thanks For the quick reply, I appreciate you for giving me the time...
Well that i will check and tell you.. But i am facing a serious problem while importing the Decimal fields it is saying that this data type is not supported by the C/Side is it that we cant pull the decimal values from the Access.. Please don't say no. I need 2 pull the decimal, Integer values from Access how could i do so..Aman Kumar Gupta0 -
search also before the forum, there are some topics about Access.
viewtopic.php?f=5&t=2407
viewtopic.php?f=23&t=28733 <-- Ui, it's a post from you ......Do you make it right, it works too!0 -
While accessing the decimal field the program gives the error Error "the datatype is not supported by C/SIDE You can Access data From anyone of the following datatypes VT_VOID,VT_I2,VT_I4,.....VT_BOOL". I have made the decimal field in Access but cannot pull its data in the Nav.. This error comes when i etract the decimal value....
FieldVar := RecordVar.FIELDINDEX(1);
FieldVar.VALIDATE(varFldCollection.Item(0).Value);
FieldVar := RecordVar.FIELDINDEX(2);
FieldVar.VALIDATE(varFldCollection.Item(1).Value);
FieldVar := RecordVar.FIELDINDEX(3);
FieldVar.VALIDATE(varFldCollection.Item(2).Value);
FieldVar := RecordVar.FIELDINDEX(4);
FieldVar.VALIDATE(varFldCollection.Item(3).Value);
RecordVar.INSERT;
This is really :bug:ing me....Aman Kumar Gupta0 -
For this you need to change your sql statement cast it as float.
Select EmpCode, EmpName From Employee, cast(MyDecimal, as float) ';0 -
Hi,
I have the table known as Employee with salary as the decimal field so my query should be like this Select EmpCode, EmpName, Salary From Employee Cast(salary, As Float).
I tried this but still not wrking.Aman Kumar Gupta0 -
Try this.
Select EmpCode, EmpName, Cast(salary, As Float) as Salary From Employee
0 -
Hi,
I have already tried this but its wroking perfectly in sql server but when i run this command for the access or through my Navision program its showing the error " Syntax error (missing operator) in the query expression CAST(Salary AS Float)"
ThanksAman Kumar Gupta0 -
Hey !
Final its done actually as i am pulling data from access cast command was nt running properly then i used CDbl function in my query it worked
'Select EmpCode, EmpName, Cdbl(Salary), Check From Employee'; \:D/
thanks for the support...Aman Kumar Gupta0 -
that's good to hear.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