BorlandTalk.com

user · Guest

Hello,

Here's an example of what I'm trying to fix:

/* SQL */

CREATE TABLE JOBS (
ID INTEGER NOT NULL,
NAME VARCHAR(25)
);

/* The first record of JOBS table, ID=0, has a NAME value of '(None)' */

ALTER TABLE JOBS
ADD CONSTRAINT PK_JOBS PRIMARY KEY (ID);

CREATE GENERATOR EMPLOYEE_ID;

CREATE TABLE EMPLOYEES (
ID INTEGER NOT NULL,
NAME VARCHAR(25),
JOB_ID INTEGER DEFAULT 0 NOT NULL
);

ALTER TABLE EMPLOYEES
ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID);

ALTER TABLE NEW_TABLE
ADD CONSTRAINT NEW_TABLE_JOBS FOREIGN KEY (JOB_ID)
REFERENCES JOBS (ID) ON DELETE SET DEFAULT ON UPDATE CASCADE;

SET TERM ^ ;

CREATE TRIGGER EMPLOYEES_BI0 FOR EMPLOYEES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if (NEW.ID is NULL) then
NEW.ID = GEN_ID(EMPLOYEE_ID, 1);
if (NEW.JOB_ID is NULL) then
NEW.JOB_ID = 0;
end
^

/* DFMs */

object DMod: TDMod
OldCreateOrder = False
Left = 201
Top = 379
Height = 257
Width = 433
object DB: TIBDatabase
Connected = True
DatabaseName = 'c:\data\test.gdb'
Params.Strings = (
'user_name=sysdba'
'password=masterkey'
'lc_ctype=ASCII')
LoginPrompt = False
DefaultTransaction = Trans
IdleTimer = 0
SQLDialect = 1
TraceFlags = []
Left = 20
Top = 20
end
object Employees: TIBDataSet
Database = DB
Transaction = Trans
AfterPost = EmployeesAfterPost
BufferChunks = 10
CachedUpdates = False
DeleteSQL.Strings = (
'delete from EMPLOYEES'
'where'
' ID = :OLD_ID')
InsertSQL.Strings = (
'insert into EMPLOYEES'
' (ID, NAME, JOB_ID)'
'values'
' (:ID, :NAME, :JOB_ID)')
RefreshSQL.Strings = (
'Select '
' ID,'
' NAME,'
' JOB_ID'
'from EMPLOYEES'
'where'
' ID = :ID')
SelectSQL.Strings = (
'select *'
'from EMPLOYEES'
'where ID > 0'
'order by ID')
ModifySQL.Strings = (
'update EMPLOYEES'
'set'
' ID = :ID,'
' NAME = :NAME,'
' JOB_ID = :JOB_ID'
'where'
' ID = :OLD_ID')
Active = True
Left = 20
Top = 70
object EmployeesID: TIntegerField
FieldName = 'ID'
Origin = 'EMPLOYEES.ID'
end
object EmployeesNAME: TIBStringField
DisplayWidth = 15
FieldName = 'NAME'
Origin = 'EMPLOYEES.NAME'
Size = 25
end
object EmployeesJOB_ID: TIntegerField
AutoGenerateValue = arDefault
FieldName = 'JOB_ID'
Origin = 'EMPLOYEES.JOB_ID'
end
object EmployeesJOB_L: TIBStringField
FieldKind = fkLookup
FieldName = 'JOB_L'
LookupDataSet = Jobs
LookupKeyFields = 'ID'
LookupResultField = 'ID_NAME_C'
KeyFields = 'JOB_ID'
Size = 25
Lookup = True
end
end
object Trans: TIBTransaction
Active = True
DefaultDatabase = DB
DefaultAction = TACommitRetaining
Params.Strings = (
'read_committed'
'rec_version'
'nowait')
AutoStopAction = saCommitRetaining
Left = 70
Top = 20
end
object EmpSrc: TDataSource
DataSet = Employees
Left = 20
Top = 120
end
object JobsSrc: TDataSource
DataSet = Jobs
Left = 70
Top = 120
end
object Jobs: TIBQuery
Database = DB
Transaction = Trans
OnCalcFields = JobsCalcFields
Active = True
BufferChunks = 10
CachedUpdates = False
SQL.Strings = (
'select *'
'from JOBS'
'order by NAME')
Left = 70
Top = 75
object JobsID: TIntegerField
FieldName = 'ID'
Origin = 'JOBS.ID'
Required = True
end
object JobsNAME: TIBStringField
FieldName = 'NAME'
Origin = 'JOBS.NAME'
Size = 25
end
object JobsID_NAME_C: TIBStringField
FieldKind = fkCalculated
FieldName = 'ID_NAME_C'
Size = 35
Calculated = True
end
end
end

