Hello

While trying to identify previous problems, I have found a new
problem with TIBOQuery when inserting a row with a updateSQL, I get a
error message : Cannot insert a new row

I havn't not yet manage to solve by code the problem

here is a test program :

dfm:object FTestInsert1: TFTestInsert1
Left = 291
Top = 106
Width = 485
Height = 432
Caption = 'FTestInsert1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnShow = FormShow
PixelsPerInch = 96
TextHeight = 13
object DbGrdSecteurs: TDBGrid
Left = 17
Top = 59
Width = 440
Height = 262
Hint =
'Sélectionnez un ou plusieurs secteurs puis click droit pour
modi' +
'fier'
Anchors = [akLeft, akTop, akRight, akBottom]
DataSource = DSQSect
Options = [dgEditing, dgTitles, dgIndicator, dgColLines,
dgRowLines, dgTabs, dgAlwaysShowSelection, dgConfirmDelete,
dgCancelOnExit, dgMultiSelect]
ParentShowHint = False
ReadOnly = True
ShowHint = True
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
Columns = <
item
Alignment = taCenter
Expanded = False
FieldName = 'NUM'
Title.Alignment = taCenter
Title.Caption = 'Secteur'
Visible = True
end
item
Expanded = False
FieldName = 'LIB'
Title.Alignment = taCenter
Title.Caption = 'Libellé'
Visible = True
end
item
Expanded = False
FieldName = 'NBPROSP'
Title.Alignment = taCenter
Title.Caption = 'Prospects'
Visible = True
end>
end
object DbNavSecteurs: TDBNavigator
Left = 20
Top = 322
Width = 430
Height = 18
DataSource = DSQSect
Anchors = [akLeft, akRight, akBottom]
Hints.Strings = (
'Premier secteur'
'Secteur précédent'
'Secteur suivant'
'Dernier secteur'
'Insérer secteur'
'Supprimer secteur sans lien'
'Modifier libellé d'#39'un secteur'
'Valider la modification sur le secteur'
'Annuler la modification'
'Rafraîchir données')
ParentShowHint = False
ShowHint = True
TabOrder = 1
OnClick = DbNavSecteursClick
end
object btnBDE: TButton
Left = 56
Top = 16
Width = 97
Height = 25
Caption = 'BDE'
TabOrder = 2
OnClick = btnBDEClick
end
object btnIBO: TButton
Left = 304
Top = 16
Width = 97
Height = 25
Caption = 'IBO'
TabOrder = 3
OnClick = btnIBOClick
end
object GPACBaseIBO: TIBODatabase
AliasName = 'GPAC_T'
LoginPrompt = True
DatabaseName = 'GPACAliasIBO'
OnLogin = GPACBaseIBOLogin
SessionName = 'Default'
Left = 118
Top = 148
end
object QSectIBO: TIBOQuery
Params = <>
CachedUpdates = True
DatabaseName = 'GPACAliasIBO'
IB_Connection = GPACBaseIBO
RecordCountAccurate = True
UpdateObject = UpdSQLSectIBO
RequestLive = True
FieldOptions = []
Left = 168
Top = 144
object QSectIBONUM: TIntegerField
FieldName = 'NUM'
end
object QSectIBOLIB: TStringField
FieldName = 'LIB'
Size = 30
end
object QSectIBONBPROSP: TIntegerField
FieldName = 'NBPROSP'
end
end
object DSQSect: TDataSource
Left = 208
Top = 176
end
object UpdSQLSectIBO: TIBOUpdateSQL
Left = 248
Top = 144
end
object GPACBaseBDE: TDatabase
AliasName = 'GPAC_T'
DatabaseName = 'GPACAliasBDE'
SessionName = 'Default'
OnLogin = GPACBaseBDELogin
Left = 120
Top = 200
end
object QSectBDE: TQuery
CachedUpdates = True
DatabaseName = 'GPACAliasBDE'
UpdateObject = UpdSQLSectBDE
Left = 168
Top = 200
object QSectBDENUM: TIntegerField
FieldName = 'NUM'
end
object QSectBDELIB: TStringField
FieldName = 'LIB'
Size = 30
end
object QSectBDENBPROSP: TIntegerField
FieldName = 'NBPROSP'
end
end
object UpdSQLSectBDE: TUpdateSQL
Left = 256
Top = 200
end
end


pas:
unit TestInsert1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
Dialogs,
IBODataset, Db, IB_Components, Grids, DBGrids, ExtCtrls, DBCtrls,
StdCtrls, DBTables;

