SQLite - '' is not a valid floating point value

[oz8hp]

When using the following on SQLite I get the errormessage '' is not a valid floating point value
When running MS Access, MySQL or MS SQL the error isn't there.
What can I do to have this go away?
fldSum is defined as a Float in the SQLite db

curBought := QueryMain.FieldByName('fldbuyer_sum').AsCurrency;
intBuyer := QueryMain.FieldByName('fldbuyer_id').AsInteger;
QueryInput.SQL.Clear;
QueryInput.SQL.Add('SELECT Sum(fldamount) AS fldsum FROM ' + conTable_Payments_Buyer);
QueryInput.SQL.Add(' WHERE fldbuyer_id = :fldbuyer_id');
QueryInput.SQL.Add(' AND fldauction_guid = :fldauction_guid');
QueryInput.ParamByName('fldauction_guid').Value := Settings.Auction_GUID;
QueryInput.ParamByName('fldbuyer_id').Value := intBuyer;

17-03-2010 11:35:36 # '' is not a valid floating point value

[bork]

To reproduce this issue I need a script to create a table for your query. Also I have to know the type of the Settings.Auction_GUID and intBuyer variables.

[oz8hp]

I will create a small file with the indo and get them to you

But I can't upload a file here as far as I can see

[oz8hp]

Try www.hugopedersen.dk/content/files/sqlite.zip

[bork]

Your query returns NULL value:
QueryInput.SQL.Add('SELECT Sum(fldamount) AS fldsum FROM ' + conTable_Payments_Buyer);
QueryInput.SQL.Add(' WHERE fldbuyer_id = :fldbuyer_id');
QueryInput.SQL.Add(' AND fldauction_guid = :fldauction_guid');

NULL cannot be converted to number and you get error "'' is not a valid floating point" value in the line:
curPaid := QueryInput.FieldByName('fldSum').AsCurrency;

You have two way to fix this issue:

1. Fix programm:
if QueryInput.FieldByName('fldSum').IsNull then
curPaid := 0
else
curPaid := QueryInput.FieldByName('fldSum').AsCurrency;

2. Fix your query to return zero instead of NULL value.

[oz8hp]

OK - thanks

I think I will have to add the fix to the program. The query should not return null since the amount is default 0.

Thanks again