[SOLVED] Update query with parameter
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2016-09-19 14:53

I have been busy with building the 2 update-queries.
The first one is oke, but the second one gives an error when i am
trying to put in the parameter at the end.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm_Invoer_Lid.UpdateLid;var mySql: TSQLQuery;    cSQL, d:
string;begin  //Sla persoonsgegevens op in tbl_Personen  mySQl :=
TSQLQuery.Create(nil);  mySQl.DataBase := DB_Info.Connect_DB;  try   
cSQL := 'UPDATE tbl_Personen SET ' +            'Lid_Voornaam = "' +
Edit_Voornaam.Text + '", ' +            'Lid_Tussenvoegsel = "' +
Edit_Tussen.Text + '", ' +            'Lid_Achternaam = "' +
Edit_Achternaam.Text + '", ' +            'Lid_Initialen = "' +
Edit_Initialen.Text + '", ' +            'Lid_Adres = "' +
Edit_Adres.Text + '", ' +            'Lid_Postcode = "' +
Edit_Postcode.Text + '", ' +            'Lid_Telefoon = "' +
Edit_Telefoon.Text + '", ' +            'Lid_Mobiel = "' +
Edit_Mobiel.Text + '", ' +            'Lid_Lid = -1 ' +     // Het lid
word bewerkt dus deze is aanwezig.            'WHERE Lid_ID = ' +
Edit_ID.Text;    mySql.Active := False;    mySql.SQL.Text := 'SELECT *
FROM tbl_Personen';    mySql.UpdateSQL.Text := cSQL;    mySql.Active
:= True;    mySql.Edit;    mySql.Post;    mySql.ApplyUpdates;     
cSQL := 'UPDATE tbl_Personen_Leden SET ' +            'Lid_Geb_Plaats
= "' + Edit_Geboorteplaats.Text + '", ' +            'Lid_Email = "' +
Edit_Email.Text + '", ' +//            'Lid_Geslacht = "' + Edit_.Text
+ '", ' +            'Lid_NIJB_nr = "' + Edit_NIJBnr.Text + '", ' +  
        'Lid_Opmerkingen = "' + Edit_Opmerkingen.Text + '", ' +      
    'Lid_Banknr = "' + Edit_Rekening.Text + '", ' +           
'Lid_tnv = "' + Edit_TNV.Text + '", ' +            'Lid_BSN = "' +
Edit_BSN.Text + '", ';    if DBCB_Identificatie.Checked = True then
cSQL := cSQL + 'Lid_Identificatie = -1, '                            
           else cSQL := cSQL + 'Lid_Identificatie = 0, ';    if
DBCB_Inschrijfformulier.Checked = True then cSQL := cSQL +
'Lid_Inschrijfformulier = -1, '                                      
      else cSQL := cSQL + 'Lid_Inschrijfformulier = 0, ';    if
DBCB_Geroyeerd.Checked = True then cSQL := cSQL + 'Lid_Geroyeerd = -1,
'                                     else cSQL := cSQL +
'Lid_Geroyeerd = 0, ';    cSQL := cSQL + 'Lid_Geb_datum =
:Lid_Geb_datum ';    cSQL := cSQL + 'WHERE Lid_ID = ' + Edit_ID.Text; 
  mySql.Active := False;    mySql.SQL.Text := 'SELECT * FROM
tbl_Personen_Leden';    mySql.UpdateSQL.Text := cSQL;  // -=> Here it
goes wrong    mySql.ParamByName('Lid_Geb_datum').AsDate :=
EncodeToJulian(DBDateTimePicker_Geboortedatum.Field.AsDateTime);     
mySql.Active := True;    mySql.Edit;    mySql.Post;   
mySql.ApplyUpdates;    if DB_Info.Trans_DB.Active then     
DB_Info.Trans_DB.CommitRetaining;  finally    MySql.Free;  end;end;  
 // UpdateLid 

This is the function of EncodeToJulian.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
EncodeToJulian (MyDate: TDateTime): TDate;var d1, d2, d3: integer;   
d: string;begin  d := FormatDateTime('DD-MM-YYYY', MyDate); 
//ShowMessage (d);  d1 := StrToInt (Copy(d, 4, 2));  // maand  d2 :=
StrToInt (Copy(d, 1, 2));  // dag  d3 := StrToInt (Copy(d, 7, 4));  //
jaar  EncodeToJulian := EncodeDate(d3, d1, d2);end;     //
EncodeToJulian What am i doing wrong?

----------------------------------------------------------------------
rvk · 2016-09-19 15:06

Always say exactly WHAT error you are getting.
(or do you want us to guess  )

But why not use ExecSQL directly and put the UPDATE-sql directly in
SQL.Text?

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   cSQL :=
'UPDATE tbl_Personen SET ' +            'Lid_Voornaam = "' +
Edit_Voornaam.Text + '", ' +            'Lid_Tussenvoegsel = "' +
Edit_Tussen.Text + '", ' +            'Lid_Achternaam = "' +
Edit_Achternaam.Text + '", ' +            'Lid_Initialen = "' +
Edit_Initialen.Text + '", ' +            'Lid_Adres = "' +
Edit_Adres.Text + '", ' +            'Lid_Postcode = "' +
Edit_Postcode.Text + '", ' +            'Lid_Telefoon = "' +
Edit_Telefoon.Text + '", ' +            'Lid_Mobiel = "' +
Edit_Mobiel.Text + '", ' +            'Lid_Lid = -1 ' +     // Het lid
word bewerkt dus deze is aanwezig.            'WHERE Lid_ID = ' +
Edit_ID.Text;    mySql.SQL.Text := cSQL;    mySql.ExecSQL;    //
mySql.ApplyUpdates; // not needed ??    if DB_Info.Trans_DB.Active
then      DB_Info.Trans_DB.CommitRetaining;(And you might want to wrap
all this in a try/finally, releasing mySql in the finally section.)

