Posted on 2012-08-25
Medium Priority
Last Modified: 2012-09-05
Hi experts,

I have a delphi cxGrid I am using for grid entry to a SQL database. The grid pulls data from a view, so I have had to find a workaround for updating the data in the grid--my solution was to create a secondary column that is not linked to a field, and then use the ongetdisplay text event to populate the field and an onvalidate event to update the record to the new value.

The problem I'm finding however, is that once the value of the unlinked column is changed, the display value reverts to the value of its twin column; I want it to use the actual column's value ONLY to populate the field initially, afterwards I want it to hold the value that is input. I've tried this:

if AText = ''  then AText := VarToStr(ARecord.Values[gvFinishFormNEWCabinets_Masking_PercentComplete.Index]);

Open in new window

to no avail, the event seems to run anyways (I'm guessing that during edit it blanks the field at some point). Is there another way around this or maybe a better event to be using?
Question by:Bianca
  • 3
  • 2
  • 2
  • +1

Expert Comment

ID: 38334028
My hint -  "Calculated Field".
LVL 21

Expert Comment

ID: 38334069
Honestly, I think you would find it easier to just move the view data into a TClientDataset.  To do that you:
1) Create a TDatasetProvider and set the dataset property to your dataset.  Set the name to something you will use in the next step.
2) Create a TClientDataset and set the ProviderName property to the name of your TDatasetProvider.
3) Open the TClientDataset.

Once the data is in the TClientDataset, you can connect it to any control and edit to your heart's content.  The TClientDataset will keep track of what has been edited so you can check.  No need to fuss with extra columns if you don't want to.  When the user clicks on the OK button, then you cycle through and update the changes to the database.  As a bonus, if the user decides to do several edits, then cancel the whole set... all you have to do is not do the updates.

There's a lot out there on working with TClientDatasets.  Another bonus is that you can do in memory indexing on them.

Let me know if you need more.
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38335561
the grid has several options:
column level:
  OnGetDisplayText for displaying data to the user
  OnGetDataText for get a value of the data >> for sorting

view level
OnInitEditValue >> for setting the value in the editor

i'm guessing you need both OnInitEditValue and OnGetDisplayText
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

LVL 38

Expert Comment

by:Geert Gruwez
ID: 38335563
>> developmentguru
it looks like this is a visual issue, clientdataset (or any others) isn't a visual component

Author Comment

ID: 38337331
As Geert said, I need this to be a visual component since we are using the cxgrid to edit. Maybe if there is a way to use the clientdataset in conjunction with the cxgrid? I don't have much experience with that particular component... and to the first response, I need the user to be able to edit the field, and I don't believe just using a calculated field would allow that (in fact I think it would prevent it being editable).

I'm curious how you would use a combination of OnInitEditValue and OnGetDisplayText though, Geert. Basically here's where I'm at now:

Editing the view directly gives a "row could not be located for editing" error, which obviously is due to the design of the view. The workaround I found was to create a second "dummy" column that has no field link, and then use a combination of OnGetDisplayText to populate it and OnValidate to run a SQL update to give the illusion of directly editing. The OnValidate runs perfectly, using the TaskID and the JobID to locate the record to update, BUT I am not requerying the data, so the linked column (which is hidden) remains at its original quantity until I refresh the data. Ideally I would keep it this way, as requerying the data with each edit would likely be too slow...

Is there a reason why my OnGetDisplayText event STILL sets AText to the other columns value even under the if AText = '' condition? It seems like getting this to work would be the simplest solution.

Author Comment

ID: 38338734
Okay, so I noticed something I hadn't before (stupid me). With no events tied to the blank unlinked column my values still do not stick, which is apparently the reason for my line of code not working.

Soo, now the question becomes, do I HAVE to have a field linked to the column in order for it to retain the value? or is there some work around to this?

A possible solution would be simply to refresh only the record that has been edited... Is there a way through delphi to only refresh one SQL record?
LVL 21

Expert Comment

ID: 38339028
Client dataset connects to TDataSource which connects to any data aware component.  If the grid is pulling data from a view, but he wants one column NOT pulled from the view...

It still sounds like a great use for a TClientDataset.
LVL 38

Accepted Solution

Geert Gruwez earned 2000 total points
ID: 38339872
reads data from a view ...

well basically you can edit a view also

i would throw your approach around
create a instead of update/insert trigger on the view in the database
also add the column to the view
>> and then interpret the values in the instead of trigger

write all the logic in the trigger
refreshing the view in delphi will still be needed after your update

this will be way simpler than the manipulations needed in the cx grid

reference for a instead of update trigger in transact-sql:

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question