Delphi: "The parameter object is incorrectly defined. Inconsistent or incomplete information provided."

Question

This is a function that performs the following actions:

• Create a random token with a length of 8
• Paste this token into the database

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

• If the user does not have a 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; 

Doing this throws the exception indicated in the header. I cut the above example down and voila: no more exceptions. Sorry, I don’t understand 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 SQL has only 1 parameter. I am using Delphi 7 and MSSQL Server 2005

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

Answer 1

To do this, you must use each parameter only once in the SQL statement. To use the same parameter more than once, simply declare it with a new name. I do not know why this is so, but I know that it can be quite unpleasant.

Answer 2

I had the opportunity to try this using the compiler :) I have to install it one day at home.

While I still find your WITH usage unusual, it seems to work fine.

I saw the error you get in several cases:

• Trying to run multiple requests against the connection at once (due to thread or timer + processMessages)
• With TADOStoredProc when an invalid procedure name
• Sometimes, if ADO cannot parse the query, it cannot verify this without your DB schema.

Note that in SQL Server there is no need to explicitly determine the type of parameter. They are automatically assigned by the OnChanged event attached to the TStringList SQL object.

As a result, it is best to either set the SQL.Text property (like you) or use .Add ('SELECT ...'), use the SQL.BeginUpdate / SQL.EndUpdate pair.

Original answer:

  with AQ_Query do begin try AQ_Query := TADOQuery.Create(nil); ConnectionString := strConnectionString; 

While this works, it seems a little strange to refer to an object before you create it.

AQ_Query must be instantiated before the with statement:

  AQ_Query := TADOQuery.Create(nil); with AQ_Query do begin try ConnectionString := strConnectionString; 

It's better not to use WITH yet - it asks for trouble.

Also note that the creation of the object must be before trying. As written, you will have a compiler warning. Do not ignore them - they help you write code better.

Answer 3

While this error is a complex task, you can diagnose it enough to make sure that your request is valid. The problem is with your settings. The best way to find the true problem is for the SQL Server Profiler to keep track of your database as the query comes in. It will show you how the parameters were interpreted. Copy this query into a text editor to find out where your problem is.

If you cannot use the SQL Server Profiler, you should simply display the "BenuNr" and "strToken" values ​​on the screen or console so that you can really see what you are passing as parameters.

Answer 4

So far I have not fixed the problem.

But I think the problem lies somewhere inside the parameters and how they are available. The compiler selects them by index, and not by name, as I assumed.

When you familiarize yourself with the TADOQuery component in the OI (especially in the TParameters section), you can see these indices.

In the end, you need to do this first, add a parameter, give it a name, and then insert a value or something like that.

Answer 5

Turn off preparation. Set prepared = False on the ADO component. It seems that the server sees this before both parameters exist and prepare (compile) it. When you execute it with two parameters, the parameter list does not match the prepared statement.

Answer 6

Uch. You were simply struck by what our systems architect at work calls the “worst mistake ever”. This is a pretty common mistake and can mean all kinds of things. But I only saw this in INSERT statements, so try looking there. This is caused by the determination that ADO somehow does not match the database schema.

Try cutting out your query to just paste and use the SQL profiler included with SQL Management Studio to see what ADO does when it starts. Most likely, he will ask for the structure of your table, compare it with the structure of your operator and will not like what he finds, and never sends the INSERT command to the database.

Make sure that you have the correct data types in the fields, and that you can successfully run INSERT in this table using only these two values. It may not work - it is the worst mistake ever, but it should give you a starting point.

Answer 7

You do not need to specify a DataType . After a successful call to Prepare; Parameters must be correctly configured based on the server table definition.

My guess is that when assigning a DataType , the parameter is probably reset, and some information is missing, for example, ParamType should be ptInput , but from reset to ptUnknown or something like that.

Try removing the lines where you install the DataType and see if that helps.

Answer 8

For me, this error occurred without parameters when the (otherwise fully valid) SQL statement contained a varchar literal value with a colon (:) inside the cited subtext. For instance.

 UPDATE ... SET myfield = 'foo " :bar "' 

The solution might be to switch to parameters from ugly inline literals. I have not yet found another suitable solution.