Posted on 2012-08-25
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
    LVL 9

    Expert Comment

    My hint -  "Calculated Field".
    LVL 21

    Expert Comment

    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 36

    Expert Comment

    by:Geert Gruwez
    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
    LVL 36

    Expert Comment

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

    Author Comment

    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

    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

    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 36

    Accepted Solution

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now