Using ADO & Stored Procedures in C/SIDE

kriki

My error.
It should be:

IADOCommand.CommandText := 'SELECT TOP 1 NULL FROM asset_class WHERE class_name = ''' + FAClassRec.Code + '''';

(there was a '1' too many).

kwalker0412

That worked in so far as it compiled and ran.
I am still having troube with the RecordCount variable.
But if I check the EOF, I get the result I need, so I am good! \:D/

And I had no trouble INSERTing into the SQL table when I needed to.

Thanks for all your help. =D>
Kelly

Waldo

ADO ... you gotta love it

ara3n

I just wanted to add some additional suggestion.

When using recordset variable. Add it as local otherwise you will get an error for duplicate Variable.

Second. When running a sql statement that returns a record set, for Decimal values etc, you will get an error

This message is for C/AL programmers:
This data type is not supported by C/SIDE. You can access data of any of the following data types: VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR and VT_BOOL"

You can change you sql statement and use cast function

Select Amount 
from Customer

to

Select cast (Amount as float)
from Customer

Waldo

Thanks, Rashed. This is valuable info ... .
May be you can add them to my blog as well?

ara3n

Done.

triplechoco

very interesting post! thanks a lot for sharing it with us..


commission de surendettement - commission de surendettement, vous pouvez demander un dossier de surendettement.

jversusj

kwalker0412 wrote:

That worked in so far as it compiled and ran.
I am still having troube with the RecordCount variable.
But if I check the EOF, I get the result I need, so I am good! \:D/

And I had no trouble INSERTing into the SQL table when I needed to.

Thanks for all your help. =D>
Kelly

i had this same problem. i added 1 line to your code and it appears like the count works.

ADORecordSet.ActiveConnection := VarActiveConnection;
ADORecordSet.CursorType       := 3;   //this sets the cursor to static, according to devguru, the count only works for static cursor
ADORecordSet.Open(ADOCommand);

DRB

Hi,
Can we use Windows Authentication also in this:

lADOConnection.ConnectionString:=GetConnectionString(precDBServer."Server Name", precDBServer."Database Name", precDBServer.Login, precDBServer.Password);

In the above code, what should be changed for using Windows Authentication?

Waldo

Check out www.connectionstrings.com ..

May be this is a good alternative connectionstring for you:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

DRB

Thanks Waldo, that worked.

Now I am getting two other errors:
1.

---

Microsoft Dynamics NAV

---

This message is for C/AL programmers:

The call to member ConnectionString failed. ADODB.Connection returned the following message:
Operation is not allowed when the object is open.

---

OK

---

2.cannot change the activeconnection property of a recordset object which has a command object as its source.


Following is the code that I am using:

IF ISCLEAR(lADOConnection) THEN BEGIN
CREATE(lADOConnection);
lADOConnection.ConnectionString:=GetConnectionString('ServerName', 'DatabaseName', TRUE);
lADOConnection.Open;
END;
IF ISCLEAR(lADOCommand) THEN
CREATE(lADOCommand);

lvarActiveConnection := lADOConnection;
lADOCommand.ActiveConnection := lvarActiveConnection;

lADOCommand.CommandText :=SQLString(SelectionType);
lADOCommand.CommandType := 1;
lADOCommand.CommandTimeout := 0;
lADOCommand.Execute;


IF ISCLEAR(lADORecordset) THEN
CREATE(lADORecordset);

lADORecordset.ActiveConnection := lvarActiveConnection;
lADORecordset.Open(lADOCommand);
I:=0;
WHILE NOT lADORecordset.EOF DO BEGIN
I+=1;
IF I=1 THEN
InsertCaptionIntoExcel(SelectionType);
InsertDataIntoExcel(SelectionType);
lADORecordset.MoveNext;
END;
EndOfLoop;
lADOConnection.Close;
CLEAR(lADOConnection);

Any help will be greatly appreciated.

mabl4367

Hi Waldo!

I tested your examples and everything works very well.

Now that I try to use this in my own way I run into som trouble.

I wanted to declare lADORecordSet as a global variable in a report that is going to get som data from another database using an sp.

The problem is that as soon as i declare any global variable of type Automation, Microsoft ActiveX Data Objects 2.8 Library'.Recordset, I can no longer compile the report without geting an error message telling me that the variable is declared more then once.

I have tried creating a new report with nothing in it except for this global variable and I get the same error.

I also tried exporting this report in txt-format. When I open up the file in a text editor the variable is decladerd only once. Although there are some event triggers declared that seams to have something to do with the variable.

OBJECT Report 51086 TEST SQL SP CALL 2
{
  OBJECT-PROPERTIES
  {
    Date=11-08-18;
    Time=09:17:50;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      lADORecordSet@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Recordset";

    EVENT lADORecordSet@1000000000::WillChangeField@9(cFields@1000000003 : Integer;Fields@1000000002 : Variant;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::FieldChangeComplete@10(cFields@1000000004 : Integer;Fields@1000000003 : Variant;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::WillChangeRecord@11(adReason@1000000003 : Integer;cRecords@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::RecordChangeComplete@12(adReason@1000000004 : Integer;cRecords@1000000003 : Integer;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::WillChangeRecordset@13(adReason@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::RecordsetChangeComplete@14(adReason@1000000003 : Integer;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::WillMove@15(adReason@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::MoveComplete@16(adReason@1000000003 : Integer;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::EndOfRecordset@17(VAR fMoreData@1000000002 : Boolean;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::FetchProgress@18(Progress@1000000003 : Integer;MaxProgress@1000000002 : Integer;adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

    EVENT lADORecordSet@1000000000::FetchComplete@19(pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pRecordset@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset");
    BEGIN
    END;

Can anyone shed some light on this problem?

rmv_RU

ara3n wrote:

I just wanted to add some additional suggestion.

You can change you sql statement and use cast function

Select Amount 
from Customer

to

Select cast (Amount as float)
from Customer

Because result of calculation is unpredictable I never use float datatype.
Compare:

declare @f1 float, @f2 float, @f3 float

set @f1=12345678912345692
set @f2=1
set @f3=1

select @f1, @f1+@f2+@f3, @f2+@f3+@f1

Using money datatype is better way.

jr_macarilay

is there a way to use Windows Authentication with this?

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

julkifli33

can we use this to automate customer master from database 1 to another databases?
thanks

mohana_cse06

jr macarilay wrote:

is there a way to use Windows Authentication with this?

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

Use

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

julkifli33

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

kriki

Give this a look : http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx

kmkaot

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.

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();

kriki

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.

Ewan

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

 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;

Ewan

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

kriki

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;

Ewan

Thanks

forgot about checking for null field values :oops:

nitinagrawal003

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?

nitinagrawal003

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

mohana_cse06

After the recordset is loaded, we can loop that recordset and put (in my case) the results into a temp table.

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..

Ewan

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?

Miklos_Hollender

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:

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...

matthewtaing

Hey guys,

Encountered an issue using ADO where my stored procedure is getting data from a linked server and it keeps dropping connection after idling for some time and gives an error message that says Login failed for user 'NT authority\anonymous logon' when I try to run my codeunit.

If I go back to SQL management studio and and run the Test connection then my codeunit works again. The following is the t-sql that I run to create the linked server.

EXEC master.dbo.sp_addlinkedserver @server = N'linkedservername\linkedserver_SQL', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedservername\linkedserver_SQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedservername\linkedserver_SQL',@useself=N'True',@locallogin=N'domain\username',@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'linkedservername\linkedserver_SQL', @optname=N'use remote collation', @optvalue=N'true'

The following is the connection string I used for my ADOConnection.

  ConnectionString:='Provider=SQLOLEDB;'
    + 'Data Source='+ServerName+';'
    + 'Initial Catalog='+DBName+';'
    + 'Integrated Security=SSPI;'
    + 'Trusted_Connection=True;';

Appreciate it if any of you found the solution to this issue.

Thanks