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 ?
DelphiPascalDatabases

Avatar of undefined
Last Comment
marioway

8/22/2022 - Mon
Geert G

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

IbQuery5.ParamByName('A').DataType := ftString;
Geert G

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
Geert G

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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
marioway

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

Mario
Geert G

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 '+
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marioway

ASKER
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
Geert G

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
marioway

ASKER
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