Using ADO & Stored Procedures in C/SIDE

WaldoWaldo Member Posts: 3,412
edited 2015-07-15 in NAV Tips & Tricks
Source: my blog
For a better edited version of this text I advice you to go to my blog. :wink:


Recently, I had to do a small project. A client of us wanted to see the global inventory of all its companies. Furthermore, the companies were also spread over multiple databases. They wanted quick answers on questions like "What is the inventory of this item in any location in any company in any database". Now, I'm not going to tell you how I solved the case. I just wanted to highlight some interesting stuff that I used.

Some basics about using ADO in NAV.

ADO (ActiveX Data Objects) is described as "ADO is designed to provide a consistent way of accessing data regardless of how the data are structured. ". It's actually automation that you can use (should be installed together with windows - but I'm not sure) to access about any kind of database. It's generally used by developers in C/SIDE to access data in another database then the current database.

It's basically 3 steps:

1. Make a connection to the database, using a "connectionstring"
http://www.connectionstrings.com/ is a very helpful website to help you compose your connectionstring. Usually, I create a seperate setup table with the parameters needed to create a connectionstring.

In C/SIDE, you can create a connection like this:
IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection); 
lADOConnection.ConnectionString:=GetConnectionString(precDBServer."Server Name", precDBServer."Database Name", precDBServer.Login, precDBServer.Password); 
lADOConnection.Open;
LADOConnection is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Connection". You also see that I compose my connectionstring in a seperate function:
GetConnectionString(...) : Text[1024] 

ltxtConnectionString:='Driver={SQL Server};' 
    + 'Server='+pcodServerName+';' 
    + 'Database='+pcodDatabaseName+';' 
    + 'Uid='+pcodUserID+';' 
    + 'Pwd='+pcodPassword+';'; 

EXIT(ltxtConnectionString);


2. Do your thing in the database (read, write, loop data, call SP, grant permissions,...) ... usually using SQL commands.

We will go into this deeper further down the road.

3. Close your connection

This is how I close my connection:
lADOConnection.Close; 
CLEAR(lADOConnection);

If you're a dedicated C/SIDE developer, you see that this is not usual. Creating a connection? Closing a connection?? In C/SIDE, you're already connected by just declaring a record variable - by sorts of speaking. That's because the native client connects with the database before you can start working - and therefore, you're always connected . It's logical, I know, but anyway ...

What could it be useful for?

So, what can we do in for example in the database. Well. A number of things. From the top of my head:

Creating company-specific views when a new company is created
Create data in a queue table
Compose a SQL String, execute that SQL String and givie back the results ... .
...
It all comes down on executing a stored procedure in a SQL Server database. If you can do that, then the possibilities is limited to what you can do with Stored Procedures. And there is not much limitation to that . About everything you can do in T-SQL, you can do with SP's!

So, let me try to explain all this by using a few examples:

Creating company-specific views when a new company is created
First of all, I created a stored procedure in SSMS (SQL Server Management Studio) like this (I added some comments to make things clearer):
CREATE PROCEDURE [dbo].[SP_CreateView_ItemLocation] 
     @CompanyName VARCHAR(30) 
AS 
BEGIN 
     SET NOCOUNT ON;

DECLARE @SQLString NVARCHAR(MAX) 

--If the view already exists, drop the view 

SET @SQLString = 'IF OBJECT_ID (''['+@CompanyName+'$SP_ItemLocation]'', ''view'') IS NOT NULL DROP VIEW ['+@CompanyName+'$SP_ItemLocation]' 

EXEC sp_executesql @SQLString 

--assemble the SQLString (including the companyname) 

SET @SQLString = 
     'CREATE VIEW [dbo].['+@CompanyName+'$SP_ItemLocation] 
     AS 
     SELECT DISTINCT ''ILE'' AS TableName, [Item No_] AS ItemNo, [Location Code] AS LocationCode, 
     ['+@CompanyName+'$Location].Name AS LocationName, ['+@CompanyName+'$Location].[Main Location] AS MainLocation 
     FROM dbo.['+@CompanyName+'$Item Ledger Entry], ['+@CompanyName+'$Location] 
     where dbo.['+@CompanyName+'$Item Ledger Entry].[Location Code] = ['+@CompanyName+'$Location].Code' 

print @SQLString 
     --this "print " is optionally - it is useful when you're debugging your SP 
     --in SSMS, because it shows the SQLString that you have been building. 

exec sp_executesql @SQLString 

