ADO.Locate issue

[Orionizer]

I'm having an issue using the ADO.Locate function (Using Delphi 7). I've got the following code:

procedure TFrmMain.RzBtnProcessClick(Sender: TObject);

var
  WB: IXLSWorkbook;
  WS: IXLSWorksheet;
  X: Integer;
  MillStyle: Integer;
  KnitNo: Integer;
  CustSKU: String;

begin
  if RzBtnEditInputFile.Text = '' then
    Application.MessageBox('You Must Select An Input File!','Error',MB_OK)
  else if not FileExists(RzBtnEditInputFile.Text) then
    Application.MessageBox('Cannot Locate Input File!  Please Select A Valid File!','Error',MB_OK)
  else
    begin
      FrmInfo.RzLblInfo.Caption:='Opening AS/400 Connection...';
      FrmInfo.Show;
      FrmInfo.Refresh;
      ADOCnx400.Open;
      ADOTblPRICEP.Open;
      FrmInfo.RzLblInfo.Caption:='Opening Spreadsheet...';
      FrmInfo.Refresh;
      WB:=TXLSWorkbook.Create;
      WB.Open(RzBtnEditInputFile.Text);
      WS:=WB.WorkSheets[1];
      FrmInfo.RzLblInfo.Caption:='Processing...';
      FrmInfo.Refresh;
      for X:=2 to WS.UsedRange.Rows.Count do
        begin
          if not VarIsNull(WS.Cells[X,3].Value) then
            begin
              MillStyle:=WS.Cells[X,2].Value;
              KnitNo:=WS.Cells[X,3].Value;
              SiMain.LogInteger('MillStyle',MillStyle);
              SiMain.LogInteger('KnitNo',KnitNo);
              ADOTblPRICEP.First;
              if ADOTblPRICEP.Locate('DHMSTY;DHKNIT',VarArrayOf([MillStyle,KnitNo]),[]) then
                begin
                  CustSKU:=ADOTblPRICEP.FieldByName('DHCSTY').AsString;
                  SiMain.LogInteger('RecNo',ADOTblPRICEP.RecNo);
                  SiMain.LogMessage('** FOUND **');
                end
              else
                begin
                  CustSKU:='';
                  SiMain.LogError('** NOT FOUND **');
                end;
              SiMain.LogString('CustSKU',CustSKU);
              WS.Cells[X,5].Value:=CustSKU;
            end;
        end;
      FrmInfo.Close;
      WB.Save;
      ADOTblPRICEP.Close;
      ADOCnx400.Close;
      WB.Close;
      Application.MessageBox('Finished Processing!','Information',MB_OK);
      Close;
    end;
end;

The ADOTblPRICEP is filtered on "DHCUST = 309" and filtered set to true.

When I do the ADO.Locate function, it says it finds the MillStyle/KnitNo combination, even though this does NOT exist in the database! Now, what's strange is I can remove the filter and everything seems to work properly! Any ideas?

Thanks in advance!

[Orionizer]

Also, this doesn't occur on every record, but quite a lot of them!

[SalmiSoft]

I don't see anything obviously wrong with your code so I would look at your data. For example, you check:

if not VarIsNull(WS.Cells[X,3].Value) then

but what about

MillStyle:=WS.Cells[X,2].Value;

Can WS.Cells[X,2].Value ever be null? What effect would it have if it were null?

Also I suggest you step through this in the debugger and examine the variables. What are the values of WS.Cells[X,2].Value and WS.Cells[X,3].Value which produce an erroneous match? What value of CustSKU does the match produce?

[Orionizer]

I am checking the values being searched, so that's not the issue. For example, I search for:

MillStyle = 6117
KnitNo = 1

This says the value is found, but that's not valid for DHCUST = 309. Here's a copy from my SmartInspect Log:

MillStyle = 5683
KnitNo = 23
** NOT FOUND **
CustSKU = ''
MillStyle = 6117
KnitNo = 1
RecNo = 451
** FOUND **
CustSKU = 'SW0SW452-043-L'

As you can see, some of them are not found, but some are found, but incorrectly.

In the example with 6117, the valid customer number (DHCUST) is 25 and the CustSKU (if it was finding this for some reason even with the filter set) should be:

PSX308-043-MISC

NOT

SW0SW452-043-L

[Orionizer]

Ok, I changed this a bit and got it to work correctly. I removed the filter completely and changed the .Locate call to:

          if ADOTblPRICEP.Locate('DHMSTY;DHKNIT;DHCUST',VarArrayOf([MillStyle,KnitNo,309]),[]) then

and it works perfectly! VERY strange. Appears to be a bug in the filter of the D7 ADOTable component.

I'm going to try this in XE7 with an example application to see if it happens there (using the old code). Will let you know.

[Orionizer]

Just tried in XE7 and the same issue. Looks like this has been addressed on StackOverflow before.