Search code examples
sql-serverdelphiadoparameterized-query

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

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?

Solution

  • 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.