I have an app I'm developing with an eye towards remote operation. Roughly, I show records
in a grid and then Add/Edit/Delete through buttons.

Following someone's tip on these forums (sorry I don't remember who but great idea) I tie
the grid to a CANNED TDBISAMQuery so it's easier to scroll around. Then, for example, when
someone wants to edit a specific record I Open a LIVE TDBISAMQuery with a WHERE clause
that pulls ONLY that record. Edit using databound controls as normal, when done I Post the
live query and copy the new information to the CANNED query so that everything is
synchronized. Seems to work fine. My problem is when I need to ADD a record. What I really
want is an EMPTY live query; I can then use databound controls to add the record, post it
and add it to the canned query. Problem is when I try to create the empty live query by
basically using a WHERE clause that can't be satisfied, add the record and then Post, the
record "disappears" from the live query because the new record doesn't satisfy the WHERE
either; thus I can't add the record to the canned query. Since the table has an AutoInc
key I can't just copy BEFORE I post since I need the key value. Does that make sense?

OK, so what I thought I'd do is take advantage of a Modified Date field in the record
which is stamped as part of the BeforePost event. If I create a Live Query with a WHERE of
dModified >= ate with pDate set to Now I should pull NO (or one or two) records but the
NEW record should stay in the recordset. I will try it but I was wondering if anyone could
think of a simpler way to be able to just add the ONE record to the table while
maintaining this scenario of updating the canned query to reduce network traffic. Is there
a way to CREATE an empty live query such that an added record will "stick"?

I suppose I could use ExecSQL but the table has 105 fields and I'm loathe to have to
create the command (but will do it if that is a more efficient way of accomplishing my
goal). I just am trying to get DBISAM/Delphi to do the grunt work.

Thanks for reading this and any pointers or experiences would be appreciated.


Norman

Norman L. Kleinberg <nlk11021@yahoo.com> wrote in
news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com:

> I have an app I'm developing with an eye towards remote operation.
> Roughly, I show records in a grid and then Add/Edit/Delete through
> buttons.
>
> Following someone's tip on these forums (sorry I don't remember who
> but great idea) I tie the grid to a CANNED TDBISAMQuery so it's easier
> to scroll around. Then, for example, when someone wants to edit a
> specific record I Open a LIVE TDBISAMQuery with a WHERE clause that
> pulls ONLY that record. Edit using databound controls as normal, when
> done I Post the live query and copy the new information to the CANNED
> query so that everything is synchronized. Seems to work fine. My
> problem is when I need to ADD a record.

Norman,

 As it was me that gave you the idea, I feel I should help you out here


Since my live Query (updQuery) is in a master-detail relationship with
the Canned Query (tblMaster), if I do an updQuery.Insert it just opens
onto a new empty record, and I go ahead as for an update.

here's the Canned Query's onBeforePost event:

------------------------------8<-------------------------------
 if tblMaster.ResultIsLive then
   exit // let normal updatable query function take over
 else
 begin // use UpdQuery which is a live query on the main table in
master-detail link
   updQuery.Open;
   case tblMaster.State of
     dsEdit:
       begin
         aMsg := 'modified';
         updQuery.Edit;
       end;
     dsInsert:
       begin
         aMsg := 'inserted';
         updQuery.Insert;
       end;
     else
       updQuery.Close;
       raise Exception.Create(SUpdateQueryNotIn);
       exit;
   end;

 begin
   for i := 0 to pred(updQuery.FieldCount) do
   begin
     if tblMaster.FieldByName(updQuery.Fields[i].FieldName).Value <>
         tblMaster.FieldByName(updQuery.Fields[i].FieldName).OldValue
then
       updQuery.Fields[i].assign(
         tblMaster.FieldByName(updQuery.Fields[i].FieldName));
   end;
   updQuery.Post;
 end;
//    Showmessage(IntToStr(updQuery.RowsAffected)+' lines '+aMsg);
   updQuery.Close;
   exit;
------------------------------8<-------------------------------

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1)

Chris:

So YOU'RE the culprit. Well, thanks, but I got absolutely NO sleep last night trying to
figure this thing out.

I actually went through the scenario you suggest. If the live query is for the DETAIL then
I have an AUTOMATIC WHERE clause (key-field = parent-key-field) which will be satisfied by
few if any existing records. Inserting a record and then assigning the parent key to the
child then automatically fulfills the WHERE and I'm set. What I'm trying to do is extend
this to a very simple case: there is NO master-detail, just a single table in which to
Insert a record. Odd that the more complicated setup (master-detail) makes things simpler.

I am going, nevertheless, to study your code since I'm sure I'll be able to pick up some
useful tips.

Thanks much for the original suggestion and the follow-up.


Norman

Chris Erdal <chris@No-Spam-erdal.net> wrote:

Norman L. Kleinberg <nlk11021@yahoo.com> wrote in
news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com:

Norman,

-snip-

 As it was me that gave you the idea, I feel I should help you out here


