[Solved] Update error Parameter not Found
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2020-08-14 23:44

I am trying to do an update on my database with with following code
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     tbl1 :=
'tbl_Evaluatie_Referee';    cSQL := 'UPDATE ' + tbl1 + ' SET ' +     
     'EVO_Verstuurd = 1, ' +            'EVO_Verstuurd_Datum =
:Verstuurd ' +            'WHERE EVO_ID = ' + Save_ID.ToString;     
Form_Lint.TQ_Rapport_Master.Active := False;   
Form_Lint.TQ_Rapport_Master.DataBase := Form_Lint.Connect_RefereeDB; 
 Form_Lint.TQ_Rapport_Master.SQL.Text := 'SELECT * FROM ' + tbl1 +
';';    Form_Lint.TQ_Rapport_Master.UpdateSQL.Text := cSQL;   
Form_Lint.TQ_Rapport_Master.Active := True;   
Form_Lint.TQ_Rapport_Master.Active := False;   
Form_Lint.TQ_Rapport_Master.ParamByName('Verstuurd').AsDate :=
EncodeToJulian(Date());    Form_Lint.TQ_Rapport_Master.ApplyUpdates; 
 Form_Lint.TQ_Rapport_Master.Active := True; 

But I get an error as displayed in the attachment.


tbl_referee is defined as:
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     //
tbl_Evaluatie_Referee    cSQL := '';    cSQL := 'CREATE TABLE
tbl_Evaluatie_Referee (' +            'Evo_ID WORD NOT NULL PRIMARY
KEY ASC, ' +            'Evo_Scheids_ID WORD, ' +           
'Evo_Wed_ID WORD, ' +            'Evo_Systeem WORD, ' +           
'Evo_SoortWedstrijd WORD, ' +            'Evo_Supervisor WORD, ' +   
       'Evo_Goal_TeamA_Per1 WORD DEFAULT(0), ' +           
'Evo_Goal_TeamA_Per2 WORD DEFAULT(0), ' +           
'Evo_Goal_TeamA_Per3 WORD DEFAULT(0), ' +           
'Evo_Goal_TeamA_Per4 WORD DEFAULT(0), ' +           
'Evo_Goal_TeamB_Per1 WORD DEFAULT(0), ' +           
'Evo_Goal_TeamB_Per2 WORD DEFAULT(0), ' +           
'Evo_Goal_TeamB_Per3 WORD DEFAULT(0), ' +           
'Evo_Goal_TeamB_Per4 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamA_Per1 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamA_Per2 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamA_Per3 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamA_Per4 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamB_Per1 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamB_Per2 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamB_Per3 WORD DEFAULT(0), ' +           
'Evo_Straf_TeamB_Per4 WORD DEFAULT(0), ' +           
'Evo_Categorie_01 WORD DEFAULT(0), ' +            'Evo_Categorie_02
WORD DEFAULT(0), ' +            'Evo_Categorie_03 WORD DEFAULT(0), '
+            'Evo_Categorie_04 WORD DEFAULT(0), ' +           
'Evo_Categorie_05 WORD DEFAULT(0), ' +            'Evo_Categorie_06
WORD DEFAULT(0), ' +            'Evo_Categorie_07 WORD DEFAULT(0), '
+            'Evo_Categorie_08 WORD DEFAULT(0), ' +           
'Evo_Categorie_09 WORD DEFAULT(0), ' +            'Evo_Categorie_10
WORD DEFAULT(0), ' +            'Evo_Categorie_11 WORD DEFAULT(0), '
+            'Evo_Categorie_12 WORD DEFAULT(0), ' +           
'Evo_Categorie_13 WORD DEFAULT(0), ' +            'Evo_Categorie_14
WORD DEFAULT(0), ' +            'Evo_Categorie_15 WORD DEFAULT(0), '
+            'Evo_Categorie_16 WORD DEFAULT(0), ' +           
'Evo_Categorie_17 WORD DEFAULT(0), ' +            'Evo_SterkePunten
TEXT, ' +            'Evo_VerbeterPunten TEXT, ' +           
'Evo_Feedback TEXT, ' +            'Evo_MinorA WORD DEFAULT(0), ' +  
        'Evo_MinorB WORD DEFAULT(0), ' +            'Evo_MajorA WORD
DEFAULT(0), ' +            'Evo_MajorB WORD DEFAULT(0), ' +          
'Evo_MiscA WORD DEFAULT(0), ' +            'Evo_MiscB WORD DEFAULT(0),
' +            'Evo_GMPA WORD DEFAULT(0), ' +            'Evo_GMPB
WORD DEFAULT(0), ' +            'Evo_MPA WORD DEFAULT(0), ' +        
  'Evo_MPB WORD DEFAULT(0), ' +            'Evo_PSA WORD DEFAULT(0), '
+            'Evo_PSB WORD DEFAULT(0), ' +            'EVO_Verstuurd
BOOLEAN, ' +            'EVO_Verstuurd_Datum DATE ' +            ')';

