refer to sql tables

AST

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

DenSter

Sure you can. You can use ADO to do this, or you could also create a linked table in NAV.

AST

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

You can use the AdoCon.Execute(SQLStatement) method to execute whatever statement you like.

Example

AdoCon.Execute('INSERT INTO TABLE...');

Waldo

A few more examples:
http://www.mibuso.com/forum/viewtopic.php?t=9995&highlight=

AST

Thanks for replaying.

AST

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

You'll have to work with the ".parameters" statement of ADO.
Here is some code:
http://www.freevbcode.com/ShowCode.asp?ID=3687

AST

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

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.

AST

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

I solved my problem
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);