Trapping Exception using TSQLQuery & params

I am getting a "SQL Server Error: arithmetic exception, numeric overflow, or string truncation" error

here is the code below

AQuery:= TSQLQuery.Create(nil);
with AQuery do
begin
SQLConnection:- AConnection;
 SQL.Text:= 'Insert into.....';
 ParamByName('...').asString:= 'PCT';
 .
 .
 .

 try
  ExecSQL;
 finally
  AQuery.Free;
 end;
end;

I have alot of ParamByName lines, and I can't figure out which one is throwing the exception. I just know its thrown on the ExecSQL line. How can i te开发者_高级运维ll which paramByName is causing the error?

When you have the metadata of the table, check the maximum length of string fields. When debugging, check the length of the strings you feed the parambynames. Also check the type of numeric fields, and make sure you don't exceed a maximum value. (I had this problem once with a string which length exceeded the varchars length in the table, and had this problem with a smallint databasefield that I tried to set to a too high value)

Get the SQL text after param substitution and run it as a query in SQL Server management studio.
You'll be able to debug it from there.

You are trying to insert a string value into a field that is not big enough to hold the value. Check the length of the values you are inserting against the length of the field in the table.

As others have said, it's almost certainly that you are pushing too-large a string into one of your fields. It could also happen with numeric values but it's most likely to be a string.

I'd suggest you temporarily alter each of your ParamByName('').AsString:=blah lines with a text constant, eg;

ParamByName('surname').AsString:='Smith';
ParamByName('firstname').AsString:='John';

etc, and see if you get an error. If it goes through without an error, then your problem is most likely to be that one of your string parameters is too long. Check your table schema and debug the actual strings you are putting into the parameters.

Depending on how much access (and experience) you have with this, you might find it more helpful to turn on the SQL Server logging such that you can see your queries (and the contents of those parameters) when the get processed by the SQL server. This will show you exactly what string and numeric values are actually being given to the server. Which version/edition of SQL Server are you using?