Recent

Author Topic: [Solved] Update error Parameter not Found  (Read 1471 times)

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
[Solved] Update error Parameter not Found
« on: August 15, 2020, 01:44:41 am »
I am trying to do an update on my database with with following code
Code: Pascal  [Select][+][-]
  1.     tbl1 := 'tbl_Evaluatie_Referee';
  2.     cSQL := 'UPDATE ' + tbl1 + ' SET ' +
  3.             'EVO_Verstuurd = 1, ' +
  4.             'EVO_Verstuurd_Datum = :Verstuurd ' +
  5.             'WHERE EVO_ID = ' + Save_ID.ToString;
  6.  
  7.  
  8.     Form_Lint.TQ_Rapport_Master.Active := False;
  9.     Form_Lint.TQ_Rapport_Master.DataBase := Form_Lint.Connect_RefereeDB;
  10.     Form_Lint.TQ_Rapport_Master.SQL.Text := 'SELECT * FROM ' + tbl1 + ';';
  11.     Form_Lint.TQ_Rapport_Master.UpdateSQL.Text := cSQL;
  12.     Form_Lint.TQ_Rapport_Master.Active := True;
  13.     Form_Lint.TQ_Rapport_Master.Active := False;
  14.     Form_Lint.TQ_Rapport_Master.ParamByName('Verstuurd').AsDate := EncodeToJulian(Date());
  15.     Form_Lint.TQ_Rapport_Master.ApplyUpdates;
  16.     Form_Lint.TQ_Rapport_Master.Active := True;
  17.  


But I get an error as displayed in the attachment.


tbl_referee is defined as:
Code: Pascal  [Select][+][-]
  1.     // tbl_Evaluatie_Referee
  2.     cSQL := '';
  3.     cSQL := 'CREATE TABLE tbl_Evaluatie_Referee (' +
  4.             'Evo_ID WORD NOT NULL PRIMARY KEY ASC, ' +
  5.             'Evo_Scheids_ID WORD, ' +
  6.             'Evo_Wed_ID WORD, ' +
  7.             'Evo_Systeem WORD, ' +
  8.             'Evo_SoortWedstrijd WORD, ' +
  9.             'Evo_Supervisor WORD, ' +
  10.             'Evo_Goal_TeamA_Per1 WORD DEFAULT(0), ' +
  11.             'Evo_Goal_TeamA_Per2 WORD DEFAULT(0), ' +
  12.             'Evo_Goal_TeamA_Per3 WORD DEFAULT(0), ' +
  13.             'Evo_Goal_TeamA_Per4 WORD DEFAULT(0), ' +
  14.             'Evo_Goal_TeamB_Per1 WORD DEFAULT(0), ' +
  15.             'Evo_Goal_TeamB_Per2 WORD DEFAULT(0), ' +
  16.             'Evo_Goal_TeamB_Per3 WORD DEFAULT(0), ' +
  17.             'Evo_Goal_TeamB_Per4 WORD DEFAULT(0), ' +
  18.             'Evo_Straf_TeamA_Per1 WORD DEFAULT(0), ' +
  19.             'Evo_Straf_TeamA_Per2 WORD DEFAULT(0), ' +
  20.             'Evo_Straf_TeamA_Per3 WORD DEFAULT(0), ' +
  21.             'Evo_Straf_TeamA_Per4 WORD DEFAULT(0), ' +
  22.             'Evo_Straf_TeamB_Per1 WORD DEFAULT(0), ' +
  23.             'Evo_Straf_TeamB_Per2 WORD DEFAULT(0), ' +
  24.             'Evo_Straf_TeamB_Per3 WORD DEFAULT(0), ' +
  25.             'Evo_Straf_TeamB_Per4 WORD DEFAULT(0), ' +
  26.             'Evo_Categorie_01 WORD DEFAULT(0), ' +
  27.             'Evo_Categorie_02 WORD DEFAULT(0), ' +
  28.             'Evo_Categorie_03 WORD DEFAULT(0), ' +
  29.             'Evo_Categorie_04 WORD DEFAULT(0), ' +
  30.             'Evo_Categorie_05 WORD DEFAULT(0), ' +
  31.             'Evo_Categorie_06 WORD DEFAULT(0), ' +
  32.             'Evo_Categorie_07 WORD DEFAULT(0), ' +
  33.             'Evo_Categorie_08 WORD DEFAULT(0), ' +
  34.             'Evo_Categorie_09 WORD DEFAULT(0), ' +
  35.             'Evo_Categorie_10 WORD DEFAULT(0), ' +
  36.             'Evo_Categorie_11 WORD DEFAULT(0), ' +
  37.             'Evo_Categorie_12 WORD DEFAULT(0), ' +
  38.             'Evo_Categorie_13 WORD DEFAULT(0), ' +
  39.             'Evo_Categorie_14 WORD DEFAULT(0), ' +
  40.             'Evo_Categorie_15 WORD DEFAULT(0), ' +
  41.             'Evo_Categorie_16 WORD DEFAULT(0), ' +
  42.             'Evo_Categorie_17 WORD DEFAULT(0), ' +
  43.             'Evo_SterkePunten TEXT, ' +
  44.             'Evo_VerbeterPunten TEXT, ' +
  45.             'Evo_Feedback TEXT, ' +
  46.             'Evo_MinorA WORD DEFAULT(0), ' +
  47.             'Evo_MinorB WORD DEFAULT(0), ' +
  48.             'Evo_MajorA WORD DEFAULT(0), ' +
  49.             'Evo_MajorB WORD DEFAULT(0), ' +
  50.             'Evo_MiscA WORD DEFAULT(0), ' +
  51.             'Evo_MiscB WORD DEFAULT(0), ' +
  52.             'Evo_GMPA WORD DEFAULT(0), ' +
  53.             'Evo_GMPB WORD DEFAULT(0), ' +
  54.             'Evo_MPA WORD DEFAULT(0), ' +
  55.             'Evo_MPB WORD DEFAULT(0), ' +
  56.             'Evo_PSA WORD DEFAULT(0), ' +
  57.             'Evo_PSB WORD DEFAULT(0), ' +
  58.             'EVO_Verstuurd BOOLEAN, ' +
  59.             'EVO_Verstuurd_Datum DATE ' +
  60.             ')';
  61.  
