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

Question

I am trying to use parameterized queries with ADO. Executing a Command object throws an error:

Must declare the variable '@filename'

i declare the @filename parameter 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?

Answer 1

As far as I know, ADO does not support named parameters in SQL statements (SELECT, INSERT, UPDATE), so what should you use ? char to specify a parameter

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

and then assign the parameter values ​​in the same order as in the sql clause.

ADO 2.6 Introduces the NamedParameters property, but it looks like it only works with stored procedures.

Answer 2

try it

 uses ADODB, DB; ... ... ... and then in some event handler (eg 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 used the parameter by name for TADOCommand and TADOQuery without any problems.

Answer 3

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();