BufDataset Locate not working
lazarus / 16-Databases

----------------------------------------------------------------------
Stygian · 2015-08-25 11:00

Hello Everyone,

I try to put an excel file in a Bufdataset. I already readed the excel
with FPS Spreadsheet in a WorkbookSource and uploaded the nescessary
data in the Bufdataset. When i try to use locate('filedname',
mycomboboxdata.text, []). it's always stay at the last record. I tried
to move it to the first but it not worked.
Do i need indexing for this? I search the forum but i can't find the
solution.

Here is a small code from my source:

Code: [Select]  datam.MDprod.Create(Self);
  datam.MDProd.FieldDefs.Add('ID', ftAutoInc, 0, True);
  datam.MDprod.FieldDefs.Add('CPP', ftString, 50, True);
  datam.MDprod.FieldDefs.Add('PL_num', ftString, 50, True);
  datam.MDprod.FieldDefs.Add('SO_num', ftString, 50, True);
  datam.MDprod.FieldDefs.Add('TO_num', ftString, 50, True);
  datam.MDprod.CreateDataset;
  datam.MDprod.Open;
  datam.MDprod.Append;
  row := WSexc.Worksheet.GetLastRowIndex;
  g := 1;
  for g := 1 to row do
  begin
  datam.MDprod.FieldByName('CPP').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g,0);
  datam.MDprod.FieldByName('PL_num').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g,21);
  datam.MDprod.FieldByName('SO_num').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g,3);
  datam.MDprod.FieldByName('TO_num').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g,32);
  DBCPP.Items.Add(datam.MDprod.FieldByName('CPP').AsString);
  DBCPP.ReadOnly := True;
  end;
  datam.MDprod.Post;
...
 datam.MDprod.First;
 datam.MDprod.Locate('CPP', DBCPP.Text, []);
 ...
Any help welcome .

Thanks,
Sty

----------------------------------------------------------------------
rvk · 2015-08-25 11:39

As far as I can see are you just adding ONE record into your
Bufdataset. You have the Append and Post OUTSIDE your loop (and are
missing ApplyUpdates).

You should do something like this:
Code: [Select]  datam.MDprod.Open;
  row := WSexc.Worksheet.GetLastRowIndex;
  g := 1;
  for g := 1 to row do
  begin
    datam.MDprod.Append; // <--- INSIDE THE LOOP
    datam.MDprod.FieldByName('CPP').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g, 0);
    datam.MDprod.FieldByName('PL_num').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g, 21);
    datam.MDprod.FieldByName('SO_num').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g, 3);
    datam.MDprod.FieldByName('TO_num').AsString :=
WSexc.Worksheet.ReadAsUTF8Text(g, 32);
    datam.MDprod.Post;          // <--- also inside
    datam.MDprod.ApplyUpdates;  // <--- applyupdates
    DBCPP.Items.Add(datam.MDprod.FieldByName('CPP').AsString);
    DBCPP.ReadOnly := True;
  end;
  //...
  datam.MDprod.First;
  datam.MDprod.Locate('CPP', DBCPP.Text, []);
  //...

----------------------------------------------------------------------
Stygian · 2015-08-25 11:51

Thank you rvk it's working. Only  "datam.MDprod.ApplyUpdates;" is not
needed(run on error).

----------------------------------------------------------------------
rvk · 2015-08-25 11:57

Quote from: Stygian on August 25, 2015, 01:51:17 pmThank you rvk it's
working. Only  "datam.MDprod.ApplyUpdates;" is not needed(run on
error).


Hey, that's strange. TBufDataset does implement ApplyUpdates so I
figured it was needed. If you close the dataset and open it again, are
all records still in there or are they gone?

Code: [Select]  //...
  datam.MDprod.Close;
  datam.MDprod.Open;
  datam.MDprod.First;
  datam.MDprod.Locate('CPP', DBCPP.Text, []);
  //...

If you read the whole sheet every time it doesn't matter much but if
you're planning to save the TBufdataset to file you need to check if
the records are still there.

----------------------------------------------------------------------
Stygian · 2015-08-25 12:43

It's gone. I'll read the whole sheet everytime. I use only some part
of this sheet.

----------------------------------------------------------------------
mangakissa · 2015-08-27 06:29

QuoteHey, that's strange. TBufDataset does implement ApplyUpdates so I
figured it was needed. It's a memdataset and doesn't have to save into
a database.

----------------------------------------------------------------------
rvk · 2015-08-27 06:34

Quote from: mangakissa on August 27, 2015, 08:29:28 amQuoteHey, that's
strange. TBufDataset does implement ApplyUpdates so I figured it was
needed. It's a memdataset and doesn't have to save into a database.
But it can save to a file. And if you want to keep your records within
the lifetime of your program but want to use close and open you need
to use ApplyUpdates. But in this case it seems the original file is
read every time again, so it's not needed here.