----------------------------------------------------------------------
TRon · 2020-08-15 00:38

Hi Madref,

I don't think I have ever seen someone or some example use
Applyupdates for a sql set statement. afaik it is used when manually
editing (multiple) records.

You have deactivated your query, then set a parameter, to activate
your query again, which does not make sense to me.

You should set the parameter before you actually "execute" the sql
statement, e.g. directly after you set the SQL text.

But even then I have my doubts it will works as UpdateSQL seems to be
a special one, see https://www.freepascal.org/docs-
html/fcl/sqldb/tsqlquery.updatesql.html
QuoteNote that old field values can be specified as :OLD_FIELDNAME
Which seems to suggest that you are actually telling the statement to
look for a field with that name (and its corresponding value).

But first try to fix the ParamByName issue first, then feel free to
report back how that went.

----------------------------------------------------------------------
TRon · 2020-08-15 02:59

Ah, indeed that does not seem to work madref. See
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.params.html
QuoteThe Update, delete and insert SQL statements are not scanned for
parameters.

In case possible, I would suggest using something like:
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 tbl1 :=
'tbl_Evaluatie_Referee';    cSQL := 'UPDATE ' + tbl1 + ' SET ' +     
     'EVO_Verstuurd = 1, ' +            'EVO_Verstuurd_Datum =
:Verstuurd ' +            'WHERE EVO_ID = ' + Save_ID.ToString;    
Form_Lint.TQ_Rapport_Master.Active := False;   
Form_Lint.TQ_Rapport_Master.DataBase := Form_Lint.Connect_RefereeDB; 
 Form_Lint.TQ_Rapport_Master.SQL.Text := cSQL;   
Form_Lint.TQ_Rapport_Master.ParamByName('Verstuurd').AsDate :=
EncodeToJulian(Date());    Form_Lint.TQ_Rapport_Master.ExecSQL;   
Form_Lint.TQ_Rapport_Master.Transaction.Commit;   
Form_Lint.TQ_Rapport_Master.Active := False;

----------------------------------------------------------------------
madref · 2020-08-15 07:46

Well I was trying to get it to work and then you try everything which
gives you some really funny code.
Later on I always try to clean it up 

Grrr..... the code you suggested does not work.
this does not work for some reason
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
Form_Lint.TQ_Rapport_Master.Transaction.Commit;

----------------------------------------------------------------------
TRon · 2020-08-15 13:44

Quote from: madref on August 15, 2020, 09:46:17 amWell I was trying to
get it to work and then you try everything which gives you some really
funny code.
Later on I always try to clean it up 
Ok, so it is quite possible I experienced your steps as being a bit
'weird' 

Don't worry though, I do not know all about sql myself, so wondered if
your approach was something new I could learn from.

I have never used sqlupdate myself as the documentation seems to
suggest that it is created automatically for you, and is only required
to be used in special rare cases.

QuoteGrrr..... the code you suggested does not work.
this does not work for some reason
I appreciate very much the feedback, but it is rather unsatisfactory
feedback. My electrical toothbrush doesn't work can also mean so many
things, e.g. batteries dead, brush worn out, toothpaste won't stick on
the brush, it stops spinning when applying etc. etc.

So, could you elaborate one what exactly is not working for you ?

Do you make use of transactions to begin with ? afaik you should, in
which case how did you named it ? (right now I simply assumed you had
it setup in the DB, and could use that statement but might not apply
for your current code).

When using a query to update record I need to use commit to actually
make sure the changes are committed to the database.

What happen for you when you leave out the commit ?

