DBGrid: Calculated Fields and Lookup Fields

[Stefano]

Hello,

I am new in Lazaryus (I use vers. 0.9.12beta), I come from Delphi. I'd like to put a Calculated Field and a Lookup Field on a DbGrid. I use SqLdb with IBConnection, SQLQuery and SQLTransaction. I've tried, but at design time I cannot set a field as Calculated or Lookup, and I'm not able to set at run time a new field for a new column.

I've searched a lot with Google, but I've find no example for that. Could someone help me?

Thank you very much.

Best regards,

Stefano

[matthijs]

You should not add these to the DBGrid but to the dataset, in your case the SQLQuery.

[Anonymous]

Thank you for your fast reply, the problem is that I don't know how to associate the new field created to the DBGrid at Runtime. I haven't found a DataField or FieldName property in the DBGRid.Columns[X] Property.

Here there is my code:

  Var MyField: TField;

  MyField := TField.Create(Self);
  MyField.FieldName := 'CALC';
  MyField.FieldKind := fkCalculated;
  MyField.ReadOnly := True;
  sqlquery1.Fields.Add(MyField);

But now I am not able to set the 'CALC' Field to the right column of my DBGrid.

Thank you very much if you can help me.

Best regards,

Stefano

[Anonymous]

I've tried a lot, but still I cannot add a calculated field or a lookup field. I've also tried to install vers. 0.9.13 with FPC 2.1.1. Could someone tell me witch code have I to use for:

1) Creating a calculated or a lookup field at runtime;

2) Associete the new field to the DBGrid at runtime (I haven't found the FieldName property in DbGrid.Columns [X]).

Thank you very very much if you can help me.

Best Regards,

Stefano

[Gianclaudio]

If you trying connect to Interbase/Firebird database, you can use other database components, that offers calculated-fields creation in Designtime:
UIB or ZeosBDO.

ZeosDBO is very good but your installation is lighly hard.
http://sourceforge.net/projects/zeoslib

UIB is very good if you use in association with AlexsDataset (FBDataset component). The FBDataset of AlexsDataset package offers various recourses for IB/FB databases acess and editing. Your installation is easy.
http://www.progdigy.com/modules.php?name=UIB
Here, you can download the UIB 2.0 components package and have link for AlexsDataset (on "Download Alternative Dataset"). Today's URL for this link is http://www.progdigy.com/modules.php?name=Downloads&d_op=getit&lid=6

Good Luck!!
Sorry for my bad English.

[Anonymous]

Thank you for your reply, I have correctly installed ZEOSLib and UIB with FBDataSet. However I have problems to use them:

1) Zeos: At design time if I set ZConnection.Connected = True I receive this error: "SQL Error: unavailable database. Error Code: -904. can't format message 13:96 - message file c:\Program Files\Borland\InterBase/interbase.msg not found".

I can set this property to True at runtime without errors, but after, at runtime, I cannot activate Dataset.

I use Firebird 1.5.3 Server (NOT the Embedded).

2) UIB: Here things are better, I can set the database, transcation and FBDataSet properties correctly at design time. The problem is that when I run the program the dataset is readonly and I cannot move among the records, I can only stay on the first record and change columns of the DBGrid but not change row, and I cannot change data.

I've tried a lot to set the various properties of FBDataSet, AllowupdateKinds allows Modify, Insert, Delete, Fields have ReadOnly property to False. JvUIBTransaction have set nothing in LockRead and LockWrite properties (as default), Options are as default with tpRead = False and tpWrite = True, tpLockRead = False, tpLockWrite = False (the problem still ramain even with toRead = True).

Could you help me? It's one week I'm trying on this, but still I don't know how I can do for use Lazarus and data-aware components with calculated or lockup fileds.

A great thanks if someone can help me.

Best regards,

Stefano

[Anonymous]

in FBDataSet write to SQLUpdate, SQLDelete, SQLInsert correct sql text for update, delete and insert record

[Anonymous]

Thank you very much! Now I can insert and modify records, calculated fields work well.

However, it remains the problem I cannot move among records, the FBDataset still remain always on the first record.

What can I do? I post the settings of FBDataSet (from my lfm file), I cannot understand where I wrong.

Thank you again for your help.

Best regards,

