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.ComboBox2Ex it(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 ?
Table : 1 field type Char; code:
procedure TAdministratie.ComboBox2Ex
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')
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').
IBQuery5.ExecSQL;
end;
end;
end;
Problem ?
it is pointless in writing so much useless code
strip it down ... unless somebody is paying you per line ???
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
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;
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;
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
Mario
ASKER
Geert, still getting Dynamic SQL Error -804 Data Type Unkown
Mario
Mario
then use a cast or convert
http://www.firebirdsql.org/refdocs/langrefupd20-cast.html
it's possible you need to specify the length too
'SELECT CAST(:A As VARCHAR(50)) FROM rdb$database '+
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 ;
it's possible you need to specify the length too
'SELECT CAST(:A As VARCHAR(50)) FROM rdb$database '+
ASKER
Geert, now I get another error: Dynamic SQL Error = -104 Token Unknown - line 1, Char 68 ).
also if I use VARCHAR(50)
Mario
also if I use VARCHAR(50)
Mario
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
thx
Mario
and it looks like firebird doesn't convert automatically
IbQuery5.ParamByName('A').