"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, May 23 '12 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, May 23 '12 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, May 23 '12 at 18:15
@MikaelEriksson You're right. ADO doesn't support named parameters; that was my (third) problem. — Ian Boyd, May 23 '12 at 18:27

score 14 · Accepted Answer · answered May 23 '12 at 18:16

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 '12 at 18:16

RRUZ

134,889
20
356
483

3

Well that certainly explains why named parameters aren't working in ADO. *oi vay Microsoft* – Ian Boyd May 23 '12 at 18:26

Hendra · Answer 2 · 2012-05-24T09:54:56.220

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.

score 0 · Answer 3 · answered Feb 11 '13 at 18:55

Use Parameters.AddWithValue as shown below

  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Database Password=RainbowTrout;";
  InsertQry = "Insert into Sections(Name, PartNumber, VersionNumber, Channel, Address, Status, IPAddr) "
        + "values(@SectionName, @PartNumber, @VersionNumber, @Channel, @Address, @Status, @IPAddr)";


  NewCfgConnection.ConnectionString = string.Format(connectionString, ConfigFN);
  NewCfgCommand.Connection = NewCfgConnection;
  NewCfgCommand.CommandText = InsertQry;
  NewCfgConnection.Open();

  // Clear parameter values from last record
  NewCfgCommand.Parameters.Clear();

  // Insert record into sections table - set parameters
  NewCfgCommand.Parameters.AddWithValue("@SectionName", sSectionName);
  NewCfgCommand.Parameters.AddWithValue("@PartNumber", sPartNumber);
  NewCfgCommand.Parameters.AddWithValue("@VersionNumber", sVersionNumber);
  NewCfgCommand.Parameters.AddWithValue("@Channel", iChannel);
  NewCfgCommand.Parameters.AddWithValue("@Address", iAddress);
  NewCfgCommand.Parameters.AddWithValue("@Status", iStatus);
  NewCfgCommand.Parameters.AddWithValue("@IPAddr", iIP);

  NewCfgCommand.ExecuteNonQuery();

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

3 Answers3

Linked