1

I'm using Delphi 5, and I want to display the results of a MySQL query in a cxGrid. I have the cxGrid, cxGridLevel, and cxGridDBTableView set up the way they default to when added to a form. The DataController.DataSource for the cxGridDBTableView is a TDataSource named DSNewKits with its DataSet set to a tMySQLQuery named NewKitsQry.

When it's time to display the data, I set the Enabled property of DSNewKits to false, run the Close method of NewKitsQry, then set the Enabled property of DSNewKits to true, set the SQL.Text property of NewKitsQry, and run the Open method NewKitsQry. I then have a message dialog display the number of results in the query, and there are 408, so I know the query is working properly. The grid displays rows & columns with lines in between, no "No data to display" message, but all the cells are blank.

I have tried doing these steps in a different order, commenting out some of them, etc., but nothing causes the data to display. I'm sure I'm missing something simple &/or obvious, but I was unable to find an example online. Thank you for any help you can give!

(Edited to include code) Here is my code:


interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  cxGridLevel, cxGridCustomTableView, cxGridTableView, cxGridDBTableView,
  cxClasses, cxControls, cxGridCustomView, cxGrid, ExtCtrls, Db,
  mySQLDbTables, ovcbase, ovcfiler, ovcstore,
  gECCConst,
  StdCtrls;

