SOLVED where is the problem in my code,sql statements or the DB connection

[yanes19]

Hi all of Lazarus community ;
first of all , I'm a Databases newbie my program uses a FireBird DB
the SELECT Statements works fine , But when I want to insert some data it raises the exception :
"Operation cannot be performed on an inactive dataset "
here is a part of my code where the exception raises :
 

Code: [Select]

procedure TForm_main.BitBtn_save_ficheClick(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.Insert;
  SQLQuery1.SQL.Text := 'INSERT INTO patients (ID_PATIENT ,TITRE_PATIENT ,NOM_PATIENT,PRENOM_PATIENT ,'+
       'DATE_NAISSANCE ,ETAT_CIVIL,PROFESSION,TELEPHONES,ADRESSE,NUM_SEC_SOCIALE,REFERE_PAR,' +
       'MEDECIN_TRAITANT, VISITES_ANTERIEURES, DATE_VISITE, M_CHRONIQUES, TRAITEMENTS_ANTERIEURS,' +
       'ANALYSES_MEDICALES, HOSPITALISATIONS, DIAGNOSTIC, ORDONNANCE, AUTRE_NOTES, TAUX_PAYE) values '  +
       '(:ID_PATIENT ,:TITRE_PATIENT ,:NOM_PATIENT,:PRENOM_PATIENT ,:DATE_NAISSANCE,:ETAT_CIVIL,:PROFESSION, ' +
       ' :TELEPHONES,:ADRESSE , :NUM_SEC_SOCIALE, :REFERE_PAR, :MEDECIN_TRAITANT,:VISITES_ANTERIEURES,'+
       ' :DATE_VISITE, :M_CHRONIQUES, :TRAITEMENTS_ANTERIEURS,:ANALYSES_MEDICALES, :HOSPITALISATIONS, ' +
       ' :DIAGNOSTIC, :ORDONNANCE, :AUTRE_NOTES, :TAUX_PAYE );';

  sql_query_Params_assign ;
   
  IBConnection1.Connected := True;
  SQLTransaction1.Active := True;
  SQLQuery1.ExecSQL;
  Savechanges ;

end;
procedure TForm_main.sql_query_Params_assign ();
begin
   SQLQuery1.Params.ParamByName('ID_PATIENT').AsString := Edit_ID_PATIENT.Text;
   SQLQuery1.Params.ParamByName('TITRE_PATIENT').AsString := Combo_TITRE_PATIENT.Text;
   SQLQuery1.Params.ParamByName('NOM_PATIENT').AsString := Edit_NOM_PATIENT.Text;
   SQLQuery1.Params.ParamByName('PRENOM_PATIENT').AsString := Edit_PRENOM_PATIENT.Text;
   SQLQuery1.Params.ParamByName('DATE_NAISSANCE').AsDate := DateEdit_BIRTH.Date;
   SQLQuery1.Params.ParamByName('ETAT_CIVIL').AsString := Combo_ETAT_CIVIL.Text;
   SQLQuery1.Params.ParamByName('PROFESSION').AsString := Edit_PROFESSION.Text;
   SQLQuery1.Params.ParamByName('TELEPHONES').AsString := Edit_TELEPHONES.Text;
   SQLQuery1.Params.ParamByName('ADRESSE').AsString := Edit_ADRESS.Text;
   SQLQuery1.Params.ParamByName('NUM_SEC_SOCIALE').AsString := Edit_NUM_SEC_SOCIALE.Text;
   SQLQuery1.Params.ParamByName('ID_PATIENT').AsDate := DateEdit_DATE_VISITE.Date;
   SQLQuery1.Params.ParamByName('REFERE_PAR').AsString := Edit_REFERE_PAR.Text;
   SQLQuery1.Params.ParamByName('MEDECIN_TRAITANT').AsString := Edit_MEDECIN_TRAITANT.Text;
   SQLQuery1.Params.ParamByName('VISITES_ANTERIEURES').AsString := Edit_VISITES_ANTERIEURES.Text;
   SQLQuery1.Params.ParamByName('M_CHRONIQUES').AsString := Edit_M_CHRONIQUES.Text;
   SQLQuery1.Params.ParamByName('TRAITEMENTS_ANTERIEURS').AsString := Edit_TRAITEMENTS_ANTERIEURS.Text;
   SQLQuery1.Params.ParamByName('ANALYSES_MEDICALES').AsString := Edit_ANALYSES_MEDICALES.Text;
   SQLQuery1.Params.ParamByName('HOSPITALISATIONS').AsString := Edit_HOSPITALISATIONS.Text;
   SQLQuery1.Params.ParamByName('DIAGNOSTIC').AsString := Edit_DIAGNOSTIC.Text;
   SQLQuery1.Params.ParamByName('ORDONNANCE').AsString := Edit_ORDONNANCE.Text;
   SQLQuery1.Params.ParamByName('AUTRE_NOTES').AsString := Edit_AUTRE_NOTES.Text;
   SQLQuery1.Params.ParamByName('TAUX_PAYE').AsInteger := StrToInt(Edit_TAUX_PAYE.Text);

end;                                     

Is it possible that the error is caused by Bad use of parametrized query ? 
Please help me ! 

[taazz]

Since the sql is not select but insert you do not have to call the insert method at all. You simple set the params and then call the execute method. So simple remove the line SQLQuery1.Insert;

[exdatis]

See:
http://wiki.freepascal.org/SQLdb_Tutorial1
part of: http://wiki.freepascal.org/Working_With_TSQLQuery
"TSQLQuery is an object that can embody a dataset coming from a database (RDBMS that uses SQL, such as Firebird, MS SQL Server, Oracle...). Using a SELECT SQL statement in the TSQLQuery's SQL property, you can determine what data is retrieved from the database into the dataset. When the dataset is changed by the program (or user), the changes can be submitted back to the database.

A TSQLQuery can also be used to directly modify data: if you specify the desired INSERT, UPDATE, DELETE etc SQL statement in the SQL property and call the ExecSQL method of the TSQLQuery, the query object will send the SQL to the database without retrieving any results. "
Regards
p.s. Also there is possibility to execute direct(db-connection)
see: http://wiki.freepascal.org/SqlDBHowto#How_to_execute_direct_queries.2Fmake_a_table.3F

[yanes19]

Thank-you for your time ,
@ taazz : the line with the call to Query's Insert() method have no effect if removed or left as is 
but it's good to right a correct code

@exdatis : I read the 3 tuts "SQLdb_Tutorial 1 , 2,3 and the other tuts in the wiki but no chance ,
BUT FIRST , is my SQL statement is correct ?
my parameters assignment is it also correct ?
sorry for my bad english 

[Lacak2]

BUT FIRST , is my SQL statement is correct ?

At first look it seems be ok (";" at end of SQL statement is not required)

my parameters assignment is it also correct ?

yes seems ok

On which line your error pop-ups?

[yanes19]

Thank you  @ Lacak2 ,
The problem now is in the Savechanges() procedure  , 
the exception raises when executing line :

SQLQuery1.ApplyUpdates;

Code: [Select]

procedure TForm_main.Savechanges;
// Saves edits done by user,
begin
  try
    if SQLTransaction1.Active then
    begin
      SQLQuery1.ApplyUpdates;
      SQLTransaction1.Commit;
      //SQLTransaction1.Active now is false
    end;
  except
//..........
  end;
end;             

if I keep the line

SQLQuery1.Close;

It raises :"""Operation cannot be performed on an inactive dataset" ""
but when I comment it , it raises """Operation cannot be performed on an active dataset" ""
what happened ? 
 

[yanes19]

It's Ok now , it was solved simply by commenting
the line "SQLQuery1.ApplyUpdates;"
The insert statement works fine now ,
But anyone can tell me why '.ApplyUpdates' have to be removed ?

THANKYOU ALL   

[Lacak2]

Intention of ApplyUpdates is send local updates , which was done in local dataset to "database server".
Local updates = SQLQuery1.Insert, Append, Edit ... SQLQuery1.Post or SQLQuery1.Delete
ApplyUpdates then propagates these local updates to "databese server" ... either uses generated SQL statements (INSERT, UPDATE, DELETE) or uses provided in InsertSQL, UpdateSQL, DeleteSQL properties of TSQLQuery.