Subject Re: [IBO] editSql - error 'field x cannot be modified'
Author Helen Borrie
At 08:07 PM 19/09/2005 +0000, you wrote:
>i have the following tiboquery defined:
>select, b.field1, b.field2, b.dateField, b.matchiD, pe.name1,
>pe.name2, pe.pkid
>from table1 b
>join table2 on b.matchID = pe.pkid

This is not a valid join statement. Where is the table referred to by the
alias "pe"? It's not possible to determine exactly what's wrong here
unless you post a statement that actually illustrates the relationships in
your non-updatable statement.

>keylinksautodefine = false
>keyLinks =

No good, as it won't uniquely identify every row in a 3-table join.

>orderingitem 1 = pe.name1,
>updateSql:editSql is
>Update table1 set
> dateSent = :dateSent
> id = :old_id

According to your reported SELECT statement, dateSent is not a field in the

With IBO, it's recommended to use the dataset's internal UpdateSQL object
(surfaced as the properties EditSQL, InsertSQL and DeleteSQL) when you use
custom DML. However, if you insist on sticking to a separate UpdateSQL
object, make certain that it the dataset's UpdateObject property is
properly linked to it.

And here I am suspicious about your search parameter. For UpdateSQL,
parameters should refer directly to a unique structure in the underlying
table. It's possible an exception is bubbling up from the parameter in
your WHERE clause (it's not valid to change the values of keyfields in
joined sets, which is implied by referring to Old_ID). Notwithstanding, I
would expect a different exception from this...

>refreshLIve = false (have tried this as true)

Not applicable to non-updatable sets unless the KeyRelation technique is
used and is valid (see below).

>preparedEdits = true
>other properties are default
>in code i have
>with qry do
> first
> while not eof do
> begin
> edit
> fieldByName('dateSent').asString := sDate

Is dateSent stored in the database as a string or a date type? If the
latter, you should pass a TDate or a TDateTime in the assignment.

> post
> next
> end
>when we run this we now get the error 'field datesent cannot be
>modified' error.

If there is only one table to be updatable, then set the KeyRelation
property to be the name of the table that you want to update and you won't
need custom DML. However, not knowing the correct structure of your
statement, we don't know whether you have a set that can be updated this
way. This is a question of KeyLinks again. If the KeyLinks can find the
right row in the table, this will work; otherwise not.

>again this was working before we upgraded to 4.5.b (from 4.3.aa)

If it was exactly as presented here, then I'm very sceptical. :-)

>any suggestions on how to get the editsql to get this field to be

1. (Essential) - get the KeyLinks right for the actual SELECT statement


2. Either
a) use KeyRelation and abandon the custom DML
b) Correct the custom DML, move it into the appropriate XxxxSQL properties
of the dataset and ditch the TUpdateSQL

>we're using delphi 7

If the suggestions above still cause problems, please try to describe the
problems accurately and post them in a new thread. I ask this, because I
have uncovered a bug with IBO 4.5 regarding property assignment, that
affects the fully-patched Delphi 7 Enterprise, but not Delphi 6 or Delphi 5.

Originally there was a possibility that it only affected IBO code that had
been developed in a previous version of IBO. However, I've just done a
project that manifested this bug when developed from scratch in Delphi 7
with IBO 4.5. I moved the whole project into Delphi 6 + IBO 4.5 and the
bug disappeared.

Geoff Worboys did some testing and there's evidence that this bug doesn't
affect the unpatched Delphi 7. However, I have reverted to Delphi 6
totally until and unless this bug is ironed out.