TAdoQuery hits size max to give Application uses value of wrong type error

Posted on 2004-04-19
Last Modified: 2012-06-21
I have a sql server table with 52 fields. Using a TAdoQuery talking to SQl Server 2000 I am setting the values using ParamByName. I have found that if I leave all the fields in I get an error on one of the lines such as :

Query.Parameters.ParamByName('MyField').Value := MyField;

the error is :
"Application uses a value of the wrong type for the current operation"
"incorrect syntax near @P38"

If I remove the lines talking to some fields the error goes away. It is not specific to particlar fields only the number. This makes no sense to me as there are 8kb per row allowed in sql server.

None of the fields are blobs, there are 364 kb :
4 uniqueidentifier (16 x 4 = 64)
23 int (23 * 4 = 92)
1 ntext (16)
24 float (24* 8 = 192)

If I remove (not from the database) any 12 of the float value lines it works fine, if I remove 11 of them I get the above error.

Can anyone shed any light on this mystery?

Thanks a lot,

Question by:tomcorcoran
  • 6
  • 5
  • 2
  • +2
LVL 12

Expert Comment

ID: 10863734
it is better to specify the type of the parameters manifestly:

Query.Parameters.ParamByName('IntParam1').AsInteger := IntValue1;
Query.Parameters.ParamByName('StrParam1').AsString := StrValue1;
Query.Parameters.ParamByName('FloatParam1').AsFloat := FloatValue1;


Author Comment

ID: 10864110
Ok, thanks. Could tat account for the error? Tom.
LVL 27

Expert Comment

ID: 10866323
>Could tat account for the error?
no, because ado-parameters do not provide As...-conversions.

try to define the paramtype and datatype of the parameter you want to use

meikl ;-)
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 17

Accepted Solution

geobul earned 100 total points
ID: 10866488

There are problems using many parameters in a query (more than 25 or so, not sure about the exact number). I would create the SQL statement on the fly without using any parameters:

Query.SQL.Add('SELECT * FROM table1 WHERE');
Query.SQL.Add('StringField1 = ' + QuotedStr(StringVariable1));
Query.SQL.Add('AND IntegerFiled1 = ' + IntToStr(IntegerVariable1));
// .. more parameters

And the same way for UPDATE/INSERT statements using FloatToStr for float fields.

Regards, Geo

Expert Comment

by:Amir Azhdari
ID: 10866711
Hi  tomcorcoran , if i don't misunderstood you want to execute a code like this :

procedure TForm1.Button1Click(Sender: TObject);

with adoquery1.parameters do begin
ParamByName('F1').Value := 'uniqueidentifie1';
ParamByName('F2').Value := 'uniqueidentifie2';
ParamByName('F3').Value := 'uniqueidentifie3';
ParamByName('F4').Value := 'uniqueidentifie4';

ParamByName('F5').Value := 123456;
ParamByName('F6').Value := 123456;
ParamByName('F7').Value := 123456;
ParamByName('F8').Value := 123456;
ParamByName('F9').Value := 123456;
ParamByName('F10').Value := 123456;
ParamByName('F11').Value := 123456;
ParamByName('F12').Value := 123456;
ParamByName('F13').Value := 123456;
ParamByName('F14').Value := 123456;
ParamByName('F15').Value := 123456;
ParamByName('F16').Value := 123456;
ParamByName('F17').Value := 123456;
ParamByName('F18').Value := 123456;
ParamByName('F19').Value := 123456;
ParamByName('F20').Value := 123456;
ParamByName('F21').Value := 123456;
ParamByName('F22').Value := 123456;
ParamByName('F23').Value := 123456;
ParamByName('F24').Value := 123456;
ParamByName('F25').Value := 123456;
ParamByName('F26').Value := 123456;
ParamByName('F27').Value := 123456;

ParamByName('F28').Value := 'ntextntextntextn';

ParamByName('F29').Value := 1.5;
ParamByName('F30').Value := 1.5;
ParamByName('F31').Value := 1.5;
ParamByName('F32').Value := 1.5;
ParamByName('F33').Value := 1.5;
ParamByName('F34').Value := 1.5;
ParamByName('F35').Value := 1.5;
ParamByName('F36').Value := 1.5;
ParamByName('F37').Value := 1.5;
ParamByName('F38').Value := 1.5;
ParamByName('F39').Value := 1.5;
ParamByName('F40').Value := 1.5;
ParamByName('F41').Value := 1.5;
ParamByName('F42').Value := 1.5;
ParamByName('F43').Value := 1.5;
ParamByName('F44').Value := 1.5;
ParamByName('F45').Value := 1.5;
ParamByName('F46').Value := 1.5;
ParamByName('F47').Value := 1.5;
ParamByName('F48').Value := 1.5;
ParamByName('F49').Value := 1.5;
ParamByName('F50').Value := 1.5;
ParamByName('F51').Value := 1.5;
ParamByName('F52').Value := 1.5;


and for example a query statemet like this :

insert into yourtable values(:F1,:F2,:F3,:F4,:F5,:F6,:F7,:F8,:F9,:F10,:F11,:F12,:F13,:F14,:F15,:F16,:F17,:F18,:F19,:F20,:F21,:F22,:F23,:F24,:F25,:F26,:F27,:F28,:F29,:F30,:F31,:F32,:F33,:F34,:F35,:F36,:F37,:F38,:F39,:F40,:F41,:F42,:F43,:F44,:F45,:F46,:F47,:F48,:F49,:F50,:F51,:F52);

but i did execute that and didn't get error!

let me know if i do misunderstand somethings



Author Comment

ID: 10867539
Thanks for the comments.

<<try to define the paramtype and datatype of the parameter you want to use>>
Meikl, I do not follow you.

Geo, interesting observation. Is thera any documentation on this problem?

Azhdari, that's right, I get the error on one of the
ParamByName('F??').Value := 1.5;
lines. We even got it in a small test project, so interesting you got no problems.

To get past this we had to split it into 2 tables, insane.

LVL 27

Assisted Solution

kretzschmar earned 75 total points
ID: 10867554
>Meikl, I do not follow you.

ParamByName('F??').ParamType := ptInput;
ParamByName('F??').DataType := ftFloat;
ParamByName('F??').Value := 1.5;

meikl ;-)

Author Comment

ID: 10867875
Thanks Meikl. You think this will stop the error? Any info on why it happens?
LVL 27

Expert Comment

ID: 10867931
>Thanks Meikl. You think this will stop the error?

its just a guess ;-)

>Any info on why it happens?

the datatype-property is set by default to ftUnknown,
which may cause that the variant-conversion fails (depending on the value)

i have done a mistake here
ParamByName('F??').ParamType := ptInput;

should be
ParamByName('F??').Direction := pdInput;

meikl ;-)

Author Comment

ID: 10868044
Ok. What's the default for Direction? Bit of a pain to set them all to pdInput. Thanks, Tom.
LVL 27

Expert Comment

ID: 10868074
>Ok. What's the default for Direction?

the default is pdInput -> there is nothing to do

except you have output-parameters
(guess no in your case),
in this case it must be explicitly set

meikl ;-)

Expert Comment

by:Amir Azhdari
ID: 10869350
tomcorcoran , i got no error for this statement
insert into yourtable values(:F1,:F2,......);

by the way , you don't need to set paramtype and datatype of the parameters manually, Adoquery will manage these
automatically everytime you execute SELECT/INSERT/UPDATE queries, you need to set these parameters just when you want to
create tables

anyway write me the sql text which you are trying to execute and get error


Author Comment

ID: 10869375
Azhdari, others are giving contrary information on setting the datatype...It is something I will try. We are using a framework so it's not so easy to
extract the code. Never had this problem before. Don't know why we hit this max now. It sounds like it is a known problem
but I have yet to see any documentatrion.

LVL 17

Expert Comment

ID: 10876285
>Is thera any documentation on this problem?

I don't know. It's based on one of my colleagues experience and perhaps depends on Delphi and/or MDAC version. I've said 'I would ..' but actually that's what I always do (create SQL statements dynamically in code) except for static SQLs, of course.

Author Comment

ID: 10880850

In a test project I tried setting the datatype for all the parameters and this error still happens. We are using a framework which looks for the
paremeters so not using them is not am option.

 It looks like we will have to leave in the second table solution which is very frustrating. there seems to be some concensus about this being
a known problembut it's strange why there is nothing written about it anywhere.

Does anyone else know anything?

Thanks, Tom.
LVL 27

Expert Comment

ID: 10880966
hmm, bad,

>Application uses a value of the wrong type for the current operation
>incorrect syntax near @P38
points to a conversion or a syntax problem

what is @P38?
are there any db-triggers involved (which may throw this error)?

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mydac connection data base issue 3 158
add combobox item based on numbers 9 144
Working with hours 3 58
MS Access from Delphi 31 32
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question