RecordCount returns 0 on Query

[mskeels]

Hello, All,

I'm running a query against a couple of tables in a SQL Server db, using a TMSQuery component connected to a TMSConnection.

I'm expecting to get rows returned; I verified this by running the exact query in SQL Server Management Studio.

However, I get 0 rows returned.

A couple other queries I have run seem to work OK.

Ideas, please?

Thanks,
Mark

(Code follows)

dmSAP.msQrySAPAux.Close;
dmSAP.msQrySAPAux.SQL.Clear;

//dummy is a string......

dummy := '';
dummy := dummy + 'select o.NumAtCard as OrderNo, o.CardCode as CustNo, ';
dummy := dummy + 'r.OpenCreQty as Qty, r.ItemCode as ItemNo, ';
dummy := dummy + 'r.U_BaseArtNo as BaseArtNo, @@rowcount AS NumRows from [SBOHwoodDev02].[dbo].[ORDR] as o inner join [SBOHwoodDev02].[dbo].[RDR1] as r ';
dummy := dummy + 'on o.DocEntry = r.DocEntry where rtrim(ltrim(o.NumAtCard)) = ''';
dummy := dummy + trim(tsJobs.Strings) + '''';


//Below is the former code which also did not work
// dmSAP.msQrySAPAux.SQL.Add('select o.NumAtCard as OrderNo, o.CardCode as CustNo, ');
// dmSAP.msQrySAPAux.SQL.Add('r.OpenCreQty as Qty, r.ItemCode as ItemNo, ');
// dmSAP.msQrySAPAux.SQL.Add('r.U_BaseArtNo as BaseArtNo, @@rowcount AS NumRows from [SBOHwoodDev02].[dbo].[ORDR] as o inner join [SBOHwoodDev02].[dbo].[RDR1] as r ');
// dmSAP.msQrySAPAux.SQL.Add('on o.DocEntry = r.DocEntry where rtrim(ltrim(o.NumAtCard)) = ''');
// dmSAP.msQrySAPAux.SQL.Add(trim(tsJobs.Strings) + '''');
//end former code

dmSAP.msQrySAPAux.Open;


//Below always returns 0
//Checking Recordcount also returns 0
//However this identical query runs SQL Server Management Studio

//check for possibility of no items existing for the order number.
if dmSAP.msQrySAPAux.FieldByName('NumRows').AsInteger = 0 then
begin
ShowMessage('Order #' + trim(tsJobs.Strings) + ' was not found. Rebuild tray info.');
// Exit;
end;

[AndreyZ]

Hello,

Please send a script to create and fill all needed tables (ORDR, RDR1) to andreyz*devart*com . Also please specify the following:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of your IDE.

[mskeels]

I believe I found the problem; I had the "MultipleActiveResultSets" connection property in the TMSConnection set to false.......

Sorry about that.

Thanks,
Mark

[AndreyZ]

It is strange that setting MultipleActiveResultSets to True solved the problem. The MultipleActiveResultSets property allows applications to have more than one pending request per connection, and, in particular, to have more than one active default result set per connection. If you can reproduce such behaviour in a small test project, please send it to andreyz*devart*com for investigation.

[mskeels]

We'll see how the schedule goes......if I get time, I'll do it.

Thanks,
Mark

[AndreyZ]

I am looking forward to hearing from you.