mibuso.com

Microsoft Business Solutions online community
It is currently Thu Apr 17, 2014 8:41 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 60 posts ]  Go to page Previous  1, 2, 3, 4
Author Message
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Wed Jan 04, 2012 9:18 am 
Offline

Joined: Wed Sep 29, 2010 3:18 am
Posts: 764
Country: Australia (au)
can we use this to automate customer master from database 1 to another databases?
thanks


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Wed Jan 04, 2012 9:25 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Sep 02, 2008 8:37 am
Posts: 4532
Location: Hyderabad
Country: India (in)
jr macarilay wrote:
is there a way to use Windows Authentication with this?

i tried 'Integrated Security=TRUE' but im getting an error


Use

Quote:
ConnectionString := 'Data Source='+ServerName+';'
+ 'Initial Catalog='+NavDb+';'
+ 'Trusted_Connection=True;';

_________________
-Mohana
http://mibuso.com/blogs/mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav


Top
 Profile  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Sun Jan 08, 2012 3:00 pm 
Offline

Joined: Wed Sep 29, 2010 3:18 am
Posts: 764
Country: Australia (au)
hi all...
now i can execute from nav to SP
i want to execute insert,update (and maybe delete row) to several databases
so... how do i send the parameter (example : customer no, customer name, etc) become parameter in my storeprocedure?
thanks


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Mon Jan 09, 2012 4:01 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7649
Location: Milan
Country: Italy (it)
Give this a look : http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Sat Jun 29, 2013 8:00 am 
Offline

Joined: Fri Mar 23, 2012 8:01 pm
Posts: 91
Country: United Arab Emirates (ae)
I am able to get the records from ADO Connection.
but unable to see the values or Store values in my NAV table because data type conversion.

Please this code and adivce.

Code: Select all
SQLString:=' SELECT     PName, DeptName, PNumber,AttDate, MIN(CAST(RsTime AS Datetime)) AS InTime,'+
           'MAX(CAST(RsTime AS Datetime)) AS OutTime,'+
           'DATEDIFF(minute, MIN(CAST(RsTime AS Datetime)),MAX(CAST(RsTime AS Datetime))) AS Total'+
           ' FROM  dbo.TMP_ATTENDANCE_DAILY2'+
           ' where attDate >='  +SingleQuote+FORMAT(firstdate,0,'<Year4>-<Month,2>-<Day,2>')+SingleQuote+
           ' AND '+' AttDate <='+SingleQuote+FORMAT(Lastdate,0,'<Year4>-<Month,2>-<Day,2>') +SingleQuote+
           ' AND PName='+SingleQuote+kmtext+SingleQuote+''+
           ' GROUP BY PName,AttDate, DeptName, PNumber ORDER BY AttDATE';




VARSQLString:=SQLString;
MESSAGE(SQLString);
ADORecSet:=ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
ADORecSet.MoveFirst;

REPEAT

KinTime:= ADORecSet.Fields.Item('InTime').Value;
kOutTme:= ADORecSet.Fields.Item('OutTime').Value;

Myvar := Myvar +1;
AttendanceData.INIT;
AttendanceData.EntryNum:= vlastno;
AttendanceData.PName:= ADORecSet.Fields.Item('pName').Value;
AttendanceData.DeptName:= ADORecSet.Fields.Item('DeptName').Value ;
AttendanceData.PNumber:= ADORecSet.Fields.Item('PNumber').Value;
AttendanceData.Date:= ADORecSet.Fields.Item('attDate').Value ;
IF KinTime <> 0D THEN
BEGIN
AttendanceData.Intime:= CREATEDATETIME(KinTime,0T);
AttendanceData.Outtime:= CREATEDATETIME(kOutTme,0T);
AttendanceData.total:= ADORecSet.Fields.Item('Total').Value ;
END;
AttendanceData.INSERT;
vlastno:=vlastno+1;
ADORecSet.MoveNext();
UNTIL ADORecSet.EOF();


Attachments:
NavDataTypeError.jpg
NavDataTypeError.jpg [ 39.84 KiB | Viewed 1572 times ]
Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Wed Jul 03, 2013 3:11 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7649
Location: Milan
Country: Italy (it)
You should first check out which field it is that generates the error. Probably an integer or date or datetime field.

If it is total, try to CAST it as integer.
If it is a date/datetime, CAST it to a VARCHAR (in a format that you define like "YYMMDD HHMMSSNNN" so you are always sure you get that format), receive it in a text-variable in NAV and then convert it backup to date/datetime.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Mon Sep 30, 2013 3:01 pm 
Offline

Joined: Wed Jan 02, 2013 5:12 pm
Posts: 37
Country: United Kingdom (uk)
Hi Am getting a strange compilation error when I try this

My code is as below for the recordset. As soon as I try to save the report that calls the stored proc I get

The C/AL obect was not saved in compiledform. Compile it from the Object Designer and run it again
Internal Compiler error.Error Code= 00000019.

If I comment out the line below it compiles and saves OK

NewDocNo := Format(ADOrecordset.Fields.Item('DocumentNo').Value);

Any Ideas?

Ewan

Code: Select all
ADOconn.Open(ConnectionString);

IF ISCLEAR(ADOcommand) THEN CREATE(ADOcommand);

varConn := ADOconn;

ADOcommand.ActiveConnection := varConn;
ADOcommand.CommandText :='Interco';
ADOcommand.CommandType := 4;
ADOcommand.CommandTimeout := 0;

ADOparameter:=ADOcommand.CreateParameter('@date',135,1,0,InterCoDate);
ADOcommand.Parameters.Append(ADOparameter);
ADOcommand.Execute;

MESSAGE('Get Records');

IF ISCLEAR(ADOrecordset) THEN
CREATE(ADOrecordset);