(you will still have the same error but it's better to work with
execsql in this case)

----------------------------------------------------------------------
madref · 2016-09-19 15:12

But t is in the second query.
It says: Parameter Lid_Geb_datum not found

----------------------------------------------------------------------
rvk · 2016-09-19 15:18

Quote from: madref on September 19, 2016, 05:12:06 pmIt says:
Parameter Lid_Geb_datum not found
That's because the mySql.ParamByName() are validated against the
SQL.Text (which you filled with a SELECT stetement)
When you do mySql.Open() it also sends the parameters with it. And the
server can't find that parameter in you statement
SELECT * FROM tbl_Personen_Leden

Only when you do UPDATE or DELETE you wan't that parameter but then
it's too late.

You could do this
SELECT * FROM tbl_Personen_Leden where Lid_Geb_datum = :Lid_Geb_datum
and then the Open will run.

But like I said in my previous post... you don't need the .Open.
So it's best to just only execute the UPDATE query (and in that case
the mySql.ParamByName() have direct effect on the update statement).

(O, sorry, I see now that you already had the try/finally   )

This would be the code (didn't test it but you get the point...)
No need for the SELECT and .open statements...

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm_Invoer_Lid.UpdateLid;var mySql: TSQLQuery;    cSQL, d:
string;begin  //Sla persoonsgegevens op in tbl_Personen  mySQl :=
TSQLQuery.Create(nil);  mySQl.DataBase := DB_Info.Connect_DB;  try   
cSQL := 'UPDATE tbl_Personen SET ' +            'Lid_Voornaam = "' +
Edit_Voornaam.Text + '", ' +            'Lid_Tussenvoegsel = "' +
Edit_Tussen.Text + '", ' +            'Lid_Achternaam = "' +
Edit_Achternaam.Text + '", ' +            'Lid_Initialen = "' +
Edit_Initialen.Text + '", ' +            'Lid_Adres = "' +
Edit_Adres.Text + '", ' +            'Lid_Postcode = "' +
Edit_Postcode.Text + '", ' +            'Lid_Telefoon = "' +
Edit_Telefoon.Text + '", ' +            'Lid_Mobiel = "' +
Edit_Mobiel.Text + '", ' +            'Lid_Lid = -1 ' +     // Het lid
word bewerkt dus deze is aanwezig.            'WHERE Lid_ID = ' +
Edit_ID.Text;    mySql.SQL.Text := cSQL;    mySql.ExecSQL;    //
mySql.ApplyUpdates; not needed ??     cSQL := 'UPDATE
tbl_Personen_Leden SET ' +            'Lid_Geb_Plaats = "' +
Edit_Geboorteplaats.Text + '", ' +            'Lid_Email = "' +
Edit_Email.Text + '", ' +//            'Lid_Geslacht = "' + Edit_.Text
+ '", ' +            'Lid_NIJB_nr = "' + Edit_NIJBnr.Text + '", ' +  
        'Lid_Opmerkingen = "' + Edit_Opmerkingen.Text + '", ' +      
    'Lid_Banknr = "' + Edit_Rekening.Text + '", ' +           
'Lid_tnv = "' + Edit_TNV.Text + '", ' +            'Lid_BSN = "' +
Edit_BSN.Text + '", ';    if DBCB_Identificatie.Checked = True then
cSQL := cSQL + 'Lid_Identificatie = -1, '                            
           else cSQL := cSQL + 'Lid_Identificatie = 0, ';    if
DBCB_Inschrijfformulier.Checked = True then cSQL := cSQL +
'Lid_Inschrijfformulier = -1, '                                      
      else cSQL := cSQL + 'Lid_Inschrijfformulier = 0, ';    if
DBCB_Geroyeerd.Checked = True then cSQL := cSQL + 'Lid_Geroyeerd = -1,
'                                     else cSQL := cSQL +
'Lid_Geroyeerd = 0, ';    cSQL := cSQL + 'Lid_Geb_datum =
:Lid_Geb_datum ';    cSQL := cSQL + 'WHERE Lid_ID = ' + Edit_ID.Text; 
  mySql.SQL.Text := cSQL;    
mySql.ParamByName('Lid_Geb_datum').AsDate :=
EncodeToJulian(DBDateTimePicker_Geboortedatum.Field.AsDateTime);    
mySql.ExecSQL;    // mySql.ApplyUpdates; not needed ??     if
DB_Info.Trans_DB.Active then      DB_Info.Trans_DB.CommitRetaining; 
finally    MySql.Free;  end;end;     // UpdateLid
(as stated I'm not sure you need the applyupdates. Just leave them out
and if it doesn't work put them back in)