refer to sql tables

ASTAST Member Posts: 108
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

  • DenSterDenSter Member Posts: 8,307
    Sure you can. You can use ADO to do this, or you could also create a linked table in NAV.
  • ASTAST 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_BrummelMarije_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...');
    
  • WaldoWaldo Member Posts: 3,412

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • ASTAST Member Posts: 108
    edited 2007-04-30
    Thanks for replaying.
  • ASTAST 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)?
  • WaldoWaldo 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
  • ASTAST 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?
  • WaldoWaldo 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
  • ASTAST 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 ](*,)
  • ASTAST 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);
    
Sign In or Register to comment.