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

Question

I'm trying to insert a record into a table in a 3-tier database setup, and the middle-tier server generates the error message above as an OLE exception when it tries to add the first parameter to the query.

I've Googled this error, and I find the same result consistently: it comes from having a colon in a string somewhere in your query, which b0rks ADO's SQL parser. This is not the case here. There are no spurious colons anywhere. I've checked and rechecked the object definition against the schema for the table I'm trying to insert into. Everything checks out, and this has my coworkers stumped. Does anyone know what else could be causing this? I'm at my wits' end here.

I'm using Delphi 2007 and SQL Server 2005.

Answer 1

If I remember well, you have to explicit put NULL value to the parameter. If you are using a TAdoStoredProc component, you should do this in design time.

Answer 2

Are you using any threading? I seem to remember getting this error when a timer event started a query while the ADO connection was being used for another synchronous query. (The timer was checking a "system available" flag every minute).

Answer 3

Have you set the DataType of the parameter or did you leave it as ftUnknown?

Answer 4

I can get this error, using Delphi 2007 and MSSQL Server 2008, and I found a workaround. (which is pretty crappy IMHO, but maybe its useful to you if yours is caused by the same thing.)

code to produce the error:

with TADOQuery.Create(nil) do try

   Connection := ADOConnection;

   SQL.Text := ' (SELECT * FROM Stock WHERE  InvCode = :InvCode ) '
              +' (SELECT * FROM Stock WHERE  InvCode = :InvCode ) ';

   Prepared := true;

   Parameters.ParamByName('InvCode').Value := 1;

   Open;  // <<<<< I get the "parameter object is...etc. error here.

 finally
   Free;
 end;

I found two ways to fix it:

1) remove the brackets from the SQL, ie:

   SQL.Text := ' SELECT * FROM Stock WHERE  InvCode = :InvCode  '
              +' SELECT * FROM Stock WHERE  InvCode = :InvCode  ';

2) use two parameters instead of one:

with TADOQuery.Create(nil) do try

   Connection := ADOConnection;

   SQL.Text := ' (SELECT * FROM Stock WHERE  InvCode = :InvCode1 ) '
              +' (SELECT * FROM Stock WHERE  InvCode = :InvCode2 ) ';

   Prepared := true;

   Parameters.ParamByName('InvCode1').Value := 1;
   Parameters.ParamByName('InvCode2').Value := 1;

   Open;  // <<<<< no error now.

 finally
   Free;
 end;

Answer 5

Here a late reply. In my case it was something completely different.

I tried to add a stored procedure to the database.

Query.SQL.Text :=
'create procedure [dbo].[test]' + #13#10 +
'@param int ' + #13#10 +
'as' + #13#10 + 
'-- For the parameter you can pick two values:' + #13#10 + 
'-- 1: Value one' + #13#10 + 
'-- 2: Value two';

When I removed the colon (:) it worked. As it saw the colon as a parameter.