SQL insert statement with ADO parameters

[donvittorio]

Hi,

I'm trying to use an sql insert statement using ADO parameters, and I can't get the value of the parameter into the db, and it's driving me nuts! I'm using SQL Server 2000, Delphi 4 and ADO 2.1 (there's nothing like modern technology is there?!)
The code is simply this:

procedure TForm1.WriteToDB(aConn: _Connection);
var
  Command: _Command;
  Param: Parameter;
  RecsAff, Params: OleVariant;
begin
  Command := CoCommand.Create;
  Command.CommandText := 'Insert into MyTable(MyID, TextCol)'+
                         ' Values('''+VarToStr(FID)+''', ?)';
  Command.CommandType := adCmdText;
  Command.Set_ActiveConnection(aConn);
  Param := Command.CreateParameter('TextCol', adVarChar, adParamInput, 10, 'testval');
  Command.Parameters.Append(Param);
  Command.Execute(RecsAff, Params, adExecuteNoRecords);
end

The insert statement executes but the value of TextCol is null, what do I have to do to get the value of the parameter into the db??!! I've tried using

Param.Value := 'testval';

but that didn't help either. Any pointers would be much appreciated.

Steve

 

[harebrain]

Try this:

Command.CommandText := 'Insert into MyTable(MyID, TextCol)' + ' Values('''+VarToStr(FID)+''', :TextCol)';

Note that the parameter name doesn't have to be the same as the column name; I just left it as you had it in your CreateParameter method call.

 

[donvittorio]

harebrain,

thanks for your thoughts, but ADO doesn't use the 'aram' syntax, it uses '?param' instead. And SQL Server doesn't use it either, so the example that you gave gets passed to SQL Server as is, and SQL Server then reports the error "Incorrect syntax near ':'"

Steve