« Last Edit: August 15, 2020, 05:45:26 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

TRon

  • Hero Member
  • *****
  • Posts: 2398
Re: Update error Parameter not Found
« Reply #1 on: August 15, 2020, 02:38:42 am »
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
Quote
Note 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

  • Hero Member
  • *****
  • Posts: 2398
Re: Update error Parameter not Found
« Reply #2 on: August 15, 2020, 04:59:58 am »
Ah, indeed that does not seem to work madref. See https://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.params.html
Quote
The Update, delete and insert SQL statements are not scanned for parameters.

In case possible, I would suggest using something like:
Code: Pascal  [Select][+][-]
  1. tbl1 := 'tbl_Evaluatie_Referee';
  2.     cSQL := 'UPDATE ' + tbl1 + ' SET ' +
  3.             'EVO_Verstuurd = 1, ' +
  4.             'EVO_Verstuurd_Datum = :Verstuurd ' +
  5.             'WHERE EVO_ID = ' + Save_ID.ToString;
  6.  
  7.     Form_Lint.TQ_Rapport_Master.Active := False;
  8.     Form_Lint.TQ_Rapport_Master.DataBase := Form_Lint.Connect_RefereeDB;
  9.     Form_Lint.TQ_Rapport_Master.SQL.Text := cSQL;
  10.     Form_Lint.TQ_Rapport_Master.ParamByName('Verstuurd').AsDate := EncodeToJulian(Date());
  11.     Form_Lint.TQ_Rapport_Master.ExecSQL;
  12.     Form_Lint.TQ_Rapport_Master.Transaction.Commit;
  13.     Form_Lint.TQ_Rapport_Master.Active := False;
  14.  

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Update error Parameter not Found
« Reply #3 on: August 15, 2020, 09:46:17 am »

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][+][-]
  1. Form_Lint.TQ_Rapport_Master.Transaction.Commit;
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