type
TFTestInsert1 = class(TForm)
DbGrdSecteurs: TDBGrid;
GPACBaseIBO: TIBODatabase;
QSectIBO: TIBOQuery;
QSectIBONUM: TIntegerField;
QSectIBOLIB: TStringField;
QSectIBONBPROSP: TIntegerField;
DSQSect: TDataSource;
UpdSQLSectIBO: TIBOUpdateSQL;
DbNavSecteurs: TDBNavigator;
btnBDE: TButton;
btnIBO: TButton;
GPACBaseBDE: TDatabase;
QSectBDE: TQuery;
UpdSQLSectBDE: TUpdateSQL;
QSectBDENUM: TIntegerField;
QSectBDELIB: TStringField;
QSectBDENBPROSP: TIntegerField;
procedure GPACBaseIBOLogin(Sender: TIB_Connection;
var AbortLogin: Boolean);
procedure FormShow(Sender: TObject);
procedure DbNavSecteursClick(Sender: TObject; Button:
TNavigateBtn);
procedure btnBDEClick(Sender: TObject);
procedure btnIBOClick(Sender: TObject);
procedure GPACBaseBDELogin(Database: TDatabase; LoginParams:
TStrings);
private
{ Déclarations privées }
public
{ Déclarations publiques }
end;

var
FTestInsert1: TFTestInsert1;

implementation

{$R *.DFM}

procedure TFTestInsert1.GPACBaseIBOLogin(Sender: TIB_Connection;
var AbortLogin: Boolean);
begin
GPACBaseIBO.Params.Values['PASSWORD'] := 's';
GPACBaseIBO.Params.Values['USER NAME'] := 'SYSDBA';
end;

procedure TFTestInsert1.FormShow(Sender: TObject);
begin

GPACBaseIBO.Connected := False;
GPACBaseBDE.Connected := False;
end;

procedure TFTestInsert1.DbNavSecteursClick(Sender: TObject;
Button: TNavigateBtn);
begin
case Button of
nbDelete:
{ temporairement pas de gestion des modifs
if EtatModification = emLecture then
SetEtatModification(emDebutModif)};
nbEdit, nbInsert:
DbGrdSecteurs.ReadOnly := False;
end;
end;

procedure TFTestInsert1.btnBDEClick(Sender: TObject);
begin
btnBDE.Enabled := False;
QSectIBO.Close;
GPACBaseIBO.Connected := False;
GPACBaseBDE.Connected := False;

try
GPACBaseBDE.Connected := False;
GPACBaseBDE.Connected := True;
except
MessageDlg('Erreur ouverture base IBO', mtWarning, [mbOK], 0);
raise;
end;

DSQSect.DataSet := QSectBDE;

with QSectBDE do begin
Close;
SQL.Clear;
SQL.Add('select S.NUM, S.LIB, count(P.SECT) as nbPROSP');
SQL.Add('from T_SECT S');
SQL.Add('left outer join T_PROSP P on P.SECT = S.NUM');
SQL.Add('group by S.NUM, S.LIB');

RequestLive := True;
UpdateObject := UpdSQLSectBDE;
end;

// Ordres de mises à jour des secteurs
with UpdSQLSectBDE.ModifySQL do begin
Clear;
Add('update T_SECT set LIB = :LIB where NUM = :NUM');
end;
with UpdSQLSectBDE.InsertSQL do begin
Clear;
Add('insert into T_SECT (NUM, LIB) values (:NUM, :LIB)');
end;
with UpdSQLSectBDE.DeleteSQL do begin
Clear;
Add('delete from T_SECT where NUM = :OLD_NUM');
end;

QSectBDE.Prepare;
QSectBDE.Open;

btnIBO.Enabled := True;
end;

procedure TFTestInsert1.btnIBOClick(Sender: TObject);
begin
btnIBO.Enabled := False;
QSectBDE.Close;
GPACBaseBDE.Connected := False;
GPACBaseIBO.Connected := False;


try
GPACBaseIBO.Connected := True;
except
MessageDlg('Erreur ouverture base IBO', mtWarning, [mbOK], 0);
raise;
end;

DSQSect.DataSet := QSectIBO;

with QSectIBO do begin
Close;
IB_Connection := GPACBaseIBO;
SQL.Clear;
SQL.Add('select S.NUM, S.LIB, count(P.SECT) as nbPROSP');
SQL.Add('from T_SECT S');
SQL.Add('left outer join T_PROSP P on P.SECT = S.NUM');
SQL.Add('group by S.NUM, S.LIB');

RequestLive := True;
UpdateObject := UpdSQLSectIBO;
end;

// Ordres de mises à jour des secteurs
with UpdSQLSectIBO.ModifySQL do begin
Clear;
Add('update T_SECT set LIB = :LIB where NUM = :NUM');
end;
with UpdSQLSectIBO.InsertSQL do begin
Clear;
Add('insert into T_SECT (NUM, LIB) values (:NUM, :LIB)');
end;
with UpdSQLSectIBO.DeleteSQL do begin
Clear;
Add('delete from T_SECT where NUM = :OLD_NUM');
end;

QSectIBO.Prepare;
QSectIBO.Open;

btnBDE.Enabled := True;
end;

procedure TFTestInsert1.GPACBaseBDELogin(Database: TDatabase;
LoginParams: TStrings);
begin
LoginParams.Add('PASSWORD=s');
LoginParams.Add('USER NAME=SYSDBA');
end;

end.

Sorry for the length of this message
Thanks
Henry Franquet