Stefano





  object FBDataSet1: TFBDataSet
    Active = True
    AfterInsert = FBDataSet1AfterInsert
    OnCalcFields = FBDataSet1CalcFields
    AutoUpdateOptions.IncrementBy = 1
    DataSource = Datasource3
    DefaultFormats.DisplayFormatNumeric = '#,##0.0'
    DefaultFormats.DisplayFormatInteger = '#,##0'
    DataBase = JvUIBDataBase1
    Macros = <>
    Option = [poTrimCharFields, poRefreshAfterPost]
    Transaction = JvUIBTransaction1
    UpdateTransaction = JvUIBTransaction1
    UpdateRecordTypes = [cusUnmodified, cusModified, cusInserted]
    SQLSelect.Strings = (
      'SELECT * FROM TABELLA1'
    )
    SQLRefresh.Strings = (
      'select'
      '  *'
      'from'
      '  TABELLA1'
      'where'
      '  TABELLA1.NUMERO = :NUMERO'
      '  '
    )
    SQLEdit.Strings = (
      'update'
      '  TABELLA1'
      'set '
      '  DESCRIZIONE = :DESCRIZIONE,'
      '  CATEGORIA = :CATEGORIA'
      'where'
      '  TABELLA1.NUMERO = :NUMERO'
    )
    SQLDelete.Strings = (
      'delete'
      'from'
      '  TABELLA1'
      'where'
      '  TABELLA1.NUMERO = :NUMERO'
    )
    SQLInsert.Strings = (
      'insert into TABELLA1'
      '(NUMERO, DESCRIZIONE, CATEGORIA)'
      'values (:NUMERO, :DESCRIZIONE, :CATEGORIA)'
    )
    left = 284
    top = 612
    object FBDataSet1NUMERO1: TLongintField
      AlignMent = taRightJustify
      DisplayWidth = 10
      FieldName = 'NUMERO'
      DisplayFormat = '#,##0'
    end
    object FBDataSet1CALC1: TFBStringField
      DisplayWidth = 10
      FieldKind = fkCalculated
      FieldName = 'CALC'
      Index = 1
      Size = 10
    end
    object FBDataSet1CATEGORIA1: TLongintField
      AlignMent = taRightJustify
      DisplayWidth = 10
      FieldName = 'CATEGORIA'
      Index = 2
      DisplayFormat = '#,##0'
    end
    object FBDataSet1DESCRIZIONE1: TFBStringField
      DisplayWidth = 10
      FieldName = 'DESCRIZIONE'
      Index = 3
      Size = 10
    end
  end
  object JvUIBDataBase1: TJvUIBDataBase
    Params.Strings = (
      'sql_dialect=3'
      'lc_ctype=NONE'
      'user_name=SYSDBA'
      'password=masterkey'
    )
    DatabaseName = 'e:\delphi\firebird\serverprova.gdb'
    UserName = 'SYSDBA'
    PassWord = 'masterkey'
    LibraryName = 'fbclient.dll'
    MetaDataOptions.Procedures = [OIDProcFieldOut]
    Connected = True
    left = 321
    top = 612
  end
  object JvUIBTransaction1: TJvUIBTransaction
    DataBase = JvUIBDataBase1
    Options = [tpConcurrency, tpWait, tpRead, tpWrite]
    left = 357
    top = 608
  end

[matthijs]

How do you move from one record to the next? The obvious way would be

Code: [Select]

 FBDataset1.Next;

[Anonymous]

I use dataaware components, so I don't use any command to moving into the dataset. Either if I try to changing rows of the DBGrid (Up and Downs arrows, PgUp or PgDown), or if I use the "Next" or "Last" button ot the DBNavigator, I cannot change record, I can only stay on the first record.

With JvUIBDataSet it works, but JVUIBDataSet doesn't have OnCalcFields event and has less features.

Besides, I'm trying to understand how update by code the server; if I set the CacheUpdates to False every change is immediately posted to the server, if I set the property to True, the FBDataSet.ApplyUpdates and JVUIBTRansaction.Commit procedures don't update the server.

A very great "THANKS" if you can help me.

Best regards,

Stefano

[Anonymous]

Hello, I've found the problem, if in FBDataSet.DataSource I set nothing all works correctly.

Before, I've setted either in FBDataSet.DataSource = DataSource1, or DataSource1.DataSet = FBDataSet.

Now, I've setted DataSoruce1.DataSet = FBDataSet, this is ok, but I have to leave FBDataSet.DataSource property empty, and all works correctly.

Thank you again and sorry for my bad English.

Best Regards,

Stefano

[Anonymous]

If you trying connect to Interbase/Firebird database, you can use other database components, that offers calculated-fields creation in Designtime:
UIB or ZeosBDO.

ZeosDBO is very good but your installation is lighly hard.
http://sourceforge.net/projects/zeoslib

UIB is very good if you use in association with AlexsDataset (FBDataset component). The FBDataset of AlexsDataset package offers various recourses for IB/FB databases acess and editing. Your installation is easy.
http://www.progdigy.com/modules.php?name=UIB
Here, you can download the UIB 2.0 components package and have link for AlexsDataset (on "Download Alternative Dataset"). Today's URL for this link is http://www.progdigy.com/modules.php?name=Downloads&d_op=getit&lid=6

Good Luck!!
Sorry for my bad English.

Hi,

I've installed Zeos components but I can't find a way to create look-up or calculated fields at design time. Can you tell me step by step how to do that?

[Anonymous]

Hello,

You can use the FieldEditors: mouse right click on dataset component (that must be active), select the Field Editor, than mouse right click again, New, this allows you to create a New Field, you can choose for Lookup Field or Calculated Field.

I hope this could help you, unfortunately, ZeosLib don't work on my Pc (I receive error -904 Database unavailable), this is why I use UIB.

Best Regards,

Stefano

[Anonymous]

Hi,

I don't know if the problem from Stefano is allready resolved but there it goes.

I had a problem with Interbase and Firebird. I installed the Interbase and after I uninstalled it and installed the Firebird, but the problem above started:

"can't format message 13:96 - message file c:\Program Files\Borland\InterBase/interbase.msg not found"

I resolved it taking away the GDS32.DLL installed by the Interbase and putting the GDS32.DLL installed by the Firebird. I mean, the Interbase install the GDS32.DLL at c:\windows\system32 but the Firebird installer don't change it.

Hope its help.

Otávio

[Stealth]

hello everyone
i am working on bcb 6.0 i have a requirement of using calculated field in dbgrid i did the following things

       TDataSetField *f1 = new TDataSetField(this);
        f1->FieldKind = fkCalculated;
        f1->FieldName = "dynamic field";
        f1->DataSet = DataSource1->DataSet;
        f1->Name = "f1";
        MyQuery1->Active = true;

but as the last statement is executed i get an error that 'dynamic field cannnot be a calculated or lookup field'
if u have the solution for it then i request u to post it