type
  Tf_cw_cxgrid_db = class(TForm)
    WebCatDB: TmySQLDatabase;
    NewKitsQry: TmySQLQuery;
    DSNewKits: TDataSource;
    Panel1: TPanel;
    cxNewKitsGrid: TcxGrid;
    cxNewKitsGridDBTableView1: TcxGridDBTableView;
    cxNewKitsGridDBTableView1CompanyID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1RegionID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1BranchID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1CustID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1Username: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitComment: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitEffDate: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitExpDate: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitNote: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitType: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitFlags: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitTab: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitAddDate: TcxGridDBColumn;
    cxNewKitsGridDBTableView1KitEditedDate: TcxGridDBColumn;
    cxNewKitsGridDBTableView1Line: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ProductID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1MfgProdID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1PartNum: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ProductDesc: TcxGridDBColumn;
    cxNewKitsGridDBTableView1Qty: TcxGridDBColumn;
    cxNewKitsGridDBTableView1RefQty: TcxGridDBColumn;
    cxNewKitsGridDBTableView1UnitPrice: TcxGridDBColumn;
    cxNewKitsGridDBTableView1UnitPriceType: TcxGridDBColumn;
    cxNewKitsGridDBTableView1UofM: TcxGridDBColumn;
    cxNewKitsGridDBTableView1PSUofM: TcxGridDBColumn;
    cxNewKitsGridDBTableView1UNSPSC: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ItemNote: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ItemGroupID: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ItemDrpDnHdr: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ItemSection: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ItemFlags: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ItemAddDate: TcxGridDBColumn;
    cxNewKitsGridDBTableView1ItemEditedDate: TcxGridDBColumn;
    cxNewKitsGrid1Level1: TcxGridLevel;
    RegistryStore: TOvcRegistryStore;
    userqry: TmySQLQuery;
    Button1: TButton;
    procedure FormShow(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  f_cw_cxgrid_db: Tf_cw_cxgrid_db;

implementation

{$R *.DFM}

procedure Tf_cw_cxgrid_db.FormShow(Sender: TObject);
var
  Server : String;
  User   : String;
  Passwd : String;
  DBName : String;
  Button : Integer;
  WCPath : String;
begin
  RegistryStore.Open;
  Server     := RegistryStore.ReadString( 'SBAdmin', 'Host',     '127.0.0.1');
  User       := RegistryStore.ReadString( 'SBAdmin', 'User',     '');
  Passwd     := RegistryStore.ReadString( 'SBAdmin', 'Password', '');
  DBName     := RegistryStore.ReadString( 'SBAdmin', 'Database', WebCatDefUserDBName );  { caw 7-24-20 }
  RegistryStore.Close;

  WebCatDB.Host              := Server;
  WebCatDB.UserName          := User;
  WebCatDB.UserPassword      := Passwd;
  WebCatDB.DatabaseName      := 'WEBCAT_' + DBName;
end;

procedure Tf_cw_cxgrid_db.Button1Click(Sender: TObject);
begin
  DSNewKits.Enabled := False;
  NewKitsQry.Close;
  try
    DSNewKits.Enabled := True;
    NewKitsQry.SQL.Text := 'SELECT KitHdrs.CompanyID, KitHdrs.RegionID, KitHdrs.BranchID, KitHdrs.CustID, ' +
                           'KitHdrs.Username, KitHdrs.KitID, KitHdrs.KitComment, KitHdrs.KitEffDate, ' +
                           'KitHdrs.KitExpDate, KitHdrs.KitNote, KitHdrs.KitType, KitHdrs.KitFlags, KitHdrs.KitTab, ' +
                           'KitHdrs.KitAddDate, KitHdrs.KitEditedDate, KitLines.Line, KitLines.ProductID, ' +
                           'KitLines.MfgProdID, KitLines.PartNum, KitLines.ProductDesc, KitLines.Qty, ' +
                           'KitLines.RefQty, KitLines.UnitPrice, KitLines.UnitPriceType, KitLines.UofM, ' +
                           'KitLines.PSUofM, KitLines.UNSPSC, KitLines.ItemNote, KitLines.ItemGroupID, ' +
                           'KitLines.ItemDrpDnHdr, KitLines.ItemSection, KitLines.ItemFlags, ' +
                           'KitLines.ItemAddDate, KitLines.ItemEditedDate ' +
                           'FROM KitHdrs JOIN KitLines ON KitHdrs.KitSeq = KitLines.KitSeq;';
    NewKitsQry.Open;
    cxNewKitsGridDBTableView1.DataController.CreateAllItems;

    MessageDlg( 'There are ' + IntToStr( NewKitsQry.RecordCount ) + ' records.', mtInformation, [mbOK], 0 );
  except
    MessageDlg( 'There was an error displaying the kit databases.', mtError, [mbOK], 0 );
  end;
end;

end.
Keila
  • 113
  • 6
  • Please post code (Pascal and the form) in your q and don't describe it. This is more helpful and faster for reproducing your problem. – Delphi Coder Aug 18 '20 at 07:21

1 Answers1

2

Although the cxGrid is a fine component, the fact that it has so many deeply-nested properties can make it quite daunting to set up a cxGrid from scratch.

I created the example below to show how to create and set up a cxGrid entirely in code so that you can easily see the bare minimum which needs to be done. It uses a TClientDataSet, which is populated in code, to supply the grid's data so that the example is completely self-contained. It would be trivial to adapt it to an existing MySql dataset.

type
  TForm1 = class(TForm)
    CDS1: TClientDataSet;
    DS1: TDataSource;
    DBNavigator1: TDBNavigator;
    Button1: TButton;
    procedure FormCreate(Sender: TObject);
  private
  protected
  public
    cxGrid : TcxGrid;
    cxLevel : TcxGridLevel;
    cxView : TcxGridDBTableView;
  end;
[...]
// This is a utility function to create TFields in code
function CreateField(AFieldClass : TFieldClass; AOwner : TComponent; ADataSet : TDataSet;
AFieldName, AName : String; ASize : Integer; AFieldKind : TFieldKind) : TField;
begin
  Result := AFieldClass.Create(AOwner);
  Result.FieldKind := AFieldKind;
  Result.FieldName := AFieldName;
  Result.Name := AName;
  Result.Size := ASize;
  Result.DataSet := ADataSet;
end;

procedure TForm1.FormCreate(Sender: TObject);
var
  i : Integer;
  Field : TField;
begin
  //  All the code to set up the cxGrid is in this event handler
  
  //  First, create the Fields of the ClientDataSet
  Field := CreateField(TIntegerField, Self, CDS1, 'ID', 'CDS1ID', 0, fkData);
  Field := CreateField(TIntegerField, Self, CDS1, 'Qty', 'CDS1Qty', 0, fkData);
  Field := CreateField(TCurrencyField, Self, CDS1, 'UnitPrice', 'CDS1UnitPrice', 0, fkData);
  CDS1.CreateDataSet;

  CDS1.IndexFieldNames := 'ID';

  //  Next, populate the CDS with a few records
  CDS1.InsertRecord([1, 1, 1]);
  CDS1.InsertRecord([2, 2, 5]);
  CDS1.InsertRecord([3, 3, 6]);

  CDS1.First;

  DS1.DataSet := CDS1;

  //  Now, create a cxGrid to display the CDS data
  cxGrid := TcxGrid.Create(Self);
  cxGrid.Parent := Self;
  cxGrid.Width := 400;

  cxLevel := cxGrid.Levels.Add;
  cxLevel.Name := 'Firstlevel';

  cxView := cxGrid.CreateView(TcxGridDBTableView) as TcxGridDBTableView;
  cxView.Name := 'ATableView';
  cxView.DataController.KeyFieldNames := 'ID';

  cxLevel.GridView := cxView;
  cxView.DataController.DataSource := DS1;
  cxView.DataController.CreateAllItems;
end;
MartynA
  • 30,454
  • 4
  • 32
  • 73
  • I don't see how the data source DS1 get connected with the data set CDS1. Is the DataSet property of DS1 set to CDS1 (at design time)? – Keila Aug 20 '20 at 16:36
  • 1
    Sorry, I thought that was so obvious I left it out, but I'll add it to the code - in Delphi (Or you could do it in the Object Inspector at design time), TDataSource is the (only) conduit for data in a dataset like CDS1 to a db-aware control like TcxGrid. – MartynA Aug 20 '20 at 16:46
  • I'm afraid I'm missing something just as obvious, which is why I asked. I'm still missing something; my data isn't showing. – Keila Aug 20 '20 at 16:55
  • As well as whether my code works, please tell me the result of this 30-second experiment: Place a TDBGrid (from the DataControls tab of the Component Palette) on your form, and set its DataSource property to your DSNewKits. Run the app. Does the DBGrid display the data records or not? – MartynA Aug 20 '20 at 17:13
  • The CDS is on the DataAccess tab, right next to TDataSource. – MartynA Aug 20 '20 at 17:54
  • Yes, the DBGrid displays the records. I'm still working on running your code. – Keila Aug 20 '20 at 17:54
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/220163/discussion-between-keila-and-martyna). – Keila Aug 20 '20 at 17:54
  • Okay; it just kept prompting me to move it to chat. Yes, the DBGrid displays the records. In mine, the CDS was on the Midas palette, but I finally found it. When I run your code, it shows – Keila Aug 21 '20 at 12:52
  • Let's see if we can get my code working for you first, because it is so simple that if we can't, I don't see any prospect of getting yours working. I just created a completely new Delphi (7) project and transplanted the code from my answer. It compiled and ran and displayed the 3 records. Please do that, but before you run the app, put a debugger breakpoint on the first `CDS1.InsertRecord...` line. Does the debugger stop on this breakpoint or not? I ask because I can only get your result if I comment out the CDS1.InsertRecord lines. – MartynA Aug 21 '20 at 13:26
  • Well, I feel like a dummy, but I had forgotten to put the FormCreate procedure in the OnCreate event of the form. Once I did that, it worked fine. – Keila Aug 21 '20 at 18:18
  • Well, it's great you have found it where it was coming off the rails! It would be good if you could be so kind as to accept my answer, too. – MartynA Aug 21 '20 at 18:23
  • As there is no obvious reason why your cxGrid isn't working, in your position I would do this: Start a completely new project containing only a single form. On it put **only** a blank DBGrid, from the ComponentPalette, connect it to a TDataSource and that to your MySql dataset component. Connect them up and use the code in my answer to create the cxGrid. Do you still get the result that the DBGrid shows the data but the cxGrid doesn't? I'm suggesting trying this because your form contains other components like TOvcRegistryStore from the Orpheus library which may be interfering. – MartynA Aug 21 '20 at 18:44
  • That allowed me to figure it out. Apparently it didn't connect all those columns I had created at design time with the data; when I use DataController.CreateAllItems instead, it works. Thank you immensely for your help! – Keila Aug 21 '20 at 19:25