boardtc
asked on
TAdoQuery hits size max to give Application uses value of wrong type error
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.ParamByNa me('MyFiel d').Value := MyField;
the error is :
"Application uses a value of the wrong type for the current operation"
or
"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,
Tom.
Query.Parameters.ParamByNa
the error is :
"Application uses a value of the wrong type for the current operation"
or
"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,
Tom.
ASKER
Ok, thanks. Could tat account for the error? Tom.
>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 ;-)
no, because ado-parameters do not provide As...-conversions.
try to define the paramtype and datatype of the parameter you want to use
meikl ;-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi tomcorcoran , if i don't misunderstood you want to execute a code like this :
procedure TForm1.Button1Click(Sender : TObject);
begin
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;
end;
adoquery1.ExecSQL;
end;
and for example a query statemet like this :
insert into yourtable values(:F1,:F2,:F3,:F4,:F5 ,:F6,:F7,: F8,:F9,:F1 0,:F11,:F1 2,:F13,:F1 4,:F15,:F1 6,:F17,:F1 8,:F19,:F2 0,:F21,:F2 2,:F23,:F2 4,:F25,:F2 6,:F27,:F2 8,:F29,:F3 0,:F31,:F3 2,:F33,:F3 4,:F35,:F3 6,:F37,:F3 8,:F39,:F4 0,:F41,:F4 2,:F43,:F4 4,:F45,:F4 6,:F47,:F4 8,:F49,:F5 0,:F51,:F5 2);
but i did execute that and didn't get error!
let me know if i do misunderstand somethings
Regards
Azhdari
procedure TForm1.Button1Click(Sender
begin
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;
end;
adoquery1.ExecSQL;
end;
and for example a query statemet like this :
insert into yourtable values(:F1,:F2,:F3,:F4,:F5
but i did execute that and didn't get error!
let me know if i do misunderstand somethings
Regards
Azhdari
ASKER
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.
Tom.
<<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.
Tom.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Meikl. You think this will stop the error? Any info on why it happens?
>Thanks Meikl. You think this will stop the error?
its just a guess ;-)
>Any info on why it happens?
specially
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??').ParamTy pe := ptInput;
should be
ParamByName('F??').Directi on := pdInput;
meikl ;-)
its just a guess ;-)
>Any info on why it happens?
specially
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??').ParamTy
should be
ParamByName('F??').Directi
meikl ;-)
ASKER
Ok. What's the default for Direction? Bit of a pain to set them all to pdInput. Thanks, Tom.
>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 ;-)
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 ;-)
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
Regards
Azhdari
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
Regards
Azhdari
ASKER
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.
Tom.
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.
Tom.
>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.
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.
ASKER
Meikl,
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.
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.
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)?
>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)?
Query.Parameters.ParamByNa
//................
Query.Parameters.ParamByNa
//................
Query.Parameters.ParamByNa
emil