END
Then, I wrote some C/AL to call this SP (I excluded the connection-stuff mentioned above. Note that a connection is still necessary):


CreateCompanyViews() 

... <Open your connection here (see above)> 

IF ISCLEAR(lADOCommand) THEN 
CREATE(lADOCommand);    

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

lADOCommand.CommandText := 'SP_CreateView_ItemLocation';
lADOCommand.CommandType := 4; 
lADOCommand.CommandTimeout := 0;    

lADOParameter:=lADOCommand.CreateParameter('@CompanyName', 200, 1, 30,COMPANYNAME); 
lADOCommand.Parameters.Append(lADOParameter);    

lADOCommand.Execute; 

... <Close your connection here (see above)>
You see that I'm using LADOCommand, which is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Command" and LADOParameter is a "'Microsoft ActiveX Data Objects 2.8 Library'.Parameter". Some explanation is required, I guess:

A command needs a Command Type. This site is useful to help you with the different command types. You see we used "4", being a stored procedure.
To let the SP know for what company we have to create the view, we have to send a parameter. Therefor, we use the ADO parameter automation mentioned above. To help you with this, this site is useful. You have to define the direction of the parameter (input, output, ...) and the data type and its length. In my example, I used an input parameter, varchar as datatype and length 30.
Note that we used lvarActiveConnection. This variable is of the type Variant. You have to give the active connection to the command variable, and this is the way to do it:
Load it into your variant like: lvarActiveConnection := lADOConnection;
Use it in your command like: lADOCommand.ActiveConnection := lvarActiveConnection;
To conclude, I added this code in Codeunit 2 at the end of the OnRun trigger. The code in this codeunit is executed when a company is created.
cduMyADOSamplesMgt.CreateCompanyViews;
I added this after the COMMIT, because I want to make sure that my company is created, no matter if my view was successfully created or not.

Compose a SQL String, execute that SQL String and give back the results ... .
This time, I went a little bit further , because now I have an output result set .

First, my stored procedure:
CREATE PROCEDURE [dbo].[SP_LoadInventory] 
     @ItemNo AS VARCHAR(20) 
AS 

BEGIN 
     SET NOCOUNT ON;    
     DECLARE @CompanyName VARCHAR(30) 
     DECLARE @OrigCompanyName VARCHAR(30) 
     DECLARE @SQLString NVARCHAR(MAX) 
     DECLARE @Counter INT 

DECLARE curCompany CURSOR FOR SELECT [Name] as CompanyName from dbo.Company 
OPEN curCompany 

SET @Counter = 0 
SET @SQLString='' 

FETCH NEXT FROM curCompany INTO @CompanyName 
-- Begin looping all companies in the database 

