Delphi: “Parameter object is improperly defined. Inconsistent or incomplete information was provided.”

Question

This is a Function that does the following:

• Create a random Token with 8 length
• Insert that Token into the Database

• If the User has already a token, update it.

• If the User has no token, insert it.

procedure createToken(BenuNr: integer);
var
  AQ_Query:       TADOQuery;
  strToken:       string;
  intZaehler:     integer;
  const cCharSet: string = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
begin
    //Random String as Token
    SetLength(strToken, 8);
  for intZaehler := 1 to 8 do
  begin
    strToken[intZaehler] := cCharSet[1+Random(Length(cCharSet))];
  end;
  //Inserts the Token into the Database
  with AQ_Query do
  begin
    try
      AQ_Query := TADOQuery.Create(nil);
      ConnectionString := strConnectionString;
      SQL.Text := 'if EXISTS(select * from TOKN where BENU_NR = :paramBenu_NR) begin update TOKN set TOKEN = :paramTOKEN where BENU_NR = :paramBenu_NR end else insert into TOKN (BENU_NR, TOKEN) values (:paramBENU_NR,:paramTOKEN)';
      Prepared := true;
      Parameters.ParamByName('paramBENU_NR').DataType := ftInteger;
      Parameters.ParamByName('paramTOKEN').DataType := ftString;
      Parameters.ParamByName('paramBENU_NR').Value := BenuNr;
      Parameters.ParamByName('paramTOKEN').Value := strToken;
      ExecSQL;    //<< Exception as stated in the title
    finally
      Free;
    end;
  end;
end;

Executing this throws me the exception as stated in the title. I cut the above example down and voila: no more exception. Unfortunately I don't get why?

procedure createToken();
var
  AQ_Query:     TADOQuery;
  strToken:     string;
  intZaehler:      integer;
  const cCharSet:  string = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
begin
    //Random String as Token
    SetLength(strToken, 8);
  for intZaehler := 1 to 8 do
  begin
    strToken[intZaehler] := cCharSet[1+Random(Length(cCharSet))];
  end;
  //Inserts the Token into the Database
  with AQ_Query do
  begin
    try
      AQ_Query := TADOQuery.Create(nil);
      ConnectionString := strConnectionString;
      SQL.Text := 'update TOKN set TOKEN = :paramTOKEN where BENU_NR = 1';
      Prepared := true;
      Parameters.ParamByName('paramTOKEN').DataType := ftString;
      Parameters.ParamByName('paramTOKEN').Value := strToken;
      ExecSQL;   //<< No more exception
    finally
      Free;
    end;
  end;
end;

It seems that there is only 1 Parameter allowed per SQL. I am using Delphi 7 and MSSQL Server 2005

Any idea how to fix the first code block to make it work?

Answer 1

To make this work you have to use every parameter only once in the SQL clause. To use same parameter more than once just declare it with new name. I don't know why this is so but I know it can be pretty annoying.