----------------------------------------------------------------------
madref · 2020-08-15 15:07

what I am trying to do is that I am sending an email to someone and I
am registering that by the date that I have send it and put in a 1 for
sending it....
So i am only updating these two field in my table.
The procedure I am using to connect to my database
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_Lint.Database_Verbinden;begin  if not DirectoryExists(UserDir)
 then ForceDirectories(UserDir);  if FileExists(UserDir + RefereeDBF)
and (RefereeDBF <> '') then    begin     
Connect_RefereeDB.DatabaseName := UserDir + RefereeDBF;     
Connect_RefereeDB.Connected := True;      Trans_RefereeDB.DataBase :=
Connect_RefereeDB;      Form_Lint.Caption := PrgNaam;      if
CheckVersie = false then begin        Form_Melding.Melding (mUitroep1,
bOK,          'Current database file is' + sCrLf +          'NOT for
this version !!' + sCrLf + sCrLf +          'Contact vendor for
database update !!',          'Database Version Check !!');       
Application.Terminate;        Exit;      end;    end  else     //
Exists Not !!    begin      Form_Melding.Melding (mVraag1, bYesNo,
'Are you sure that you want to' +        chr(13) + chr(10) + 'create a
new database?', PrgNaam +' [New Database]');      if
Form_MessageButtonPressed = 1 then begin        Form_Melding.Melding
(mUitroep1, bOK,          'You can only use this app if you create a
new database. ' +          'Restart the app after exit and create a
new database !!',          PrgNaam + ' [Create Database]');       
Application.Terminate;        Exit;      end  // if    else     
begin         Database_Aanmaken;      end;    // if
Form_MessageButtonPressed  end; // Else FileExistsend;     //
Database_Verbinden When I use the statement as you suggested\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
Form_Lint.TQ_Rapport_Master.Transaction.Commit;I get a compiling
error: Identefier idents no member "commit" and the compiling is
stopped.


If I leave the statement out it compiles like a charm, but no update
of the record is done.
I also have not used an update-query often and that is why I am
running into this problem.

----------------------------------------------------------------------
TRon · 2020-08-15 15:15

Oops, I am terribly sorry about that madref .

I made an error, as I wrongfully identified TQ_Rapport_Master was your
database... but it is actually your query
(no excuse as the rest of my suggested code identified it as being
your query, just a facepalm moment for me)

Use your transaction instead, "Trans_RefereeDB.commit" or you could
perhaps use "Connect_RefereeDB.Transaction.Commit"

----------------------------------------------------------------------
madref · 2020-08-15 15:44

Well I figured it out.


Both our code had a small flaw. But I fixed it.
Indeed your code needed the Form_Lint.Trans_RefereeDB.CommitRetaining


And mine needed a small code change. I was re-assigning Save_ID to a
different value so that the corresponding record was false.


Now that I have corrected both problems IT WORKS.


Final piece of code looks like this:
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 ' + tbl1 + ' SET ' +            'EVO_Verstuurd = 1, ' +      
    'EVO_Verstuurd_Datum = :Verstuurd ' +            'WHERE EVO_ID =
:EID;';    Form_Lint.TQ_Rapport_Master.Active := False;   
Form_Lint.TQ_Rapport_Master.Clear;   
Form_Lint.TQ_Rapport_Master.DataBase := Form_Lint.Connect_RefereeDB; 
 Form_Lint.TQ_Rapport_Master.SQL.Text := cSQL;   
Form_Lint.TQ_Rapport_Master.Params.ParamByName('Verstuurd').AsDate :=
EncodeToJulian(Date());   
Form_Lint.TQ_Rapport_Master.Params.ParamByName('EID').AsWord :=
Save_ID;    Form_Lint.TQ_Rapport_Master.ExecSQL;    if
Form_Lint.Trans_RefereeDB.Active then
Form_Lint.Trans_RefereeDB.CommitRetaining;

----------------------------------------------------------------------
TRon · 2020-08-15 16:14

Glad that you were ale to fix your issue.

Thank you for the feedback and posting your fixed version (as it
allows me to verify and/or learn from it as well).

And of course using CommitRetaining is perfectly valid as well (It did
not occur to me when I replied).

Again, i'm sorry about my mix up between db/transaction and query.