Link to home
Start Free TrialLog in
Avatar of marioway
marioway

asked on

Exiting edit and insert into firebird table gives Dynamic SQL Error

Hi, another problem emerged; when I exit an edit(box), and try to insert a value, other then the one allready in the table, it gives me a dynamic SQL Error : Data Type Unknown
Table : 1 field type Char; code:

procedure TAdministratie.ComboBox2Exit(Sender: TObject);
var A: string;
begin
  A := Trim(ComboBox2.Text);
  if A = '' then
  begin
  end
    else
  with Datamodule1 do
  begin
    IBQuery10.Close;
    IBQuery10.SQL.Clear;
    IBQuery10.SQL.Text := 'SELECT 1 from STRAAT WHERE Upper(STRATEN) = Upper(:A)';
    IBQuery10.ParamByName('A').AsString:= A ;
    IbQuery10.Open;
    if not IBQuery10.IsEmpty then
      Exit
    else
    begin
      IBQuery5.Close;
      IBQuery5.SQL.Clear;
      IBQuery5.SQL.Text :=
        'Insert INTO STRAAT (STRATEN) '+
        'SELECT :A As STRATEN FROM rdb$database '+
        'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
        '  WHERE Upper(STRATEN) = Upper(:A)) ';
      IBQuery5.ParamByName('A').AsString:= A ;
      IBQuery5.ExecSQL;
    end;
  end;
end;

Problem ?
Avatar of Geert G
Geert G
Flag of Belgium image

the database doesn't know what type is required
and it looks like firebird doesn't convert automatically

IbQuery5.ParamByName('A').DataType := ftString;
it is pointless in writing so much useless code
strip it down ... unless somebody is paying you per line ???

procedure TAdministratie.ComboBox2Exit(Sender: TObject);
var A: string;
begin
  A := Trim(ComboBox2.Text);
  if A <> '' then
  with Datamodule1 do
  begin
    IBQuery5.Close;
    IBQuery5.SQL.Text :=
      'Insert INTO STRAAT (STRATEN) '+
      'SELECT :A As STRATEN FROM rdb$database '+
      'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
      '  WHERE Upper(STRATEN) = Upper(:A)) ';
    IBQuery5.ParamByName('A').AsString:= A ;
    IBQuery5.ParamByName('A').DataType := ftString ;
    IBQuery5.ExecSQL;
  end;
end;

Open in new window


1: if you set Sql.Text then you can delete the Sql.Clear line preceding
2: there is no need to check with IbQuery10 for a duplicate
the insert statement with the contained select already does this
3: there is no point in writing a empty "begin" "end" clause
alternately, if you want to know if the insert added a record:

procedure TAdministratie.ComboBox2Exit(Sender: TObject);
var A: string;
begin
  A := Trim(ComboBox2.Text);
  if A <> '' then
  with Datamodule1 do
  begin
    IBQuery5.Close;
    IBQuery5.SQL.Text :=
      'Insert INTO STRAAT (STRATEN) '+
      'SELECT :A As STRATEN FROM rdb$database '+
      'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
      '  WHERE Upper(STRATEN) = Upper(:A)) ';
    IBQuery5.ParamByName('A').AsString:= A ;
    IBQuery5.ParamByName('A').DataType := ftString ;
    IBQuery5.ExecSQL;
    // Show how many records were inserted
    ShowMessage(Format('%d records inserted.', [IbQuery5.RowsAffected]));
  end;
end;

Open in new window

Avatar of marioway
marioway

ASKER

Hi, Geert , I don't get payd .....  I'm going to follow your advise ! Now I'm going to try this thing out

Mario
Geert, still getting Dynamic SQL Error -804 Data Type Unkown

Mario
then use a cast or convert
http://www.firebirdsql.org/refdocs/langrefupd20-cast.html

 IBQuery5.SQL.Text :=
      'Insert INTO STRAAT (STRATEN) '+
      'SELECT CAST(:A As VARCHAR) FROM rdb$database '+
      'WHERE 0 = ( SELECT COUNT(*) X FROM STRAAT '+
      '  WHERE Upper(STRATEN) = Upper(Cast (:A as varchar))) ';
    IBQuery5.ParamByName('A').AsString:= A ;

Open in new window


it's possible you need to specify the length too
      'SELECT CAST(:A As VARCHAR(50)) FROM rdb$database '+
Geert, now I get another error: Dynamic SQL Error = -104 Token Unknown - line 1, Char 68 ).

also if I use VARCHAR(50)

Mario
ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Geert, I have done some testing, and finally got the data in the tables; I forgot to grant everything to SYSDBA for the tables.

thx
Mario