CODEWITHSUNDEEP
sql-serverdelphioledbado
Ian Boyd
Ian Boyd

Reputation: 256581

ADO not passing first parameter value

I'm using an ADO Command object to generate parameterized queries against SQL Server.

If i generate and supply multiple parameters, all the parameter values are passed - except the first one.

If you imagine a query like:

SELECT ?, ?, ?, ?, ?

And use the ADO Command object to supply parameter values:

command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 1);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 2);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 3);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 4);
command.Parameters.Append(command.CreateParameter('', adInteger, adParamInput, 0, 5);

You can use Profiler to see the first parameter value is null:

enter image description here

exec sp_executesql N'SELECT @P1, @P2, @P3, @P4, @P5',N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int',NULL,2,3,4,5

I've tried it with different types, in different queries, in different orders. It's always the first parameter that refuses to be supplied to the database server.

And i can confirm that the parameter does have a value before calling Execute:

command.Parameters[0].Value

What am i doing wrong?

CMRE

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  ActiveX,
  ComObj,
  ADOint,
  Variants;

procedure Main;
var
    cs: string;
    cn: Connection;
    cmd: Command;
    p: _Parameter;
    recordsAffected: OleVariant;
begin
    cs := 'Provider=SQLOLEDB;Data Source=screwdriver;User ID=frog;Password=hunter2';
    cn := CoConnection.Create;
    WriteLn('Connecting to database...');
    cn.Open(cs, '', '', Integer(adConnectUnspecified));

    cmd := CoCommand.Create;
    cmd.CommandType := adCmdText;
    cmd.CommandText := 'IF ? IS NULL RAISERROR(''It was null'', 16, 1);';

    cmd.Parameters.Append(cmd.CreateParameter('', adinteger, adParamInput, 0, 1));

    cmd.Set_ActiveConnection(cn);

    WriteLn('Executing command');
    cmd.Execute({out}recordsAffected, Null, adExecuteNoRecords);
end;

begin
  try
    CoInitialize(nil);
     Main;
        WriteLn('Success');
  except
     on E: Exception do
        begin
            Writeln(E.ClassName, ': ', E.Message);
        end;
  end;
    WriteLn('Press enter to close...');
    ReadLn;

end.

Bonus Reading

Upvotes: 2

Views: 343

Answers (1)

kobik
kobik

Reputation: 21232

You should use EmptyParam instead of Null in your cmd.Execute statement (which is the Parameters argument). 

cmd.Execute({out}recordsAffected, EmptyParam, adExecuteNoRecords);

See: Execute Method (ADO Command)

The EmptyParam should be compatible with an Optional ole parameter. You could also use OleVariant(cmd).Execute in your example.

Upvotes: 3

Related Questions