WHILE @@FETCH_STATUS = 0 
BEGIN 
     -- Converting the wonderful NAV-supported-but-best-not-used-in-SQL characters 
     SET @OrigCompanyName = @CompanyName 
     SET @CompanyName = REPLACE(@CompanyName,'.','_'); 
     SET @CompanyName = REPLACE(@CompanyName,'"','_'); 
     SET @CompanyName = REPLACE(@CompanyName,'\','_'); 
     SET @CompanyName = REPLACE(@CompanyName,'/','_'); 
     SET @CompanyName = REPLACE(@CompanyName,'''','_');    

-- Only put the UNION in between of two SELECT statements 
IF @Counter > 0 
BEGIN 
     SET @SQLString = @SQLString + 'UNION' 
END 
SET @SQLString = @SQLString + 
     ' 
     SELECT DISTINCT ''' + @OrigCompanyName + ''' AS CompanyName,ItemNo, LocationCode, LocationName, MainLocation 
     FROM dbo.[' + @CompanyName + '$SP_ItemLocation] a 
     WHERE ItemNo = ''' + @ItemNo + ''' 
     ' 
FETCH NEXT FROM curCompany INTO @CompanyName 

SET @Counter = @Counter + 1 

END; 

print @SQLString 

EXEC sp_executesql @SQLString         

CLOSE curCompany 
DEALLOCATE curCompany 

END
This is just a simple stored procedure that is going to UNION a SQL Statement for each company. In C/AL, you can call this Stored Procedure like:
... <Open your connection here> 

IF ISCLEAR(lADOCommand) THEN 
CREATE(lADOCommand);    

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

lADOCommand.CommandText := 'SP_LoadInventory'; 
lADOCommand.CommandType := 4; 
lADOCommand.CommandTimeout := 0;    

lADOParameter:=lADOCommand.CreateParameter('@ItemNo', 200, 1, 20,pcodItemNo); 
lADOCommand.Parameters.Append(lADOParameter);    

lADOCommand.Execute; 

IF ISCLEAR(lADORecordset) THEN 
     CREATE(lADORecordset); 

lADORecordset.ActiveConnection := lvarActiveConnection; 
lADORecordset.Open(lADOCommand);    

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; 

... <Close your connection here>
May be here also some explanation:

Note that we used lvarActiveConnection to load the active connection into our recordset variable as well.
The execution of the SP is the same as before, but now you expect something back. That's why you're loading the resultset into a recorset ('Microsoft ActiveX Data Objects 2.8 Library'.Recordset) with the statement: lADORecordset.Open(lADOCommand);
After the recordset is loaded, we can loop that recordset and put (in my case) the results into a temp table.
Not that you can move through your recordset with MoveNext, MoveFirst, MoveFirst, MoveLast. You can check if you're at the end with EOF (End Of File).
Using an output parameter in a Stored Procedure
OK, taking a little step back. Suppose we want to create a SP with a simple output parameter like a decimal, boolean or whatever. So you won't get back a recordset like above, but just an output parameter. Not really creative, but a SP with an output parameter looks something like this:
CREATE PROCEDURE [dbo].[WALDO_test] 
     @Result AS VARCHAR(20) OUTPUT 
AS 
BEGIN 
     SET @Result = 'Test Output'; 
END
You define your outputparameter with the OUTPUT keyword.

In NAV it could be something like:
... <Open your connection here> 

IF ISCLEAR(lADOCommand) THEN 
     CREATE(lADOCommand); 

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

lADOCommand.CommandText := 'WALDO_test'; 
lADOCommand.CommandType := 4; 
lADOCommand.CommandTimeout := 0;    

lADOParameter:=lADOCommand.CreateParameter('@Result', 200, 2, 20,ltxtResult); 
lADOCommand.Parameters.Append(lADOParameter);    

lADOCommand.Execute; 

//Get your result back from the command variable :
ltxtResult := FORMAT(lADOCommand.Parameters.Item('@Result').Value);    

MESSAGE(ltxtResult); 

... <Close your connection here>
You see that you need an extra statement to pull your result back from the command variable. It's not going to do that for you automatically (allthough you defined your parameter as being "output" (we defined the direction-argument in the CreateParameter-statement as "2")).

One comment might be that you want to use the return value in stead of an output parameter, but keep in mind that the return value of a SP must be "integer".

There are tons of other things you can do with ADO, Stored Procedures,... . I just wanted you to experience how you can use ADO, and what it might be useful for. I'm not saying that this is always the best solution in any case. It's just one of the possibilities where much is possible and flexible solutions can be built with.

Sorry for the somewhat bad editing. I had my hands full with it .

Any comment is highly appreciated.

Eric Wauters
MVP - Microsoft Dynamics NAV
My blog
«13

Comments

  • srinivassrinivas Member Posts: 3
    Dear Waldo,
    Can you explain about PrecDBServer.Is it variable or something.
    please give reply i need it. :shock:
    Srinivas
    Microsoft Dynamics Nav

    ---You are the Creator of our own Destiny
  • WaldoWaldo Member Posts: 3,412
    srinivas wrote:
    Dear Waldo,
    Can you explain about PrecDBServer.Is it variable or something.
    please give reply i need it. :shock:

    precDBServer is a recordvariable of a table with the fields:
    - "Code"
    - "Description"
    - "Server Name"
    - "Database name"
    - "Login"
    - "Password"

    So, it's a table which contains the connection details of servers. Using these fields, I can form a connectionstring.

    Is that somewhat clear :?:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • srinivassrinivas Member Posts: 3
    Thank you Waldo ,

    While i was trying to make connection , I got the following error.
    can u help me .

    Microsoft Business Solutions-Navision
    This message is for C/AL programmers:

    The call to member Open failed. Microsoft OLE DB Provider for ODBC Drivers returned the following message:
    [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

    OK

    I gave valid servername ,userid,password,database name.
    but i don't know why it is showing this error.actually this was the code which i have written.



    IF ISCLEAR(ladoconnection) THEN CREATE (ladoconnection);

    ladoconnection.ConnectionString:=getconnectionstring(precdbserver."server name", precdbserver."database name", precdbserver.login,precdbserver.password);

    ladoconnection.Open;




    getconnectionstring(VAR server1 : Text[300];VAR databasename : Text[300];VAR login : Text[300];VAR password : Text[300]) ltextconnectio



    ltextconnectionstring:='Driver={SQL Server};'
    + 'Server='+ server1 +';'
    + 'Database='+databasename+';'
    + 'Uid='+login+';'
    + 'Pwd='+password+';';

    EXIT(ltextconnectionstring);
    Srinivas
    Microsoft Dynamics Nav

    ---You are the Creator of our own Destiny
  • rajpatelbcarajpatelbca Member Posts: 178
    great idea for interact with SQL Server.

    Thanks =D>
    Experience Makes Man Perfect....
    Rajesh Patel
  • John_ReinholdJohn_Reinhold Member Posts: 19
    Hello, Waldo

    This is very interesting and useful. I have it working fine.

    However, I can not get it to work, when the Stored Porcedure is made with an output parameter as this:
    ALTER PROCEDURE [dbo].[SP_PDIFFTRANSAKTIONHUVUD$uppdatera$IMPL]
       @return_value_argument float(53)  OUTPUT
    AS 
       BEGIN
    	  EXECUTE sysdb.ssma_oracle.db_fn_check_init_package 'fackta05', 'dbo', 'SP_PDIFFTRANSAKTIONHUVUD'
          EXECUTE dbo.SP_PDIFFTRANSAKTIONHUVUD$satt_status_start
    		INSERT INTO newstorefp.dbo.PDIFFTRANSAKTIONHUVUD (
    

    This is how I'm setting up parameters and doing the execution:
    _ActiveConnection := _ADOConnection;
    _ADOCommand.ActiveConnection := _ActiveConnection;
    _ADOCommand.CommandText := Text004;
    _ADOCommand.CommandType := 4;
    _ADOCommand.CommandTimeout := 0;
    _ADOParameter := _ADOCommand.CreateParameter('@return_value_argument2',5,2,64);
    _ADOCommand.Parameters.Append(_ADOParameter);
    _ADOCommand.Execute;
    

    Of course the open connection and other stuff are working. The only thing, I can't figure out, is the line:
    _ADOParameter := _ADOCommand.CreateParameter('@return_value_argument2',5,2,64);
    

    I've tried a lot of possibilities. But I'm getting confused understanding the setup of SQL data types.

    I believe that the error
    An exeption was raised in method Append. The OLE control or Automation server has returned error (HRESULT) -2147352567. The component did not provide the exception description.
    is because data type mismatch. But how do I set the _ADOParameter to hold a float(53)? To change the SPs parameter data type has impact on other systems, so it is not easy to do this.

    I'm used to code NAV but not SQL.
    Regards,
    John Reinhold
  • WaldoWaldo Member Posts: 3,412
    Did you try it with different datatypes? 14 (Decimal) or 4 (Single precision floating point)?

    And did you try it without setting the size ? Or by setting 53 as size?

    Or something like
    _ADOParameter := _ADOCommand.CreateParameter('@return_value_argument2',5,2,53,decMyResultParameter);

    Or, if possible, change the float to a text or decimal, which is (more) compliant to C/SIDE?

    Just a few ideas from the top of my head... :-k

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • bobyboby Member Posts: 78
    Hello everybody,
    It's a great idea to use the "tiers" part of SQL to generate the data via stored procedure..

    With your approach, do you think it's possible (and relevant) to read what the stored procedure is sending back, in an instream to be treated in an xmlport??
    With stored procedure, you can send back XML by using the FORXML statement.. It's not a recordset anymore and I don't know how to get it back, but it's valid xml.

    ideas:?: :?: :?: :?:
  • sixzagsixzag Member Posts: 2
    Hello, Waldo

    Your post is very interesting thank :D

    I try to make one connection with ado but I meet some problem:

    I have one sql database with 4 record inside and I want to put this 4 record in one temporary Navision table. I try to follow your instruction, but when I execute my code only the first record is put i my Navision table. I made a LADORecordset.RecordCount(); and Navision show me -1 ???? ](*,)

    I don't know if my T-sql or my navision code is wrong.

    My T-sql
    create procedure [dbo].[SP_ProjetcView]
    as
    begin
       set nocount on
    
    declare project cursor for select Dossier
    from [dbo].[tableVendor]
    
    open project
    fetch next from project 
    
    while  @@fetch_status = 0
    begin
    
    fetch next from project 
    end
    
    close project
    deallocate project
    
    end
    

    My nav code
    
    CreatConnexion();
    
    IF ISCLEAR(LADOCommand) THEN
      CREATE(LADOCommand);
    
    lvarActiveConnection := LADOConnection;
    LADOCommand.ActiveConnection := lvarActiveConnection;
    
    LADOCommand.CommandText := 'SP_ProjetcView';
    LADOCommand.CommandType := 4;
    LADOCommand.CommandTimeout := 0;
    
    LADOCommand.Execute;
    
    IF ISCLEAR(LADORecordset) THEN
      CREATE(LADORecordset);
    
    LADORecordset.ActiveConnection := lvarActiveConnection;
    LADORecordset.Open(LADOCommand);
    
    WHILE NOT LADORecordset.EOF DO BEGIN
    
      RecordProject.INIT;
      RecordProject.dossier := LADORecordset.Fields.Item('Dossier').Value;
      RecordProject.INSERT();
    
    LADORecordset.MoveNext;
    
    END;
    
    CloseConnexion();
    
    

    Do you have any idea about the problem???
  • WaldoWaldo Member Posts: 3,412
    If I interprete your code correctly, you don't need a Stored Procedure, and definitely not a cursor.

    I think, if you just put "select Dossier from [dbo].[tableVendor]" in your SP would do the trick.

    Also, you can avoid using an SP and just use the select statement as commandtext (and off course another command type (1)).

    Didn't test this though ... good luck.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • sixzagsixzag Member Posts: 2
    I just put "select Dossier from [dbo].[tableVendor]" in my SP and it work very well.
    :D

    Thank you very much
  • WaldoWaldo Member Posts: 3,412
    No prob 8)

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • kwalker0412kwalker0412 Member Posts: 11
    Waldo,
    in a previous message you say:
    Also, you can avoid using an SP and just use the select statement as commandtext (and off course another command type (1)).

    Is there any good documentation for this? I am updating a third party SQL database from Navision. I have it working (thanks for your help), using SP's I created in that SQL database. But I would prefer to not have to do anything in the other product just keep all the code in Navision.
    I would need to do a SELECT and if the COUNT(*) is 0, do an INSERT.
    When I try to do the SELECT statement using command type 1, I get errors...
    Any help would be greatly appreciated.

    Kelly
  • WaldoWaldo Member Posts: 3,412
    Well, if you're working with CommandText, I wouldn't know how to receive a return value to know whether COUNT(*) returns a value or not.

    So in that case, I would choose for an SP... .

    But May be if you return it into e recordset, read the first value of the first column (that should be your count(*))?

    Can you put your code here?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • kwalker0412kwalker0412 Member Posts: 11
    Could I get a set of records that I can loop thru in Navision? And if I can't find the record do an INSERT?
  • WaldoWaldo Member Posts: 3,412
    Indeed, something like that. Similar like working with an SP that returns a recordset...

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • kwalker0412kwalker0412 Member Posts: 11
    sorry I answered too quickly to the last post...
    My first issue is when I create a variable for a record set I get an error.

    "The variable LADORECORDSET::WILLCHANGERECOR is defined more than once. An example could be: 'File' exists as both a data type and a global variable."
  • WaldoWaldo Member Posts: 3,412
    Sorry, man, this doesn't tell me much.. :-k

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • kwalker0412kwalker0412 Member Posts: 11
    :oops:
    Sorry, not sure how to explain all this, here is my code.
    If I remove the code between the asterisks and the variable lADORecordset, I can run the codeunit without errors.
    Hopefully this will be more helpful :?:

    //CREATE CONNECTION AND OPEN
    IF ISCLEAR(lADOConnection) THEN
    CREATE(lADOConnection);
    lADOConnection.ConnectionString:= 'Driver={SQL Server}; Server=MDR-LAB; Database=WaspTrackAsset; UID=kw; PWD=kw';
    lADOConnection.Open;

    IF ISCLEAR(lADOCommand) THEN
    CREATE(lADOCommand);

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

    //GET RECORD SET
    lADOCommand.CommandText := 'SELECT * FROM dbo.asset_class';
    lADOCommand.CommandType := 1;
    lADOCommand.CommandTimeout := 0;
    lADOCommand.Execute;

    //**************************************
    IF ISCLEAR(lADORecordset) THEN
    CREATE(lADORecordset);

    lADORecordset.ActiveConnection := lvarActiveConnection;
    lADORecordset.Open(lADOCommand);

    WHILE NOT lADORecordset.EOF DO BEGIN
    message('record from SQL');

    lADORecordset.MoveNext;
    //**********************************************

    //CLOSE CONNECTION
    lADOConnection.Close;
    CLEAR(lADOConnection);


    And here are my global variables:
    lADOConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
    lADOCommand@1000000001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000507-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Command";
    lADORecordSet@1000000003 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Recordset";
    lvarActiveConnection@1000000002 : Variant;
  • WaldoWaldo Member Posts: 3,412
    On first sight, that's also how I intended to do it :|.

    Can you tell me where the debugger errors out, and what the message is (again)?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • kwalker0412kwalker0412 Member Posts: 11
    I cannot debug as this will not compile, the error message when I try to compile is:

    "The variable LADORECORDSET::WILLCHANGERECOR is defined more than once. An example could be: 'File' exists as both a data type and a global variable."


    I was thinking that I defined my lADORecordset variable wrong but don't know how else I would define it....
  • WaldoWaldo Member Posts: 3,412
    that seems OK.
    Can you check if there is a double name present (locals and globals perhaps?)?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • kwalker0412kwalker0412 Member Posts: 11
    Okay, found the issue. I was using 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset and should have been using 'Microsoft ActiveX Data Objects Recordset 2.8 Library'.Recordset. Now I compile and run and I get back a record set.

    =D>

    Okay, now on to doing something with the recordset. I'll post again if I run into anything else

    Thanks so much for your help!

    Kelly
  • kwalker0412kwalker0412 Member Posts: 11
    okay, no matter what I do to my select statement, I get a RecordCount of -1 ? Everything compiles and there are no run time errors.

    CODE:
    *********************************
    CLEAR(lADOCommand);
    CREATE(lADOCommand);

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

    //GET RECORD SET
    lADOCommand.CommandText := 'SELECT * FROM asset_class WHERE class_name = ''' + FAClassRec.Code + '''';
    lADOCommand.CommandType := 1;
    lADOCommand.CommandTimeout := 0;
    lADOCommand.Execute;

    CLEAR(lADORecordSet);
    CREATE(lADORecordSet);

    lADORecordSet.ActiveConnection := lvarActiveConnection;
    lADORecordSet.Open(lADOCommand);

    EVALUATE(rcount,FORMAT(lADORecordSet.RecordCount));
    IF rcount > 0 THEN
    MESSAGE('record found')
    ELSE
    MESSAGE('record not found');

    *********************************
    END OF CODE

    If I copy my select statement to the Query Analyzer it works fine too.
    Anything


    Kelly
  • krikikriki Member, Moderator Posts: 9,094
    I think that the RecordCount doesn't work as intended. I noticed that I also get -1 and I get 0 if there are NO records.
    So the -1 just wants to say that there are records, but not how many.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    You could also use
    lADOCommand.CommandText := 'SELECT count(*) FROM asset_class WHERE class_name = ''' + FAClassRec.Code + '''';

    in this case, you get only one record, one field. that fields gives you the number of records.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • krikikriki Member, Moderator Posts: 9,094
    Waldo wrote:
    You could also use
    lADOCommand.CommandText := 'SELECT count(*) FROM asset_class WHERE class_name = ''' + FAClassRec.Code + '''';

    in this case, you get only one record, one field. that fields gives you the number of records.
    But it creates an extra DB-request and if has no good key to use ...

    So if you don't REALLY need the number of records, better avoid this request.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    Reading his code, I had the impression that the only thing he needs IS the number of records ... why getting a complete recordset when you only need one number?

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • krikikriki Member, Moderator Posts: 9,094
    I just read his code, and it seems he just wants to know if there is at least one record.
    In this case:
    IADOCommand.CommandText := 'SELECT TOP1 1 NULL FROM asset_class WHERE class_name = ''' + FAClassRec.Code + '''';
    

    And then test lADORecordSet.RecordCount if it is '-1' or '0'.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • WaldoWaldo Member Posts: 3,412
    true ... :mrgreen:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • kwalker0412kwalker0412 Member Posts: 11
    Thanks for your input. To clarify, I only need to know if there are any records that match the criteria. Theoretically, there should only be one, but for my purposes, if there are any records I do nothing, if there are no records I need to insert a record.

    Unfortunately, when I changed my select statement to
    kriki wrote:
    IADOCommand.CommandText := 'SELECT TOP1 1 NULL FROM asset_class WHERE class_name = ''' + FAClassRec.Code + '''';
    

    I get a run time error - Incorrect syntax near the keyword 'NULL'.

    I copied in your select statement exact, was that what I was suppose to do?

    Thanks so much for any help

    Kelly
Sign In or Register to comment.