Board index » delphi » DBGrid:1 record inserted, duplicate display ??

DBGrid:1 record inserted, duplicate display ??

Hi!
I'm using D3 with ODBC and SQL Anywhere 5, all under W95.

I have a form with a DBNavigator and a simple DBgrid to maintain a 5-field
table.
Field 1 is unique RowID (integer), fields 2 & 3 are strings (user input),
fields 4&5 are userID and timestamp.

The table has a before insert trigger at the row level to:
 1) read the next available RowID from another table - increment it and
update that table (works fine)
 2) set UserID to current user (works)
 3) set the timestamp to current timestamp (works)

When I Insert a new record from my D3 app , the database part works fine but
the DBgrid displays 2 new records, instead of only one:
- one with only the user input (fields 2&3)
- one with the UserID and Timestamp values.

To get the DBGrid to display this properly, I have to disconnect from the db
and reconnect.
What am I missing??

JR Nicolet

 

Re:DBGrid:1 record inserted, duplicate display ??


Quote
Jean-Rene Nicolet wrote:
> Hi!
> I'm using D3 with ODBC and SQL Anywhere 5, all under W95.

> I have a form with a DBNavigator and a simple DBgrid to maintain a 5-field
> table.
> Field 1 is unique RowID (integer), fields 2 & 3 are strings (user input),
> fields 4&5 are userID and timestamp.

> The table has a before insert trigger at the row level to:
>  1) read the next available RowID from another table - increment it and
> update that table (works fine)
>  2) set UserID to current user (works)
>  3) set the timestamp to current timestamp (works)

> When I Insert a new record from my D3 app , the database part works fine but
> the DBgrid displays 2 new records, instead of only one:
> - one with only the user input (fields 2&3)
> - one with the UserID and Timestamp values.

> To get the DBGrid to display this properly, I have to disconnect from the db
> and reconnect.
> What am I missing??

> JR Nicolet

Hi Jean-Rene,
I don't think your doing anything wrong.  I posted the same problem just the
other day. The only way to refresh the grid is to close and open the TQuery.
Sorry.

I did have one person suggest a third party product called ODBCExpress.  I may
look at that because as you will find out, you will have to bookmark the record
you just posted in order to fake a refresh method call.

Good Luck

Greg

Re:DBGrid:1 record inserted, duplicate display ??


On Tue, 2 Dec 1997 13:52:04 -0600, "Jean-Rene Nicolet"

Quote
<jnico...@sbrc.umanitoba.ca> wrote:
>Hi!
>I'm using D3 with ODBC and SQL Anywhere 5, all under W95.

>I have a form with a DBNavigator and a simple DBgrid to maintain a 5-field
>table.
>Field 1 is unique RowID (integer), fields 2 & 3 are strings (user input),
>fields 4&5 are userID and timestamp.

>The table has a before insert trigger at the row level to:
> 1) read the next available RowID from another table - increment it and
>update that table (works fine)
> 2) set UserID to current user (works)
> 3) set the timestamp to current timestamp (works)

>When I Insert a new record from my D3 app , the database part works fine but
>the DBgrid displays 2 new records, instead of only one:
>- one with only the user input (fields 2&3)
>- one with the UserID and Timestamp values.

>To get the DBGrid to display this properly, I have to disconnect from the db
>and reconnect.
>What am I missing??

>JR Nicolet

You have to use the after insert event instead of the before insert
event.

Jan den Ouden

Re:DBGrid:1 record inserted, duplicate display ??


Jean

The problem is, Delphi knows nothing about the data entered through
your BeforeInsert trigger.

As my fellow correspondent says, you can close and then re-open the
query and the update will appear correct but, you have to locate back
to the record you had just inserted. (A whole lot too much work, I
think).

What we did to resolve the problem was to find out the next value for
the PK and set it in Delphi, then post the record.  This works just
fine.

Finally, it may have been me that recommended ODBC Express. I use it A
LOT and I love it. You won't be dissappointed with its speed (or level
of technical support) but you should be prepared for a bit more
programming)

Good Luck

Derek Davidson
Author of DK's Audit Components
Get a FREE copy from my web site at :
http://freespace.{*word*269}.net/d.davidson

(Remove the x to EMail me : der...@mksoft.com)

Re:DBGrid:1 record inserted, duplicate display ??


Hello Jean-Rene,

I can reproduce the behavior that you are seeing.  It mainly has to do with the
fact that one of the fields being filled by the trigger is part of the primary
key.  If you try the same scenario you have presented and use something besides
a field that is being filled by a trigger as the key you won't see this
problem.  This issue is that the local buffer has different data than the server
contains and we are trying to refresh the display grid and we see the record in
the local buffer as one record and the record on the server as a different
record because it has a different key value.  the local buffer has null as the
key value and the record on the server actually has valid key value.
I suggest using a stored procedure to return all the values the trigger would
give you and then insert the complete record.

I hop this helps,

Jim Kroutch

Quote
Jean-Rene Nicolet wrote:
> Hi!
> I'm using D3 with ODBC and SQL Anywhere 5, all under W95.

> I have a form with a DBNavigator and a simple DBgrid to maintain a 5-field
> table.
> Field 1 is unique RowID (integer), fields 2 & 3 are strings (user input),
> fields 4&5 are userID and timestamp.

> The table has a before insert trigger at the row level to:
>  1) read the next available RowID from another table - increment it and
> update that table (works fine)
>  2) set UserID to current user (works)
>  3) set the timestamp to current timestamp (works)

> When I Insert a new record from my D3 app , the database part works fine but
> the DBgrid displays 2 new records, instead of only one:
> - one with only the user input (fields 2&3)
> - one with the UserID and Timestamp values.

> To get the DBGrid to display this properly, I have to disconnect from the db
> and reconnect.
> What am I missing??

> JR Nicolet

--
BDE Support:
 http://www.borland.com/devsupport/bde
SQL Links Support:
 http://www.borland.com/devsupport/sqllinks
Datagateway Support:
 http://www.borland.com/devsupport/datagateway
Common Delphi and BDE Questions and Answers:
 http://www.borland.com/devsupport/delphi/qanda/delphi3qa.html

Re:DBGrid:1 record inserted, duplicate display ??


Hi!
Jean-Rene here!

Thank you all for the prompt and excellent responses! I've added a stored
procedure that returns to Delphi the values that the trigger itself is going
to write to the new record (We have to keep the trigger for security
reasons); I'm just about to test the new set-up and I'm confident it will
work

Thank guys!

Cheers,
Jean-Rene

Other Threads