ADO Connection not working!?

r44712

Hi

I've a system that connects Navision with a program that we've written internally, both using SQL databases.


I've implimented some code:

IF ISCLEAR(ADOConnection) THEN BEGIN
  IF NOT CREATE(ADOConnection) THEN ERROR('Cannot create ADO Connection automation variable.');
END;

IF ISCLEAR(ADORecSet) THEN BEGIN
  IF NOT CREATE(ADORecSet) THEN ERROR('Cannot create ADO Recordset automation variable.');
END;

IF ISCLEAR(ADOCommand) THEN BEGIN
  IF NOT CREATE(ADOCommand) THEN ERROR('Cannot create ADO Command automation variable.');
END;

IF ISCLEAR(ADOStream) THEN BEGIN
  IF NOT CREATE(ADOStream) THEN ERROR('Cannot create ADO Stream automation variable.');
END;


ConnString := 'Provider=SQLOLEDB;Data Source=' + DbServer + ';'
                + 'Initial Catalog=' + DbName + ';User ID=' + DbUsername + ';Password=' + DbPassword + ';';

ADOConnection.ConnectionString(ConnString);

ADOConnection.Open;
ADOCommand.ActiveConnection := ConnString;

ADOCommand.CommandText := STRSUBSTNO('UpdateAuctionStatusWithStock ''%1''',ItemNo);

ADOCommand.Execute();

CloseConnection;

Which is called from codeunit 22 (Item Jnl.-Post Line) as when an item has been purchased, +ve increased, or transferred into a location that we sell goods from:

ADOMgmt.InsertItemNo(ItemLedgEntry."Item No.");

This code has been tested as working previously (on occasion) producing the required outcome on the external system.

However, every now an again it seems to cause Navision to hang for a long while and we either get the error that the connection has timed-out of there is an error near the syntax '123456' (where 123456 is the item no.).

Which is all very confusing, becuase if I add a line to a codeunit (with no other lines in it) it calls the stored procedure without any problem and updates the external system everytime. ](*,)

Its all very odd!!! Has anyone experienced this problem themselves, or know of any way to resolve it, thanks?

r44712

This is such a confusing problem and seemingly imposible to debug as making a change works for a bit and then stops working. Try it again the following day and all is fine for a short while??? :-k

lubost

I think that problem can be in UpdateAuctionStatusWithStock. Is it stored procedure?

r44712

lubost wrote:

I think that problem can be in UpdateAuctionStatusWithStock. Is it stored procedure?

It is yeah. With much headscratching, it turns out that it was trying to access a table that was locked by navision as part of the posting process...


Thanks for the input, anyhow!