RecordCount returns 0 on Query

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
mskeels
Posts: 16
Joined: Tue 06 Mar 2012 15:08

RecordCount returns 0 on Query

Post by mskeels » Tue 15 May 2012 19:48

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

Re: RecordCount returns 0 on Query

Post by AndreyZ » Wed 16 May 2012 11:46

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
Posts: 16
Joined: Tue 06 Mar 2012 15:08

Re: RecordCount returns 0 on Query

Post by mskeels » Wed 16 May 2012 14:16

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

Sorry about that.

Thanks,
Mark

AndreyZ

Re: RecordCount returns 0 on Query

Post by AndreyZ » Thu 17 May 2012 09:46

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
Posts: 16
Joined: Tue 06 Mar 2012 15:08

Re: RecordCount returns 0 on Query

Post by mskeels » Thu 17 May 2012 13:07

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

Thanks,
Mark

AndreyZ

Re: RecordCount returns 0 on Query

Post by AndreyZ » Thu 17 May 2012 14:27

I am looking forward to hearing from you.

Post Reply