--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 09:00 PM 11/09/2006, you wrote:
>
> >Right Click on TIBOQuery Component & "Edit Query".
> >Select "UpdateSQL" tab.
> >There are 3 boxes - "EditSQL", "InsertSQL", "DeleteSQL".
> >What I described as the "insert window" is the "InsertSQL" box.
>
> OK, now we are getting to the nitty-gritty. You haven't hitherto
> mentioned that you have a dataset (a SELECT statement) and that you
> were playing about with the InsertSQL property *of a dataset*.
>
> Right. Now, one of the things a dataset object (such as TIBOQuery)
> "knows" is how to create Insert, Update and Delete statements based
> on the specification of its SELECT statement. Provided you give it
> the unique key of the set (via KeyLinks) and set its RequestLive
> property true, it generates these statements automatically.
>
> All this is hunky-dory if your SELECT statement outputs fields from a
> single table. With joined sets, grouped sets and selectable stored
> procedures, you have to create a custom SQL statement to perform the
> respective DML operations according to data entered into field
> "slots" in your interface. As a broad rule, such custom SQL
> statements will be calls to executable stored procedures, that take
> values from the "field slots" as input parameters.
>
>
> >Can I modify the code in the "InsertSQL" box that was created when I
> >pressed the button "Generate for Table" so that I can make one of the
> >columns receive a delphi global variable, and have the other columns
> >take their values as normal from an dbgrid?
>
> No. In fact, the other columns do NOT take their values from the
> grid. Lying between the grid (or any other data-aware control) and
> the dataset is a datasource. A datasource is an object too, one that
> knows what to do when the user changes something in the control. It
> does quite a lot of stuff and, all going well, the datasource will
> update the dataset, effectively passing user's changes (or
> instructions) to the dataset. The datasource "knows" what's
> happening in both the control and the dataset and its job is keep
> them both in sync.
>
>
> >If not, is there an easy way to do this using the TIBOQuery component?
>
> For simplicity's sake, let's suppose the SELECT statement that forms
> the SQL property of the dataset is a "simple" one - consisting of the
> primary key fields plus some others, all from a single table.
>
> In the ordinary case, when you call the dataset's Insert method, the
> "empty" record that appears is the "visual end" of an array of
> parameters that the dataset component has automatically constructed
> in the InsertSQL property. You don't want to change *that*. What
> you do want to do here is to intervene and change the value that the
> component would automatically assign to that particular parameter.
>
> Now, absolutely nothing happens in the database until you call
> Post. So the place where you want to intervene is just before the
> Post occurs, i.e. at BeforePost. Here, you want to make what's
> called a "late binding" for this parameter. It's dead simple:
>
> procedure TForm1.IBOQuery1BeforePost(DataSet: TDataSet);
> begin
> Dataset.ParamByName'('PVERSION_ID').AsInteger := VAR_PVERSION_ID;
> end;
>
> One other point you could do it, provided you ensure that the user
> can't modify it, is in AfterInsert - like what happens when the
> dataset invokes its Gen_ID() method and picks up the
> GeneratorLinks. That way, the user will be able to see the actual
> value that will be posted.
>
> Helen
>

Helen, thanks for your very helpful reply.

I am using a "simple" SELECT statement that forms the SQL property of
the dataset consisting of the primary key field "ID" plus some others,
all from a single table. However I have a "where pversion_id =
:Var_PVERSION_ID"

The variable that I pass to the parameter "Var_PVERSION_ID" is called
"curr_proj_version". This works OK and returns just the rows that I need.

When inserting a new row, I comment out the "ID" field in the insert
statement because I have an insert trigger that automatically provides
a value for this primary key column. I have tested this by manually
filling in the other not null column "PVersion_ID" and it works OK.

Now I want to automatically provide a value to the "PVersion_ID"
column when I do the insert.

I examined your instructions, but shouldn't the procedure use FieldBy
Name rather than ParameterByName to set the field value?

I tried both of the following statements in the procedure and now I
get the error "Field "ID" must have a value" as if the trigger has
stopped working.

If I manually enter the next "ID" value I get the message "Field
"PVERSION_ID" must have a value."


procedure TDataModule5.IBOQuery1BeforePost(DataSet: TDataSet);
begin
iboquery1.FieldByName('PVERSION_ID').AsInteger := curr_proj_version;
//alternative statement tested
//iboquery1.ParamByName('var_PVERSION_ID').AsInteger := curr_proj_version;
end;

Here is the select statement for your reference:

SELECT *
FROM LOC
where
pversion_id = :Var_PVERSION_ID
ORDER BY ID

Here is the insert statement:

INSERT INTO LOC(
/*ID, PK*/
PVERSION_ID,
NAME,
FLOORAREA,
LOCKD,
CREATED,
DESLOCREF,
LOCQTY)
VALUES (
/*:ID,*/
:PVERSION_ID,
:NAME,
:FLOORAREA,
:LOCKD,
:CREATED,
:DESLOCREF,
:LOCQTY)


Thanks for your help,

Mitch