object fMain: TfMain
Left = 202
Top = 109
BorderStyle = bsSingle
Caption = 'Test'
ClientHeight = 147
ClientWidth = 270
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Label1: TLabel
Left = 15
Top = 60
Width = 11
Height = 13
Caption = 'ID'
end
object Label2: TLabel
Left = 15
Top = 90
Width = 28
Height = 13
Caption = 'Name'
end
object Label3: TLabel
Left = 15
Top = 120
Width = 17
Height = 13
Caption = 'Job'
end
object DBEdit1: TDBEdit
Left = 55
Top = 55
Width = 36
Height = 21
DataField = 'ID'
DataSource = DMod.EmpSrc
TabOrder = 0
end
object DBEdit2: TDBEdit
Left = 55
Top = 85
Width = 121
Height = 21
DataField = 'NAME'
DataSource = DMod.EmpSrc
TabOrder = 1
end
object DBLookupComboBox1: TDBLookupComboBox
Left = 55
Top = 115
Width = 121
Height = 21
DataField = 'JOB_L'
DataSource = DMod.EmpSrc
TabOrder = 2
end
object DBNavigator1: TDBNavigator
Left = 20
Top = 10
Width = 240
Height = 25
DataSource = DMod.EmpSrc
TabOrder = 3
end
end

/* C++ */

void __fastcall TDMod::JobsCalcFields(TDataSet *DataSet)
{
JobsID_NAME_C->AsString = "[" + JobsID->AsString + "] " +
JobsNAME->AsString;
}
//--------------------------------------------------------------------------
-

void __fastcall TDMod::EmployeesAfterPost(TDataSet *DataSet)
{
Trans->CommitRetaining();
}
//--------------------------------------------------------------------------
-

==================================

So, the problem is that I want to be able for the user to be completely
ignorant of the Employees.ID field. It has a generator. I'd like to
eliminate the field from the form entirely. I also want the default value
for Employees.JOB_ID to be looked up for the TDBLookupComboBox if the user
doesn't provide a value.

What happens is, when you click the "+" button on the navigator, you get the
expected blank record to fill in. If only the NAME field is entered, then
the Post button is clicked, the ID and JOB fields remain blank, instead of
showing their generated and default values. How do I get those values to
appear on POST?

Many thanks to all,

Terry

Wayne Niddery [TeamB] · Guest

user wrote:

Terry · Guest

OK, I'm trying this for a BeforeInsert event handler on the EMPLOYEES table:

void __fastcall TDMod::EmployeesBeforeInsert(TDataSet *DataSet)
{
TIBQuery *Qry = new TIBQuery(this);

Qry->Database = DB;
Qry->SQL->Text = "select Gen_ID(employee_id, 1) from rdb$database;";
Qry->Open();

Employees->ParamByName("ID")->AsInteger =
Qry->Fields->Fields[0]->AsInteger;

// Since the param in the INSERT statement of the TIBDataSet is named
":ID"...

Qry->Close();

delete Qry;
}

....and it isn't working. I recieve a "Field "ID" not found" exception.

I'm assuming I have misunderstood something here.

Thanks,
Terry

"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote in message
news:45266449$1 (AT) newsgroups (DOT) borland.com...

Wayne Niddery [TeamB] · Guest

Terry wrote:

Jeff Overcash (TeamB) · Guest

user wrote:

Terry · Guest

"Jeff Overcash (TeamB)" <jeffovercash (AT) mindspring (DOT) com> wrote: