What are the "Missing SQL property" here?

Question

When trying to execute the code:

function TDBClass.addNome(nome: String): String;
var
  rsnome: TADOQuery;
begin
  rsnome := TADOQuery.Create(nil);
  rsnome.Connection := connection;
  rsnome.Open();
  rsnome.SQL.Clear;
  rsnome.SQL.Text:='UPDATE enroll SET nome = "test" where id ="1"';
  rsnome.Parameters.ParamByName('nome').Value:= nome;
  rsnome.ExecSQL;
  rsnome.post();
  rsnome.Close();
  rsnome.Free();
end;

I'm receiving the error message "Missing SQL property". Where did I go wrong?
Thanks in advance!

score 8 · Answer 1 · answered Nov 30 '11 at 12:47

8

You're calling rsnome.Open before setting the SQL by rsnome.SQL.Text := ....

answered Nov 30 '11 at 12:47

Ondrej Kelle

36,941
2
65
128

Parameters should be preceded with a ':' as in :NOME. You do not need the post() after the ExecSql and you may have to prepare the query for the parameter value to be assigned. [rsnome.PREPARED = True;]. – Pieter van Wyk Nov 30 '11 at 13:24
1

@Pieter, you don't need to set the [Prepared](http://docwiki.embarcadero.com/VCL/XE2/en/ADODB.TADOQuery.Prepared) property at all. It is used for caching parameter values if you are executing a query with the same parameter values repetitively, what is not this case. And calling [Open](http://docwiki.embarcadero.com/VCL/en/DB.TDataSet.Open) with the query from the question will fail with empty dataset. +1 for Ken's [answer](http://stackoverflow.com/a/8327084/960757). – TLama Nov 30 '11 at 17:04

score 5 · Accepted Answer · edited May 23 '17 at 12:20

5

I don't think you want to use Open at all, and you are using parameters incorrectly.
i.e. the SQL doesn't have any :PARAM placeholders in it. I think it should be something like: rsnome.SQL.Text:='UPDATE enroll SET nome = :NOME where id = :ID';

See this example: AdoQuery Error using parameters

edited May 23 '17 at 12:20

Community

1
1

answered Nov 30 '11 at 13:03

Chris Thornton

15,620
5
37
62

And encapsulate this in a try/finally block since your query object does not have an owner. – Sam M Nov 30 '11 at 13:53

score 4 · Answer 3 · answered Nov 30 '11 at 13:59

You have several errors. You're calling Open before you've assigned ths SQL (and without needing to do so).

You're trying to set a parameter value you haven't created a parameter to accept. (BTW, I'd make ID also a parameter, so you can use this to update more than one person's name.)

You're not handling making sure that things get cleaned up in case there's an error (which given the code you posted there certainly will be).

You're using Post, which isn't necessary with an SQL database.

Try something like this instead:

function TDBClass.addNome(nome: String): String;
var
  rsnome: TADOQuery;
begin
  rsnome := TADOQuery.Create(nil);
  try
    rsnome.Connection := connection;
    rsnome.SQL.Clear;
    rsnome.SQL.Text:='UPDATE enroll SET nome = :nome where id ="1"';
    rsnome.Parameters.ParamByName('nome').Value:= nome;
    rsnome.ExecSQL;
    rsnome.Close();
  finally
    rsnome.Free();
  end;
end;

What are the "Missing SQL property" here?

3 Answers3