Since my live Query (updQuery) is in a master-detail relationship with
the Canned Query (tblMaster), if I do an updQuery.Insert it just opens
onto a new empty record, and I go ahead as for an update.

here's the Canned Query's onBeforePost event:

------------------------------8<-------------------------------
 if tblMaster.ResultIsLive then
   exit // let normal updatable query function take over
 else
 begin // use UpdQuery which is a live query on the main table in
master-detail link
   updQuery.Open;
   case tblMaster.State of
     dsEdit:
       begin
         aMsg := 'modified';
         updQuery.Edit;
       end;
     dsInsert:
       begin
         aMsg := 'inserted';
         updQuery.Insert;
       end;
     else
       updQuery.Close;
       raise Exception.Create(SUpdateQueryNotIn);
       exit;
   end;

 begin
   for i := 0 to pred(updQuery.FieldCount) do
   begin
     if tblMaster.FieldByName(updQuery.Fields[i].FieldName).Value <>
         tblMaster.FieldByName(updQuery.Fields[i].FieldName).OldValue
then
       updQuery.Fields[i].assign(
         tblMaster.FieldByName(updQuery.Fields[i].FieldName));
   end;
   updQuery.Post;
 end;
//    Showmessage(IntToStr(updQuery.RowsAffected)+' lines '+aMsg);
   updQuery.Close;
   exit;
------------------------------8<-------------------------------

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1)

Norman L. Kleinberg <nlk11021@yahoo.com> wrote in
news:6D093F6F-12B4-4040-9662-45C14763D83D@news.elevatesoft.com:

> Chris:
>
> So YOU'RE the culprit. Well, thanks, but I got absolutely NO sleep
> last night trying to figure this thing out.
>
> I actually went through the scenario you suggest. If the live query is
> for the DETAIL then I have an AUTOMATIC WHERE clause (key-field =
> parent-key-field) which will be satisfied by few if any existing
> records. Inserting a record and then assigning the parent key to the
> child then automatically fulfills the WHERE and I'm set. What I'm
> trying to do is extend this to a very simple case: there is NO
> master-detail, just a single table in which to Insert a record. Odd
> that the more complicated setup (master-detail) makes things simpler.

Norman,

 I confused you on the master-detail bit. I'm only talking about one
REAL table here.

 In my scenario the main grid is filled from a Query (tblMaster) which
may or may not be live, depending on the SQL script.

 On the same form I also have a blank Query (upQuery) which is to be
used (in the event that the main query is not live) for inserts, updates
and deletes (because I wanted to take advantage of Context Database
Extensions to enforce RI).

 When I'm about to open updQuery, I fill its SQL automatically:

procedure TDBISAMAdvStrGrdForm.updQueryBeforeOpen(DataSet: TDataSet);
begin
 inherited;
 updQuery.SQL.Text := 'SELECT * FROM ' + UpdTableName + WhereClause
(true);
 updQuery.DataSource := dsMaster;
end;

The WHERE clause is also created automatically:

function TDBISAMAdvStrGrdForm.WhereClause: string;
var
 i: Integer;
 aKeyField: string;
 aFldName: string;
begin
 Result := ' WHERE ';
 SetDelimiter(';');
 i := 1;
 repeat
   aKeyField := GetToken(PrimaryKeyFields, i);
   if aKeyField > '' then
   begin
     if i > 1 then
       Result := Result + 'AND ';
       Result := Result + aKeyField + ' = :' + aKeyField;
   end;
 until not NextToken(PrimaryKeyFields, i);
end;

(GetToken etc. come from HyperString, by "EFD Systems")

This way I end up with updQuery as the "child" table with tblMaster as
its "Master" table in a pseudo-Master-Detail setup, but they're both
based on the same REAL database table and linked by its Primary key
field.

That's how I keep updQuery in sync with tblMaster's active row - whenever
updQuery opens, it is locked on to the current row in tblMaster by this
link.

