• Registered: 2014-08-13
  • Posts: 94

Topic: sqlexecute notworking

i am not able to get my sql statements to execute properly. i am sure it has to do with the id i am picking or syntax. is there any documentation available anywhere on using the sqlexecute in scripts, with forms, syntax etc. i dont seem to be able to find anything around. i have dl most of the samples but they dont explain the what and why of the statement.

thanks

lee

Post's attachments

Attachment icon Billiards 3.zip 114.44 kb, 571 downloads since 2016-05-24 

  • Registered: 2014-08-02
  • Posts: 906

Re: sqlexecute notworking

Hello ihimes

With form RPTMenu, error message is really clear :     matches.hh1,  matches.hh2 don't belong to table Matches.

But this table has two fields called  h11 and h12..
Perhaps an error syntax from you.

Also, you sql statement was not closed with a semi-colon (;)
This says to Delphi this is the end of your instruction.

When yo fix those problems, Report Designer opens and you can go on with your rapport.

JB

  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

Hi JB

sorry about that. thats what i get for not being more clear about my problems. the rptmene form is just something i work on a bit at a time when i cant; get other stuff to work. the sqlexecute problems i am having are in the script. trying to increase the count in fields every time a match is played.

lee

  • Registered: 2014-02-14
  • Posts: 10,294

Re: sqlexecute notworking

lhimes wrote:

Hi JB

sorry about that. thats what i get for not being more clear about my problems. the rptmene form is just something i work on a bit at a time when i cant; get other stuff to work. the sqlexecute problems i am having are in the script. trying to increase the count in fields every time a match is played.

lee

Hello,


Please let me know, which exactly sql query don't work in your script.

Dmitry.
  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

Hello Dmitry

all of them except the datetimepicker one. they are all basically the same so if this one
works, the rest should work as well.

line 68:
form1.edit42.Value := sqlexecute('select wksplayed from matches where id =' +inttostr(playersform.combobox1.dbitemid))+1;

thanks

lee

  • Registered: 2014-02-14
  • Posts: 10,294

Re: sqlexecute notworking

lhimes wrote:

Hello Dmitry

all of them except the datetimepicker one. they are all basically the same so if this one
works, the rest should work as well.

line 68:
form1.edit42.Value := sqlexecute('select wksplayed from matches where id =' +inttostr(playersform.combobox1.dbitemid))+1;

thanks

lee

ComboBox1 have foreign key "teammates.id_Teams"
but in your SQL query you try to find in the table "matches"
It does not make sense.

Dmitry.
  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

Hi Dmitry

at this point ;i was grasping at straws trying to find anything to work. i had tried eerything i could think of and nothing worked so i posted here looking for help. what you see is my last attempt.

thanks

lee

  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

can someone help with this problem?

lee

  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

ok, so maybe that one was too tough. i have simplified my project and attached it. i have the same problem in line 5 of the script. i am trying to add 1 each time the team plays. the sql command seems to work in sqlite studio but when i run it here i get "could not convert variant of type (UniCodeString) into type (double)".  any help would be appreciated.

lee

Post's attachments

Attachment icon My Sample 4.zip 19.57 kb, 593 downloads since 2016-06-06 

  • Registered: 2014-02-14
  • Posts: 10,294

Re: sqlexecute notworking

lhimes wrote:

ok, so maybe that one was too tough. i have simplified my project and attached it. i have the same problem in line 5 of the script. i am trying to add 1 each time the team plays. the sql command seems to work in sqlite studio but when i run it here i get "could not convert variant of type (UniCodeString) into type (double)".  any help would be appreciated.

lee

This query is not correct:

form1.edit22.Value := sqlexecute('select wksplayed from matches where id_teams = (select id from teams)')+1;

What exactly you want to do using this query?

Dmitry.
  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

i want to take the value in wksplayed, add 1 to it and save it back to wksplayed using form1.edit22

thanks for the response

lee

  • Registered: 2014-02-14
  • Posts: 10,294

Re: sqlexecute notworking

lhimes wrote:

i want to take the value in wksplayed, add 1 to it and save it back to wksplayed using form1.edit22

thanks for the response

lee

Please try to replace this line

   form1.edit22.Value := sqlexecute('select wksplayed from matches where id_teams = (select id from teams)')+1;

to

   form1.edit22.Value := sqlexecute('select wksplayed from matches where id_teams = ' + Form1.ComboBox1.sqlValue)+1;
Dmitry.
  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

i get the same error

lee

  • Registered: 2014-02-14
  • Posts: 10,294

Re: sqlexecute notworking

lhimes wrote:

i get the same error

lee

Please check it:
form1.edit22.Value := sqlexecute('select IFNULL(wksplayed, 0) from matches where id_teams = ' + Form1.ComboBox1.sqlValue)+1;

Dmitry.
  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

now i dont get the error but it doesnt add the 1

lee

  • Registered: 2014-02-14
  • Posts: 10,294

Re: sqlexecute notworking

lhimes wrote:

now i dont get the error but it doesnt add the 1

lee

Checked, it works.

Dmitry.
  • Registered: 2014-08-13
  • Posts: 94

Re: sqlexecute notworking

if i pick a team then save it, the first time it adds 1. if i pick the same team again, it wont add the 1.  it needs to add it every time the team plays not just the first time.

lee