CODEWITHSUNDEEP
sqldelphi
Caruso
Caruso

Reputation: 129

incorrect integer value '' for column cuit at row 1

I have a webservice, with the next code in the implementation:

procedure Tferreteria.inserpersona(persona:Tpersona);
begin
  //insertar persona
  dm:=Tconsultas.Create(nil);

   with dm.SQLQuery1 do
   begin
     Close;
     SQL.Add('insert into persona (nombreyape, domicilio)values (:nombreyape,:domicilio);');
     ParamByName('nombreyape').AsString:=persona.nombreyape;
     ParamByName('domicilio').AsString:=persona.domicilio;
     ExecSQL();
   end;
 end;

 procedure Tferreteria.insercliente(cliente:Tcliente);
 begin
   //insertar cliente

   with dm.SQLQuery1 do
   begin
     SQL.Clear;
     SQL.Add ('insert into clientes(idcliente,idpersona,cuit,cuil) values (null,(select     last_insert_id()),:cuit,:cuil);');

     ParamByName('cuit').AsInteger:=cliente.cuit;
     ParamByName('cuil').AsInteger:=cliente.cuil;
     ExecSQL();
   end;
 end;

As you see are 2 procedures which insert into tables personas and clientes. Clientes have a column idpersona which references personas

In the button click event I have this code

with Persona do
begin
  nombreyape:=Form2.Edit1.Text;
  domicilio:=Form2.Edit8.Text;
  idlocalidad:=StrToInt(Form2.Edit3.Text);
end;

with Cliente do
begin
  cuit:=StrToInt(Edit6.Text);
  cuil:=StrToInt(Edit7.Text);
end;
//llamar al servicio
GetIferreteria.inserpersona(Persona);
GetIferreteria.insercliente(Cliente);

and I obtain this error

incorrect integer value 'aaa' for column cuit at row 1,

when aaa is written in edit1, which is not assigned to clientes.cuit,

Now I have another problem:

I have to insert idpersona in clientes, but I have to insert idpersona in telper, telper is a table which makes a relation among telephones and people who have these telephones.

If I do the same with other query, the last insert id would be an id from clientes (idcliente), not idpersona.

What can I do to make the idpersona stand for each moment I need to use it, in each table I mean?

Upvotes: 0

Views: 613

Answers (1)

Remy Lebeau
Remy Lebeau

Reputation: 596497

I strongly recommend you re-write this code. Especially since you are using last_insert_id() (which you do not need to select to retreive), so you should execute the two statements together, preferably in a transaction (or better, in a stored procedure).

Try something more like this:

procedure Tferreteria.inserPersonaUnCliente(persona: Tpersona; cliente: Tcliente);
var
  Query: TSQLQuery;
  Conn: TSQLConnection;
  SQL: TStrings;
  Trans: TDBXTransaction;
begin
  dm := Tconsultas.Create(nil);
  Query := dm.SQLQuery1;
  Conn := Query.SQLConnection;
  SQL := Query.SQL;

  //insertar persona un cliente

  Query.Close;

  Trans := Conn.BeginTransaction;
  try
    SQL.BeginUpdate;
    try
      SQL.Clear;
      SQL.Add('insert into persona (nombreyape, domicilio) values (:nombreyape, :domicilio);';
      SQL.Add('insert into clientes(idcliente, idpersona, cuit, cuil) values (null, last_insert_id(), :cuit, :cuil);');
    finally
      SQL.EndUpdate;
    end;
    Query.ParamByName('nombreyape').AsString := persona.nombreyape;
    Query.ParamByName('domicilio').AsString := persona.domicilio;
    Query.ParamByName('cuit').AsInteger := cliente.cuit;
    Query.ParamByName('cuil').AsInteger := cliente.cuil;
    Query.ExecSQL;
    Conn.CommitFreeAndNil(Trans);
  except
    Conn.RollbackFreeAndNil(Trans);
    raise;
  end;
end;


with Persona do
begin
  nombreyape := Form2.Edit1.Text;
  domicilio := Form2.Edit8.Text;
  idlocalidad := StrToInt(Form2.Edit3.Text);
end;

with Cliente do
begin
  cuit := StrToInt(Edit6.Text);
  cuil := StrToInt(Edit7.Text);
end;

//llamar al servicio
GetIferreteria.inserPersonaUnCliente(Persona, Cliente);

Upvotes: 1

Related Questions