Trapping Exception using TSQLQuery & params

Question

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 tell which paramByName is causing the error?

Answer 1

The quickest way to find out may be by using dichotomy, which is a fancy name that mean dividing the problem in successive "halves":

• For about half of the ParamByName('...') lines, set the value to '' (or to a single character string, or to 0 if numeric).
• If the exception persists, the problem is in the remaining half (and possibly in some of the parameters that were temporarilly changed as well..) When such is the case set half of the parameters in the suspected half, to a small string / 0 and try again...
• If the exception goes away: the problem comes from the latest "half" you removed. Provide the original parameter value for half of these, and see if the problem comes backs (and if it doesn't, the problem is in the other half etc. etc.)

Answer 2

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.

Answer 3

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)

Answer 4

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.

Answer 5

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?