TQuery locate problem

marioway
marioway used Ask the Experts™
on
Hi, I have another problem

I run IBQuery6 -> select * from Bedrijsvorm

then

procedure TAdministratie.ComboBox1Exit(Sender: TObject);
var A: string;
begin
with Datamodule1 do begin
 if Trim(ComboBox1.Text) = '' then begin
  ShowMessage('Veld bedrijfsvorm MOET worden ingevuld !');
  ComboBox1.SetFocus;
  Exit;
 end
 else
  begin
   A:= Trim(ComboBox1.Text);
   if not IBQuery2.Locate('BEDRIJFSVORMEN', A, [])then begin
      IBQuery5.Close;
      IBQuery5.SQL.Clear;
      IBQuery5.SQL.Add('Insert INTO BEDRIJFSVORM (BEDRIJFSVORMEN) Values(:A)');
       IBQuery5.Params[0].AsString:= A ;
      IBQuery5.ExecSQL;
   end
    else
     begin
      Exit;
     end;
  end;
end;
end;

When I run the program, and exit the combobox, a locate is performed inside IBQuery6

When it does not find the value, run IBQuery5 to insert the value into bedrijfsvorm, else
Exit . The result is that I get an error (primary key violation), instead of exiting without trying to insert...

problem ?

thx
Mario
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ferruccio AccalaiSenior developer, analyst and customer assistance

Commented:
Are you sure about the case of A?
I'd use  
if not IBQuery2.Locate('BEDRIJFSVORMEN', A, [locaseinsensitive])then begin
It's a bit difficult find a right answer without knowing the structure of the table when you're trying to insert that value
Oracle dba
Top Expert 2009
Commented:
use a query instead of a locate
somebody else may have inserted the data between the time you have loaded the query and entered the name in the combobox

you could also use a insert select as a precaution
procedure TAdministratie.ComboBox1Exit(Sender: TObject);
var A: string;
begin
  A := Trim(ComboBox1.Text);
  if A = '' then 
  begin
    ShowMessage('Veld bedrijfsvorm MOET worden ingevuld !');
    ComboBox1.SetFocus;
  end 
    else 
  with Datamodule1 do 
  begin
    IBQuery2.SQL.Text := 'SELECT 1 from BEDRIJFSVORM WHERE Upper(BEDRIJFSVORMEN) = Upper(:A)';
    IBQuery2.ParamByName('A').AsString:= A ;
    IbQuery2.Open;
    if not IBQuery2.IsEmpty then 
      ShowMessage('Bedrijfsvorm ' + A + ' bestaat al !')
    else 
    begin
      IBQuery5.Close;
      IBQuery5.SQL.Clear;
      IBQuery5.SQL.Text := 
        'Insert INTO BEDRIJFSVORM (BEDRIJFSVORMEN) '+
        'SELECT :A As BEDRIJFSVORMEN FROM rdb$database '+
        'WHERE 0 = ( SELECT COUNT(*) X FROM BEDRIJFSVORM '+
        '  WHERE Upper(BEDRIJFSVORMEN) = Upper(:A)) ';
      IBQuery5.ParamByName('A').AsString:= A ;
      IBQuery5.ExecSQL;
    end;
  end;
end;

Open in new window

Author

Commented:
Geert, thx a lot; what would I do without you guys ... I'm creating a client/server version of my stand-alone program from scratch. Not so easy, but I'm learning fast. I'm gone a have a look at that rdb$databse thing ; seems very important ...

Mario

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial