Locate getting multiple records

NickD · Post by **NickD** » Thu 19 Apr 2007 03:29

Hi, I'm having a spot of bother with the following code. tblInvoiceDetails being a TMSTable component, on version 3.80.

        if ( tblInvoiceDetails.Locate( 'InvoiceNo;SerialNo;MeterType;RecordType', VarArrayOf([ sCurrentInvoice, slLine.Strings[IU_SERIALNO], slLine.Strings[IU_METERTYPE], '05']), [] )) then
        begin
          tblInvoiceDetails.Edit;
          tblInvoiceDetails.FieldByName('BillingVol').AsInteger := StrToInt( slLine.Strings[IU_ACTUALVOL] );
          tblInvoiceDetails.FieldByName('LineValue').AsCurrency := dLineValue;
          tblInvoiceDetails.FieldByName('GSTValue').AsCurrency := dGSTValue * 0.125;
          tblInvoiceDetails.Post;
        end

On the post, I get an exception, telling me "Update failed. Found 2 records." There are 2 records in the database that would meet the Locate criteria, and this cannot be avoided. However, I was of the understanding that Locate should only make the first record it finds that meet the criteria as the active record, not all the records that meet the Locate criteria.

Jackson · Post by **Jackson** » Thu 19 Apr 2007 09:40

Actually this exception arise on calling the TDataSet.Post method but not on calling the TDataSet.Locate method.
This error occurs when the database server is unable to determine which record to modify or delete. In other words, there are either more than one record or no records that suit the UPDATE criteria. Such situation can happen when you omit the unique field in a SELECT statement (TCustomDADataSet.SQL) or when another user modifies the table simultaneously. This exception can be suppressed. Refer to TCustomDADataSet.Options.StrictUpdate topic in SDAC help for more information.

NickD · Post by **NickD** » Thu 19 Apr 2007 21:15

Yes, I realise it occurs on the post. I am just saying that there should be no ambiguity on which record to modify because the Locate call should have only made one record the active record, even if there is more than one record that meets the Locate criteria. It should have just set the first record it found as the active record, and thus just tried to update that one.

No other user was trying to update the record as I was the only user, so I can only assume that the Locate method somehow made more than one record active.

I had noticed the StrictUpdate option, but I got the impression if I used that then it would try to update both records the Locate returned, which isn't what I want, I just want to put a value in either of the two almost identical records that the Locate found.

If it makes a difference, the tblInvoiceDetails TMSTable component is created in code, not as a visual component on the form.

Jackson · Post by **Jackson** » Fri 20 Apr 2007 12:09

The TDataSet.Locate record makes only one record as a current record.
In fact there is no possibility to make several record as currect records in SDAC (like in BDE, ADO, dbExpress).
When you call the TDataSet.Post method, SDAC generates the SQL statement for updating data and sends it to the server. This statement is based on the metadata of the table (its key and data fields).
After the SQL statement is executed, SDAC receives the number of records affected by this SQL statement. If this number is not equal to 1 and the TCustomDADataSet.Options.StrictUpdate property is True, SDAC raises an exception that you can see.
In your case the database server, but not SDAC, is unable to determine which record to update, because you have two almost identical records.
Add an unique field to your table and include this field in your SELECT statement to solve the problem.