At 09:10 AM 28/02/2005 +0000, you wrote:

> We are migrating our application to FB and we use IBO components.
>We display values from multiple table in UI
>Screen. The problem face is:
>
>In the UI Screen, the values could entered only for fields
>that are specified in the InsertSql and for the fields that
>are not in the Insertsql the values could not be entered.
>
>For example
>
>
>Table A has columns Id, Name, Description,
>Table B has columns Id, Created, LastModifed, Notes
>
>In the UI screen we allow the end user to enter Id, Name,
>Description, Notes is separate textedits
>
>The Select query is : SELECT A.ID, A.Name, A.Description, A.Notes
>from A Inner join B on
>A.Id = B.Id

Shouldn't this be:
SELECT A.ID, A.Name, A.Description, B.Notes
from A Inner join B on
A.Id = B.Id
????

>If the InsertSql is set to Insert into A values
>(:Id, :Name, :Description) then in the UI screen
>values could be entered only in the Name , Description
>textedits. If the InsertSql is changed to insert values
>into Table B, values could be entered only in the
>Notes textedit.
>
>We would like to have the values retrieved, saved through a single
>dataset.

Well, SQL does not allow you to insert rows into more than one table in one
insert statement!!

If you have a joined dataset (or another dataset that is not updatable,
such as a selectable SP, a union or a non-updatable view) AND you want the
user to think she is inserting a row into one table, you must write an
executable stored procedure to do that. You pass field values from your
dataset as arguments to this SP.

e.g., in the database:

create procedure insertBlah (
id integer, name varchar(50), description varchar(100), notes blob
sub_type 1)
as
begin
....
insert into A (id, name, description)
values (:id, :name, :description);
insert into B(id, notes)
values (:id, :notes);
....
end

Then, your dataset's InsertSQL will be along these lines:

execute procedure insertBlah (:id, :name, :description, :notes)

Similarly with updateSQL and deleteSQL.

Helen