ADOrecordset.ActiveConnection := varConn;
ADOrecordset.Open(ADOcommand);
CurrDocNo :='';
WHILE NOT ADOrecordset.EOF DO BEGIN
NewDocNo := Format(ADOrecordset.Fields.Item('DocumentNo').Value);
IF (NewDocNo <> CurrDocNo) THEN
BEGIN
CurrDocNo := NewDocNo ;
MESSAGE(NewDocNo);
END;
ADOrecordset.MoveNext;
END;


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Tue Oct 01, 2013 1:28 pm 
Offline

Joined: Wed Jan 02, 2013 5:12 pm
Posts: 37
Country: United Kingdom (uk)
Resolved this in fact I am surprised anyone got this to work at all as you cannot access the Record set directly you have to access it by using the ADO stream object

for further info see this post

http://navitips.blogspot.co.uk/2012/05/ ... -data.html =D>

Regards


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Tue Oct 01, 2013 2:52 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7649
Location: Milan
Country: Italy (it)
try something like this:

Name DataType Subtype Length
ADOStream Automation 'Microsoft ActiveX Data Objects 6.0 Library'.Stream



ADOGetFieldValue(ItxtFieldName : Text[1024]) OtxtValue : Text[1024]
// ADOGetFieldValue
// Gets the value of a field in current record
// IMPORTANT : a record must be available at the moment by a ADOFind or ADONext
// PARAMETERS:
// ItxtFieldName : the name of the field to get
// RETURN-VALUE : the value as text. All types of values will be returned as text

IF FORMAT(ADORecSet.Fields.Item(ItxtFieldName).ActualSize) = FORMAT(0) THEN // NULL-value
EXIT('');

OtxtValue := '';

ADOStream.Open;
ADOStream.WriteText(ADORecSet.Fields.Item(ItxtFieldName).Value);
ADOStream.Position:= 0;
OtxtValue := ADOStream.ReadText;
ADOStream.Close;

EXIT;

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Wed Oct 02, 2013 9:04 am 
Offline

Joined: Wed Jan 02, 2013 5:12 pm
Posts: 37
Country: United Kingdom (uk)
Thanks

forgot about checking for null field values :oops:


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Tue Oct 08, 2013 9:57 am 
Offline

Joined: Tue Oct 08, 2013 9:53 am
Posts: 2
Country: India (in)
Dear Waldo,
Thank you very much for sharing this valuable information..
Now I am able to see the result in message when I run report in C/AL.
I need to know how do I print the result on report?


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Tue Oct 08, 2013 12:13 pm 
Offline

Joined: Tue Oct 08, 2013 9:53 am
Posts: 2
Country: India (in)
Dear Waldo,

Thank you very much for sharing valuable info.

Now I am able to get message with required information.

Please let me know how I will be able to print the data on the nav report of which SQL returned.

Thanks,
Nitin


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Tue Oct 08, 2013 12:21 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Sep 02, 2008 8:37 am
Posts: 4532
Location: Hyderabad
Country: India (in)
After the recordset is loaded, we can loop that recordset and put (in my case) the results into a temp table.

Code: Select all
WHILE NOT lADORecordset.EOF DO BEGIN 

ptmpGlobalInventoryBuffer.INIT;
ptmpGlobalInventoryBuffer."Item No" := lADORecordset.Fields.Item('ItemNo').Value;
ptmpGlobalInventoryBuffer."Company Name" := lADORecordset.Fields.Item('CompanyName').Value;
ptmpGlobalInventoryBuffer."Location Code" := lADORecordset.Fields.Item('LocationCode').Value;
ptmpGlobalInventoryBuffer."Location Name" := lADORecordset.Fields.Item('LocationName').Value;
ptmpGlobalInventoryBuffer."Main Location" := lADORecordset.Fields.Item('MainLocation').Value;
ptmpGlobalInventoryBuffer.INSERT;

lADORecordset.MoveNext;

END;


You need to take a integer dataitem and repeat the temp table and show the data in sections..

_________________
-Mohana
http://mibuso.com/blogs/mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav


Top
 Profile  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Mon Oct 14, 2013 1:33 pm 
Offline

Joined: Wed Jan 02, 2013 5:12 pm
Posts: 37
Country: United Kingdom (uk)
Hi

Is it possible to run the ado command asynchronously?

The reason being I could then set up a progress bar using a timer that updates until the stored procedure has finished executing.

Anyone out there attempted to do this?


Top
 Profile E-mail  
 
 Post subject: Re: Using ADO & Stored Procedures in C/SIDE
PostPosted: Wed Oct 30, 2013 6:57 pm 
Offline

Joined: Tue Apr 11, 2006 10:00 pm
Posts: 1315
Location: Vienna
Country: Austria (at)
I made a bit of a framework for this, using stored procs with parameters, really clean, I use it for reading in an external webshop, works well in Classic: http://navitips.blogspot.co.at/2012/05/ ... -data.html

So you end up with code like this:

Code: Select all
InitAll;

ConnString := 'Provider=SQLOLEDB;Data Source=NAVISION;' +
         'Initial Catalog=NAV_INPUT;User ID=NAVREADER;Password=wonttellya;';

SPName := 'sp_readtest1';

ParamValues[1] := '3';

RunADOStoredProcRetRows;
IF NOT ADORecSet.EOF THEN
REPEAT
  MESSAGE(GetFieldStr('field1')+'|' + GetFieldStr('field2'));
  ADORecSet.MoveNext;
UNTIL ADORecSet.EOF;

CloseAll;


Really clean right?

Now would someone PLEASE upgrade it to 2013 with .NET please? I have no clue about ADO.NET...

_________________
Sorry, no support using PM, e-mail or MSN - please use this forum.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 60 posts ]  Go to page Previous  1, 2, 3, 4

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: