"Must declare the variable @myvariable" error with ADO parameterized query

Question

i am trying to use parameterized queries with ADO. Executing the Command object throws the error:

Must declare the variable '@filename'

i declare the parameter @filename using CreateParameter/Append:

sql := 'INSERT INTO Sqm(Filename, data) VALUES(@filename, @data)';

command := CoCommand.Create;
command.Set_ActiveConnection(Connection.ConnectionObject);
command.Set_CommandText(sql);
command.Set_CommandType(adCmdText);
command.Parameters.Append(Command.CreateParameter('@filename', adLongVarWChar, adParamInput, -1, Filename));
command.Parameters.Append(Command.CreateParameter('@data', adLongVarWChar, adParamInput, -1, xml);

command.Execute({out}recordsAffected, EmptyParam, adCmdText or adExecuteNoRecords);

What am i doing wrong?

Try with : instead of @ before the params in the query. And remove the @ from where you add the parameters. — Mikael Eriksson
– Mikael Eriksson, Commented May 23, 2012 at 17:51
@MikaelEriksson: Line 1: Incorrect syntax near ':' You'd be surprised, i certainly was, that nobody has ever posted an example of ADO parameterized queries. (plenty to be found with ADO.NET) — Ian Boyd
– Ian Boyd, Commented May 23, 2012 at 17:57
Looks like you are using the ado interfaces directly instead of tadocommand. Try with a ? in the query where you put the variable name values (?, ?). I'm a couple of hours away from a computer so I can't verify that it will work yet. — Mikael Eriksson
– Mikael Eriksson, Commented May 23, 2012 at 18:15
@MikaelEriksson You're right. ADO doesn't support named parameters; that was my (third) problem. — Ian Boyd
– Ian Boyd, Commented May 23, 2012 at 18:27

RRUZ · Accepted Answer · 2012-05-23 18:16:31Z

14

As far i know ADO doesn't supports named parameters in SQL sentences (SELECT, INSERT, UPDATE), so you must use the ? char to indicate the parameter

sql := 'INSERT INTO Sqm(Filename, data) VALUES(?, ?)';

and then assign the parameters values in the same order as are used in the sql sentence.

ADO 2.6 Introduces the NamedParameters property, but it seems which only works with stored procedures.

answered May 23, 2012 at 18:16

RRUZ

137k20 gold badges365 silver badges489 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Boyd Over a year ago

Well that certainly explains why named parameters aren't working in ADO. oi vay Microsoft

Hendra · Accepted Answer · 2012-05-24 09:54:56Z

2

try this

uses ADODB, DB;
...
...
... and then in some event handler (e.g. button click),
var 
  aCommand :TADOCommand;
begin
  aCommand := TADOCommand.create(self);
  aCommand.ConnectionString := 'build the connection string or use TADOConnection and assign to Connection property instead of ConnectionString property';
  aCommand.commandText := 'INSERT INTO Sqm(Filename, data) VALUES(:filename, :data);';
  aCommand.parameters.paramByName('filename').value := 'test';
  aCommand.parameters.paramByName('data').value := 'some data';
  aCommand.execute;
  aCommand.free;
end;

I have been using parameter by names this way for TADOCommand and TADOQuery with no problem.

edited May 24, 2012 at 9:54

answered May 24, 2012 at 9:22

Hendra

7654 silver badges9 bronze badges

Collectives™ on Stack Overflow

"Must declare the variable @myvariable" error with ADO parameterized query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related