Dynamic field problem in VirtualTable
Dynamic field problem in VirtualTable
I create the dynamic field in Virtualtable and use the EMS Advance Import Xls component to import the data into virtualtable. But the datetime field, float, integer is empty after import. If I hardcode the field attrib in the coding, then the data is OK. Any solution?
I want to use the "Red Color" coding only. Any idea?
while not map_setting.Eof do
begin
if map_setting.FieldByName('map_del_attrib').AsString = 'ftDateTime' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftDateTime, 0);
end
else
if map_setting.FieldByName('map_del_attrib').AsString = 'ftFloat' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftFloat, 0);
end
else
if map_setting.FieldByName('map_del_attrib').AsString = 'ftInteger' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftInteger, 0);
end
else
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
map_setting.FieldByName('map_del_attrib').DataType,
map_setting.FieldByName('map_field_length').Value); end;
map_setting.Next;
end;
I want to use the "Red Color" coding only. Any idea?
while not map_setting.Eof do
begin
if map_setting.FieldByName('map_del_attrib').AsString = 'ftDateTime' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftDateTime, 0);
end
else
if map_setting.FieldByName('map_del_attrib').AsString = 'ftFloat' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftFloat, 0);
end
else
if map_setting.FieldByName('map_del_attrib').AsString = 'ftInteger' then
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
ftInteger, 0);
end
else
begin
poreport_buffer.FieldDefs.Add(map_setting.FieldByName('map_table_field').Value,
map_setting.FieldByName('map_del_attrib').DataType,
map_setting.FieldByName('map_field_length').Value); end;
map_setting.Next;
end;
Re: Dynamic field problem in VirtualTable
Hello,
In your highlighted code you are trying to use the DataType property that is always ftString, thus you should look for the type corresponding to the name, e.g. TFieldType.
In your highlighted code you are trying to use the DataType property that is always ftString, thus you should look for the type corresponding to the name, e.g. TFieldType.
Code: Select all
const
FieldTypeStr: array [0..11] of string = ('ftUnknown', 'ftString', 'ftSmallint', 'ftInteger', 'ftWord',
'ftBoolean', 'ftFloat', 'ftCurrency', 'ftBCD', 'ftDate', 'ftTime', 'ftDateTime');
var
i: integer;
begin
VirtualTable2.Open;
while not VirtualTable2.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if VirtualTable2.FieldByName('map_del_attrib').AsString = FieldTypeStr[i] then
begin
VirtualTable1.FieldDefs.Add(VirtualTable2.FieldByName('map_table_field').Value, TFieldType(i) ,VirtualTable2.FieldByName('map_field_length').Value);
break;
end;
VirtualTable2.Next;
end;
VirtualTable1.Open;
end;
Re: Dynamic field problem in VirtualTable
Thanks a lot! It works
Re: Dynamic field problem in VirtualTable
How to create the new calculated or lookup field in virtualtable by dynamic query?
Re: Dynamic field problem in VirtualTable
Hello,
There is a code below for adding both simple and calculated fields in run-time and calculation of this field value.
There is a code below for adding both simple and calculated fields in run-time and calculation of this field value.
Code: Select all
type
TForm1 = class(TForm)
VirtualTable1: TVirtualTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
procedure FormCreate(Sender: TObject);
procedure VirtualTable1CalcFields(DataSet: TDataSet);
private
{ Private declarations }
FNAME: TStringField;
FSAL: TCurrencyField;
FBONUS: TFloatField;
FSUM: TCurrencyField;
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.FormCreate(Sender: TObject);
begin
FNAME := TStringField.Create(VirtualTable1);
FNAME.FieldName := 'Name';
FNAME.DataSet := VirtualTable1;
FSAL := TCurrencyField.Create(VirtualTable1);
FSAL.FieldName := 'Sal';
FSAL.DataSet := VirtualTable1;
FBONUS := TFloatField.Create(VirtualTable1);
FBONUS.FieldName := 'Bonus';
FBONUS.DataSet := VirtualTable1;
FSUM := TCurrencyField.Create(VirtualTable1);
FSUM.FieldName := 'Sum';
FSUM.FieldKind := fkCalculated;
FSUM.DataSet := VirtualTable1;
VirtualTable1.Open;
VirtualTable1.Edit;
VirtualTable1.Fields[0].AsString := 'SCOTT';
VirtualTable1.Fields[1].AsCurrency := 100;
VirtualTable1.Fields[2].AsFloat := 1.2;
VirtualTable1.Post;
end;
procedure TForm1.VirtualTable1CalcFields(DataSet: TDataSet);
begin
FSUM.Value := FSAL.Value*FBONUS.Value;
end;
Re: Dynamic field problem in VirtualTable
Thanks for your hints how to create calculated field. But I need to create the dynamic field too. I used your hint to create the following code. But it has the error that is "Field 'po_batch_nbr' not found"
while not msqry_extfield.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if msqry_extfield.FieldByName('add_table_del_attrib').AsString = FieldTypeStr then
begin
poreport_buffer.FieldDefs.Add(msqry_extfield.FieldByName('add_table_field').Value, TFieldType(i) ,msqry_extfield.FieldByName('add_table_field_len').Value);
poreport_buffer.FieldDefs.Update;
poreport_buffer.FieldByName(msqry_extfield.FieldByName('add_table_field').Value).FieldKind := fkCalculated;
poreport_buffer.FieldDefs.Update;
break;
end;
msqry_extfield.Next;
end;
while not msqry_extfield.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if msqry_extfield.FieldByName('add_table_del_attrib').AsString = FieldTypeStr then
begin
poreport_buffer.FieldDefs.Add(msqry_extfield.FieldByName('add_table_field').Value, TFieldType(i) ,msqry_extfield.FieldByName('add_table_field_len').Value);
poreport_buffer.FieldDefs.Update;
poreport_buffer.FieldByName(msqry_extfield.FieldByName('add_table_field').Value).FieldKind := fkCalculated;
poreport_buffer.FieldDefs.Update;
break;
end;
msqry_extfield.Next;
end;
Re: Dynamic field problem in VirtualTable
Hello,
FieldDefs, not Fields, are created in the provided code. To create fields and modify their properties, you should add a line for creation of field by its FieldDef to the loop.
FieldDefs, not Fields, are created in the provided code. To create fields and modify their properties, you should add a line for creation of field by its FieldDef to the loop.
Code: Select all
procedure TForm1.FormCreate(Sender: TObject);
const
FieldTypeStr: array [0..11] of string = ('ftUnknown', 'ftString', 'ftSmallint', 'ftInteger', 'ftWord',
'ftBoolean', 'ftFloat', 'ftCurrency', 'ftBCD', 'ftDate', 'ftTime', 'ftDateTime');
var
i: integer;
begin
VirtualTable2.Open;
while not VirtualTable2.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if VirtualTable2.FieldByName('map_del_attrib').AsString = FieldTypeStr[i] then
begin
VirtualTable1.AddField(VirtualTable2.FieldByName('map_table_field').Value, TFieldType(i) ,VirtualTable2.FieldByName('map_field_length').Value);
VirtualTable1.FieldDefs[VirtualTable1.FieldDefs.IndexOf(VirtualTable2.FieldByName('map_table_field').Value)].CreateField(VirtualTable1);
VirtualTable1.FieldByName(VirtualTable2.FieldByName('map_table_field').Value).FieldKind := fkCalculated;
break;
end;
VirtualTable2.Next;
end;
VirtualTable1.Open;
end;
Re: Dynamic field problem in VirtualTable
That's great. You help me to solve my problem.
Re: Dynamic field problem in VirtualTable
hello,
Glad to see that the problem was solved. If you have any other questions, feel free to contact us.
Glad to see that the problem was solved. If you have any other questions, feel free to contact us.
Re: Dynamic field problem in VirtualTable
Hi, I have one more question about calculated field. Can I assign the value dynamically?
The following is hardcode in the oncalcfield event. But I want to dynamic assign by table records. Because I don't want to hardcode in coding. I want to control by table records. Then the program need not to recomplie when add the fields.
procedure Tfrm_ImportDialog.poreport_bufferCalcFields(DataSet: TDataSet);
begin
poreport_buffer.FieldByName('shp_type').Value := tran_code;
end;
The following is hardcode in the oncalcfield event. But I want to dynamic assign by table records. Because I don't want to hardcode in coding. I want to control by table records. Then the program need not to recomplie when add the fields.
procedure Tfrm_ImportDialog.poreport_bufferCalcFields(DataSet: TDataSet);
begin
poreport_buffer.FieldByName('shp_type').Value := tran_code;
end;
Re: Dynamic field problem in VirtualTable
Why I got the field not found error as following code? If remark shp_createdate and shp_modifydate in oncalcfields event, then the error is gone.
while not msqry_extfield.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if msqry_extfield.FieldByName('add_table_del_attrib').AsString = FieldTypeStr then
begin
poreport_buffer.AddField(msqry_extfield.FieldByName('add_table_field').Value, TFieldType(i) ,msqry_extfield.FieldByName('add_table_field_len').Value);
poreport_buffer.FieldDefs[poreport_buffer.FieldDefs.IndexOf(msqry_extfield.FieldByName('add_table_field').Value)].CreateField(poreport_buffer);
poreport_buffer.FieldByName(msqry_extfield.FieldByName('add_table_field').Value).FieldKind := fkCalculated;
break;
end;
msqry_extfield.Next;
end;
poreport_buffer.Active := true;
procedure Tfrm_ImportDialog.poreport_bufferCalcFields(DataSet: TDataSet);
begin
poreport_buffer.FieldByName('shp_type').Value := tran_code;
// poreport_buffer.FieldByName('shp_createdate').Value := date;
// poreport_buffer.FieldByName('shp_modifydate').AsDateTime := now;
end;
while not msqry_extfield.Eof do
begin
for i := low(FieldTypeStr) to high(FieldTypeStr) do
if msqry_extfield.FieldByName('add_table_del_attrib').AsString = FieldTypeStr then
begin
poreport_buffer.AddField(msqry_extfield.FieldByName('add_table_field').Value, TFieldType(i) ,msqry_extfield.FieldByName('add_table_field_len').Value);
poreport_buffer.FieldDefs[poreport_buffer.FieldDefs.IndexOf(msqry_extfield.FieldByName('add_table_field').Value)].CreateField(poreport_buffer);
poreport_buffer.FieldByName(msqry_extfield.FieldByName('add_table_field').Value).FieldKind := fkCalculated;
break;
end;
msqry_extfield.Next;
end;
poreport_buffer.Active := true;
procedure Tfrm_ImportDialog.poreport_bufferCalcFields(DataSet: TDataSet);
begin
poreport_buffer.FieldByName('shp_type').Value := tran_code;
// poreport_buffer.FieldByName('shp_createdate').Value := date;
// poreport_buffer.FieldByName('shp_modifydate').AsDateTime := now;
end;
Re: Dynamic field problem in VirtualTable
Hello,
The value calculating procedure for calculated fields occurs only in the onCalcFields event, therefore you should create this method and set the required actions in it. You can find more information on calculated fields in the Delphi help.
The value calculating procedure for calculated fields occurs only in the onCalcFields event, therefore you should create this method and set the required actions in it. You can find more information on calculated fields in the Delphi help.
Re: Dynamic field problem in VirtualTable
I have the problem again about ftWideString attrib in VirtualTable. If I create the dynamic field and set it ftWideString, then it has the error said "Invalid field size" How to solve it. Because I found that it has unknown character if define ftString. My program need to handle chinese characters (Traditional & Simplified. That means unicode handling)
Re: Dynamic field problem in VirtualTable
Additional information about my version. Delphi is XE2 and SDAC is 6.6.11
And I found the VirtualTable is lost in 6.6.11. Before I use Delphi 2007 & SDAC 4.8. Now I need to convert the new version because the unicode issue.
And I found the VirtualTable is lost in 6.6.11. Before I use Delphi 2007 & SDAC 4.8. Now I need to convert the new version because the unicode issue.
Re: Dynamic field problem in VirtualTable
Hello,
Please specify the moment when this error occurs: during field creation, during value assigning, during table opening, etc.
The VirtualTable is at the Data Access tab
Please specify the moment when this error occurs: during field creation, during value assigning, during table opening, etc.
The VirtualTable is at the Data Access tab