refer to sql tables

AST
AST Member Posts: 108
edited 2007-05-06 in NAV/Navision Classic Client
Hello,
Can Navision refer to sql table?
I have two tables in the same sql server 2005 database: tableNav created in Navision Object Designer and tableSql created by Sql server tools.
It is possible to get data form tableNav and set it to tableSql using Navision tools? :-k

Answers

  • DenSter
    DenSter Member Posts: 8,307
    Sure you can. You can use ADO to do this, or you could also create a linked table in NAV.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • AST
    AST Member Posts: 108
    I think that ADO is good option for my database, but I don't know how to start. I just need a little help - any example about writting data to external databse. I found example about reading data from external database, but it didn't help me much.
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You can use the AdoCon.Execute(SQLStatement) method to execute whatever statement you like.

    Example
    AdoCon.Execute('INSERT INTO TABLE...');
    Marije Brummel | Freelance Business Central Specialist
  • Waldo
    Waldo Member Posts: 3,412
    A few more examples:
    http://www.mibuso.com/forum/viewtopic.php?t=9995&highlight=

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • AST
    AST Member Posts: 108
    edited 2007-04-30
    Thanks for replaying.
  • AST
    AST Member Posts: 108
    Next problem ... How can I write value as a variable?
    That was my idea 
    Country.FIND('-');
REPEAT
   SQLString := 'declare @param varchar(10)'
             + 'SET @param = Country.Code'
             + 'insert into sqlTab (name) values (@param)';

   ADOConnection.Execute(SQLString);
UNTIL Country.NEXT = 0;
    but this (SET @param = Country.Code) doesn't work.
    Do you have any suggestion how to do this task (export data from Nav table to sql table via ADO)?
  • Waldo
    Waldo Member Posts: 3,412
    You'll have to work with the ".parameters" statement of ADO.
    Here is some code:
    http://www.freevbcode.com/ShowCode.asp?ID=3687

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • AST
    AST Member Posts: 108
    I think I'm close, but I need little more help...

    I created Ado parameter, but now I have problem with 
    insert into
    sql statement. That's my code
    ADOConnection.ConnectionString('Provider=SQLOLEDB;Data Source=star;'
     + 'Initial Catalog=adotest;User ID=ast;Password= ;');
ADOConnection.Open;
ADOCommand.ActiveConnection := ADOConnection.ConnectionString;
ADOParameters:=ADOCommand.Parameters;

ADOParameter:=ADOCommand.CreateParameter('@var',4,1,3,100);
ADOCommand.CommandText := 'insert into dbo.Country (test) values (@var)';
ADOCommand.Execute;
    end error "Must declare the scalar variable "@var".
    What should I pass as value in insert into statement?
  • Waldo
    Waldo Member Posts: 3,412
    I think you have to create a stored procedure with a parameter.
    and call your SP. Something like:
    ADOConnection.ConnectionString('Provider=SQLOLEDB;Data Source=star;' 
     + 'Initial Catalog=adotest;User ID=ast;Password= ;'); 
ADOConnection.Open; 
ADOCommand.ActiveConnection := ADOConnection.ConnectionString; 
ADOParameters:=ADOCommand.Parameters; 

ADOParameter:=ADOCommand.CreateParameter('@var',4,1,3,100); 
ADOCommand.CommandText := 'sp_mycustomStoredProcedure'; 
 
ADOCommand.Execute;

    in your stored procedure, you could put your INSERT statement.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • AST
    AST Member Posts: 108
    ok, that's my SP 
    Create PROCEDURE dbo.sp_SetValue (@var int)
AS 
    INSERT INTO dbo.Country (test) values (@var)
    What parameter shoul be in ??? place ? 
    ADOParameter:=ADOCommand.CreateParameter('@var',4,1,3,100);
ADOCommand.CommandText := 'sp_SetValue ??? '
    sorry if it's stupid question, but my code still doesn't work ](*,)
  • AST
    AST Member Posts: 108
    I solved my problem :D
    This code works: 
    ADOConnection.ConnectionString('Provider=SQLOLEDB;Data Source= test;'
     + 'Initial Catalog=adotest;User ID= test;Password= test;');
ADOConnection.Open;
ADOCommand.ActiveConnection := ADOConnection.ConnectionString;
ADOParameters:=ADOCommand.Parameters;

ADOCommand.CommandText := 'sp_SetValue';
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout:=0;

ADOParameter:=ADOCommand.CreateParameter('@var',4,1,3,100); 
ADOParameters.Append(ADOParameter);
ADOCommand.Execute;
    I forgot about 
    ADOParameters.Append(ADOParameter);

Categories