This means that, in view of the onBeforePost event for tblMaster, when I
am about to post a new row in tblMaster (which will be lost as it's a
CANNED query) I open updQuery onto an empty row in the REAL table (as the
key doesn't yet exist in the REAL table on the disk), and immediately put
it into the Insert state, transfer all the required field values to it,
and Post it.

I can then update the new tblMaster's current record key field(s) with
the value(s) I find (if it's Autoinc, for example) and the Grid is
already showing the inserted row without a refresh.

Voilà!

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1)

Chris:

>Chris Erdal <chris@No-Spam-erdal.net> wrote:

>-snip-

>Norman,

>-snip-

>This way I end up with updQuery as the "child" table with tblMaster as
>its "Master" table in a pseudo-Master-Detail setup, but they're both
>based on the same REAL database table and linked by its Primary key
>field.

>That's how I keep updQuery in sync with tblMaster's active row - whenever
>updQuery opens, it is locked on to the current row in tblMaster by this
>link.

Understood. I have no problem with editing an existing record. I know the key and my
"auxiliary" query can just use it to select the proper record from the REAL table.

>This means that, in view of the onBeforePost event for tblMaster, when I
>am about to post a new row in tblMaster (which will be lost as it's a
>CANNED query) I open updQuery onto an empty row in the REAL table (as the
>key doesn't yet exist in the REAL table on the disk), and immediately put
>it into the Insert state, transfer all the required field values to it,
>and Post it.

This is the crux of my misunderstanding. What is your WHERE clause for this? Is it
aKeyField := "whatever the new key is"? If so, and the table has an AutoInc primary key,
how do you know what this value will be? For example, say I have an AutoInc CustomerID in
my table. User hits Insert: the canned query BeforePost event opens the Query on an empty
row, which I presume is accomplished by a WHERE clause such as KeyField = "new Key Field". But
how do you know what that "new Key Field" is if it's AutoInc? I guess I'm asking what your
WHERE
clause looks like when you are opening the Query on an empty row.

If the table has an AutoInc key then you won't KNOW the new key value until you Post the
record, I
believe.

>I can then update the new tblMaster's current record key field(s) with
>the value(s) I find (if it's Autoinc, for example) and the Grid is
>already showing the inserted row without a refresh.

>Voilà!
>--
>Chris
>(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.02 build 1)


I of course have no doubt it works but I can't see how if the table has an AutoInc key.
Again, my
understanding is that I won't know the value of that key until after I post the Live
query, at which point
the record disappears (unless you have a WHERE clause I haven't envisioned). The MasterSource
locking idea is great, but ONLY if you have an actual key to connect the parent and child
query.

Perhaps if you could write out your WHERE clause for an Insert I could figure it out. I
realize it's asking a lot
so I would understand if you couldn't, it's just this Insert thingie has me totally
confused.

Thanks.

Norman

"Norman L. Kleinberg" <nlk11021@yahoo.com> wrote in message
news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com...
> synchronized. Seems to work fine. My problem is when I need to ADD a
> record. What I really
> want is an EMPTY live query; I can then use databound controls to add the
> record,

Why do you need an empty query? When you do an insert, your databound
controls will be empty because the new record will be the current record.
who cares if you have other records in the live query? Nobody will see them.

Robert

Norman

What about doing the select into a memory table, and in its afterpost event either do an update or an insert?


Roy Lambert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"Norman L. Kleinberg" <nlk11021@yahoo.com> wrote in message
news:0E654CAC-D00E-4656-AE3B-638683D07F22@news.elevatesoft.com...
> synchronized. Seems to work fine. My problem is when I need to ADD a
> record. What I really
> want is an EMPTY live query; I can then use databound controls to add the
> record,

Why do you need an empty query? When you do an insert, your databound
controls will be empty because the new record will be the current record.
who cares if you have other records in the live query? Nobody will see them.

Robert
---------------------------------------------------------------------------------------

Robert:

I don't need it to be empty, you're correct. What I want, however, is to minimize network
traffic
so I need this Query, which will be Live, to have to fetch as few records as possible.

I've experimented with my "trick" of using a WHERE clause of DateModified > Now and it
DOES work,
so all this is academic, anyway.


Norman

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Norman

What about doing the select into a memory table, and in its afterpost event either do an
update or an insert?


Roy Lambert
------------------------------------
Roy:

You are of course correct. What I ran into, however, was an interesting issue which is new
to me but must
be well known to more experienced coders.

I have two issues: an AutoInc primary key which I NEED to know so I can copy to the canned
query AND the fact
that my table has 105 fields. There are a number of different ways to update a table,
including yours; do an SQL
Insert with an ExecSQL (I have to write the SQL Statement but can retrieve the AutoInc
value), memory table
followed by an automated record copy routine (can't get the AutoInc value but wouldn't
have to write 105 field transfers),
Table or Live Query with a Post (can get the AutoInc value but am trying to avoid this
because my DataSet is large)
or a "small" Live Query (don't need to write the 105 field names, minimize network
traffic, but how to get
the AutoInc value back). This actually raises another question I had which was, basically,
"what's the difference from
DBISAM's point of view"? I guess there are some differences in efficiency and locking,
etc., but basically DBISAM
has got to handle a "record copy" and an "SQL Insert", for example, the same in the
background.

I guess I've gotten off the main point. It's quite possible I'm overlooking something
simple but, to me, I'm in a box
with this. The only way I've found is to execute a  Live query with "DateModified > Now"
which returns a very small
or empty result set (not much network traffic, I think), Insert and Post. Since the newly
posted record satisfies the
WHERE it stays in the Query and I can retrieve all the field values, INCLUDING the AutoInc.

Thanks to all for taking the time to post. I will reread all of the messages and see if I
can improve my knowledge
of Delphi Database coding in general and DBISAM in particular.

Norman

Norman


OK lesson 1

SELECT * INTO "Memory\Fred" FROM BandA WHERE _BoxNo = -100000

Creates a new database in memory with the exact structure as the source. The problem is the AutoInc. With an existing record it will pick up the existing Autoinc and that will be preserved when you post. A new record will create its own, but you should be able to set it to null after the initial post.


Lesson 2 (returning the data to the table) will follow if I ever figure it out

Roy Lambert