TRon

  • Hero Member
  • *****
  • Posts: 2398
Re: Update error Parameter not Found
« Reply #4 on: August 15, 2020, 03:44:58 pm »
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  :)
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.

Quote
Grrr..... 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. :D

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

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Update error Parameter not Found
« Reply #5 on: August 15, 2020, 05:07:12 pm »
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][+][-]
  1. procedure TForm_Lint.Database_Verbinden;
  2. begin
  3.   if not DirectoryExists(UserDir)  then ForceDirectories(UserDir);
  4.   if FileExists(UserDir + RefereeDBF) and (RefereeDBF <> '') then
  5.     begin
  6.       Connect_RefereeDB.DatabaseName := UserDir + RefereeDBF;
  7.       Connect_RefereeDB.Connected := True;
  8.       Trans_RefereeDB.DataBase := Connect_RefereeDB;
  9.       Form_Lint.Caption := PrgNaam;
  10.       if CheckVersie = false then begin
  11.         Form_Melding.Melding (mUitroep1, bOK,
  12.           'Current database file is' + sCrLf +
  13.           'NOT for this version !!' + sCrLf + sCrLf +
  14.           'Contact vendor for database update !!',
  15.           'Database Version Check !!');
  16.         Application.Terminate;
  17.         Exit;
  18.       end;
  19.     end
  20.   else     // Exists Not !!
  21.     begin
  22.       Form_Melding.Melding (mVraag1, bYesNo, 'Are you sure that you want to' +
  23.         chr(13) + chr(10) + 'create a new database?', PrgNaam +' [New Database]');
  24.       if Form_MessageButtonPressed = 1 then begin
  25.         Form_Melding.Melding (mUitroep1, bOK,
  26.           'You can only use this app if you create a new database. ' +
  27.           'Restart the app after exit and create a new database !!',
  28.           PrgNaam + ' [Create Database]');
  29.         Application.Terminate;
  30.         Exit;
  31.       end  // if
  32.     else
  33.       begin
  34.          Database_Aanmaken;
  35.       end;    // if Form_MessageButtonPressed
  36.   end; // Else FileExists
  37. end;     // Database_Verbinden
  38.  
When I use the statement as you suggested\
Code: Pascal  [Select][+][-]
  1. 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.
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

TRon

  • Hero Member
  • *****
  • Posts: 2398
Re: Update error Parameter not Found
« Reply #6 on: August 15, 2020, 05:15:52 pm »
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"
« Last Edit: August 15, 2020, 05:23:53 pm by TRon »

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Update error Parameter not Found
« Reply #7 on: August 15, 2020, 05:44:07 pm »
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][+][-]
  1.     cSQL := 'UPDATE ' + tbl1 + ' SET ' +
  2.             'EVO_Verstuurd = 1, ' +
  3.             'EVO_Verstuurd_Datum = :Verstuurd ' +
  4.             'WHERE EVO_ID = :EID;';
  5.     Form_Lint.TQ_Rapport_Master.Active := False;
  6.     Form_Lint.TQ_Rapport_Master.Clear;
  7.     Form_Lint.TQ_Rapport_Master.DataBase := Form_Lint.Connect_RefereeDB;
  8.     Form_Lint.TQ_Rapport_Master.SQL.Text := cSQL;
  9.     Form_Lint.TQ_Rapport_Master.Params.ParamByName('Verstuurd').AsDate := EncodeToJulian(Date());
  10.     Form_Lint.TQ_Rapport_Master.Params.ParamByName('EID').AsWord := Save_ID;
  11.     Form_Lint.TQ_Rapport_Master.ExecSQL;
  12.     if Form_Lint.Trans_RefereeDB.Active then Form_Lint.Trans_RefereeDB.CommitRetaining;
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

TRon

  • Hero Member
  • *****
  • Posts: 2398
Re: [Solved] Update error Parameter not Found
« Reply #8 on: August 15, 2020, 06:14:38 pm »
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.


 

TinyPortal © 2005-2018