At 08:37 PM 6/05/2004 +0000, you wrote:

>I am converting a SQL Server/ODBC lib app to Firebird and IBO and I
>am having a problem with one grid which allows a user to edit the
>contents of a table (from a very simple join - this join
>was "editable" before... and I hope it is still now too).
>
>Every time I try to enter a value into one of the Quantity fields, my
>app errors out with a EIB_ error saying you cannot edit this row.
>
>I need to be able to edit the quantity fields - the rest of the
>columns I have set to not allow editing...
>
>This is/was a working app that has worked find for a few years... so
>I am curious as to what I am doing incorrect...
>
>Here is the SQL statement of the TIBOQuery, and RequestLive is set to
>true...
>
>SELECT LamInventory.ID, LamColor.ColorID, LamInventory.Width,
>LamInventory.LamLength, LamInventory.StockQuantity, LamColor.Name,
>LamInventory.AdjustQuantity, LamInventory.TargetQuantity,
>LamInventory.SpokenForQuantity
>FROM LamInventory, LamColor
>WHERE LamInventory.LamColor=LamColor.ID
>AND LamColor.LamMaker=:theMaker

Joined datasets are non-updatable. I'm not going to get into a debate
about your impression that they are updatable in MSSQL. In standard SQL
(which means Firebird) they are not.

IBO has two ways to make joined datasets live - and RequestLive (on its
own) isn't one of them. Let's begin with the simple case.

If you just want to update columns in *one* of the underlying tables, use
KeyRelation: enter the name of the updatable table there. Set RequestLive
true and make certain that your KeyLinks are correct. There must be enough
entries in Keylinks to ensure that 1) updates and deletes can find one and
only one eligible row in the table and 2) an insert will have the required
keys.

In cases where you need to update columns from more than one table, you
need to write stored procedures to do it (as indeed you needed to in MSSQL
in the times when I used MSSQL!). For updates and inserts, there should be
input parameters for every targeted field. For deletes, you need only the
primary key fields.

In IBO, you use the xxxxSQL properties for EXECUTE calls to these SPs. For
example, for EditSQL:
EXECUTE PROCEDURE UPD_BYCOLOR (::width, :etc,....)

Then IBO will make the dataset live. Make sure you use the same names for
the parameters as you use for the fieldnames - then IBO will also be able
to do the assignments for you automatically.

I strongly urge you to abandon that old SQL-89 join syntax and get up to
score with modern SQL. It is easier to read, it's faster for IBO to
process and, in some cases, the Firebird optimizer makes a better plan.

SELECT li.ID, lc.ColorID, li.Width,
li.LamLength, li.StockQuantity, lc.Name,
li.AdjustQuantity, li.TargetQuantity,
li.SpokenForQuantity
FROM LamInventory li
JOIN LamColor lc
on li.LamColor=lc.ID
WHERE lc.LamMaker=:theMaker

If you stick with the old syntax in IBO, you need to use the property
JoinLinks to tell the dataset which of the WHERE criteria are join
parameters. In your example, JoinLinks would need to contain LamColor.

Helen