MySQL writing into BLOB error: ""is invalid float
lazarus / 16-Databases

----------------------------------------------------------------------
syalyshev · 2017-10-13 10:33

Could anyone kindly help me.
When attempting to insert string variable into blob field of MySQL
table the error message ' ""is invalid float' is obtained. Query
formed is like this
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ... 
SaveQuery.SQL.Add(''''+F_Centre.Text+''','); 
SaveQuery.SQL.Add(''''+BW.Text+''','); 
SaveQuery.SQL.Add(''''+st_co+''','); 
SaveQuery.SQL.Add(''''+st_cx+''')');    SaveQuery.ExecSQL; 
SQLTransaction.Commit;...   st_co and st_cx are strings inserted into
blobs.
All records in the table are written correctly. Is it possible not to
show such a message?

----------------------------------------------------------------------
rvk · 2017-10-13 11:18

Quote from: syalyshev on October 13, 2017, 12:33:52 pmst_co and st_cx
are strings inserted into blobs.
All records in the table are written correctly. Is it possible not to
show such a message?
Yes, don't insert text when your database/SQL expects a float value.

It's impossible to see because you didn't include the whole SQL
statement but in the fields-selection you are trying to insert is a
float and you are trying to put text into that (which results in this
message).

Check the order of the fields of your entire SQL-statement or put it
here with the complete table description.

----------------------------------------------------------------------
syalyshev · 2017-10-13 12:34

Thanks a lot for your reply!
Well, the whole SQL Statement (if nesessary) looks like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ... 
SaveQuery.SQL.Add('INSERT INTO  work_sp');  SaveQuery.SQL.Add('VALUES
(last_insert_id(),'''+ES_Index.Text+''',');                          
       {index}  SaveQuery.SQL.Add(''''+FormatDateTime('yyy.mm.dd/hh:mm
:ss',StrToDateTime(Date_Time.Text))+''',');       {date} 
SaveQuery.SQL.Add(''''+customer_es_name.Text+''',');                 
                                 {cust_es} 
SaveQuery.SQL.Add(''''+ASMI_Operator.Text+''',');                    
                                 {ASMI_Operator} 
SaveQuery.SQL.Add(''''+operator_phone.Text+''',');                   
                                 {operator_phone} 
SaveQuery.SQL.Add(''''+srb_doc.Text+''',');                          
                                 {srb_doc} 
SaveQuery.SQL.Add(''''+Satellite.Text+''',');                        
                                 {Satellite} 
SaveQuery.SQL.Add(''''+SatLongitude.Text+''',');                     
                                 {Sat Longitude} 
SaveQuery.SQL.Add(''''+ChooseTP.Text+''',');                         
                                 {co_tp_name} 
SaveQuery.SQL.Add(''''+Co_Up_pol.Text+''',');                        
                                 {co_up_pol} 
SaveQuery.SQL.Add(''''+CX_tp.Text+''',');                            
                                 {cx_tp_name} 
SaveQuery.SQL.Add(''''+Cx_Up_pol.Text+''',');                        
                                 {cx_up_pol} 
SaveQuery.SQL.Add(''''+Station_Own.Text+''',');                      
                                 {es_owner} 
SaveQuery.SQL.Add(''''+User_Operator.Text+''',');                    
                                 {contact_person} 
SaveQuery.SQL.Add(''''+User_Cont_Phone.Text+''',');                  
                                 {contact_phone} 
SaveQuery.SQL.Add(''''+ES_location.Text+''',');                      
                                 {es_location} 
SaveQuery.SQL.Add(''''+ES_latitude.Text+''',');                      
                                 {es_latitude} 
SaveQuery.SQL.Add(''''+ES_longitude.Text+''',');                     
                                 {es_longitude} 
SaveQuery.SQL.Add(''''+Ant_Diameter.Text+''',');                     
                                 {es_ant_diam} 
SaveQuery.SQL.Add(''''+Ref_Station.Text+''',');                      
                                 {ref_station} 
SaveQuery.SQL.Add(''''+F_up.Text+''',');                             
                                 {frq_isol_up} 
SaveQuery.SQL.Add(''''+F_dn.Text+''',');                             
                                 {frq_isol_dn} 
SaveQuery.SQL.Add(''''+CO_EIRP_f.Text+''',');                        
                                 {co_eirp_calculated} 
SaveQuery.SQL.Add(''''+CPI_es_f.Text+''',');                         
                                 {cpi_es_calculated} 
SaveQuery.SQL.Add(''''+sp+''',');                                    
                                 {dir_spectrum} 
SaveQuery.SQL.Add(''''+frq+''',');                                   
                                 {dir_frq} 
SaveQuery.SQL.Add(''''+eirp+''',');                                  
                                 {dir_frq} 
SaveQuery.SQL.Add(''''+'0'+''',');                                   
                                 {Comment} 
SaveQuery.SQL.Add(''''+Pot_f.Text+''',');                            
                                 {Pot} 
SaveQuery.SQL.Add(''''+Pxt_f.Text+''',');                            
                                 {Pxt} 
SaveQuery.SQL.Add(''''+Por_f.Text+''',');                            
                                 {Por} 
SaveQuery.SQL.Add(''''+Pxr_f.Text+''',');                            
                                 {Pxr} 
SaveQuery.SQL.Add(''''+G_CO_Max.Text+''',');                         
                                 {G_CO_Max} 
SaveQuery.SQL.Add(''''+Got_f.Text+''',');                            
                                 {Got} 
SaveQuery.SQL.Add(''''+Gor_f.Text+''',');                            
                                 {Gor} 
SaveQuery.SQL.Add(''''+Gxt_f.Text+''',');                            
                                 {Gxt} 
SaveQuery.SQL.Add(''''+Gxr_f.Text+''',');                            
                                 {Gxr} 
SaveQuery.SQL.Add(''''+Gxtx_f.Text+''',');                           
                                 {Gxtx} 
SaveQuery.SQL.Add(''''+CPIsum_f.Text+''',');                         
                                 {CPIsum} 
SaveQuery.SQL.Add(''''+CPIsat_f.Text+''',');                         
                                 {CPIsat} 
SaveQuery.SQL.Add(''''+descr_cust.Text+''',');                       
                                 {cust_full} 
SaveQuery.SQL.Add(''''+City_Full_f.Text+''',');                      
                                 {City_Full} 
SaveQuery.SQL.Add(''''+TimeToStr(BegTime.Time)+''',');               
                                 {BegTime} 
SaveQuery.SQL.Add(''''+TimeToStr(EndTime.Time)+''',');               
                                 {EndTime} 
SaveQuery.SQL.Add(''''+SpentTime.Text+''',');                        
                                 {SpentTime} 
SaveQuery.SQL.Add(''''+F_Centre.Text+''',');                         
                                 {central FRQ} 
SaveQuery.SQL.Add(''''+BW.Text+''',');                               
                                 {Band Width} 
SaveQuery.SQL.Add(''''+st_co+''',');                                 
                                 {Spectrum values CO} 
SaveQuery.SQL.Add(''''+st_cx+''')');                                 
                                 {Spectrum values CX}  
SaveQuery.ExecSQL;  SQLTransaction.Commit;...  Table structure as per
attached table_structure.jpg.
I have just changed types of fields st_co and ct_cx from MEDIUMBLOB to
MEDIUMTEXT for to confirm that string was expected, not float! The
result was the same
What was my mistake? The message '""is invalid float' though not
having influence on the result of recording to database annoys very
much!

----------------------------------------------------------------------
rvk · 2017-10-13 12:47

Quote from: syalyshev on October 13, 2017, 02:34:06 pmTable structure
as per attached table_structure.jpg.
I have just changed types of fields st_co and ct_cx from MEDIUMBLOB to
MEDIUMTEXT for to confirm that string was expected, not float! The
result was the same
What was my mistake? The message '""is invalid float' though not
having influence on the result of recording to database annoys very
much!
The fields st_co and ct_cx are not your problem.

If you look at the following fields... you can see you pass them as
TEXT (with quotes around it). But according to your DB-design these
should be DECIMAL(8,2)s. So you need to pass them as values/valid
floats (so remove the extra '). And if one of these field.Text is
empty string you should convert it to 0 or NULL. A '' can't be stored
in a DECIMAL(8,2).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SaveQuery.SQL.Add(''''+Pot_f.Text+''',');                            
                                 {Pot} 
SaveQuery.SQL.Add(''''+Pxt_f.Text+''',');                            
                                 {Pxt} 
SaveQuery.SQL.Add(''''+Por_f.Text+''',');                            
                                 {Por} 
SaveQuery.SQL.Add(''''+Pxr_f.Text+''',');                            
                                 {Pxr} 
SaveQuery.SQL.Add(''''+G_CO_Max.Text+''',');                         
                                 {G_CO_Max} 
SaveQuery.SQL.Add(''''+Got_f.Text+''',');                            
                                 {Got} 
SaveQuery.SQL.Add(''''+Gor_f.Text+''',');                            
                                 {Gor} 
SaveQuery.SQL.Add(''''+Gxt_f.Text+''',');                            
                                 {Gxt} 
SaveQuery.SQL.Add(''''+Gxr_f.Text+''',');                            
                                 {Gxr} 
SaveQuery.SQL.Add(''''+Gxtx_f.Text+''',');                           
                                 {Gxtx} 
SaveQuery.SQL.Add(''''+CPIsum_f.Text+''',');                         
                                 {CPIsum} 
SaveQuery.SQL.Add(''''+CPIsat_f.Text+''',');                         
                                 {CPIsat}  
SaveQuery.SQL.Add(''''+F_Centre.Text+''',');                         
                                 {central FRQ} 
SaveQuery.SQL.Add(''''+BW.Text+''',');                               
                                 {Band Width}

----------------------------------------------------------------------
syalyshev · 2017-10-13 13:19

Thanks again for your reply!
I followed your recommendations and obtained no effect. No change.
One thing puzzles... Until I addded fields: fcenter, band (float!) and
spect_co, spect_cx (blob or text!), everything worked, records were
written without rough. Adding these fields changed the structure of
the table dramatically!

Thanks a lot anyway! Going to think over the issue further!

----------------------------------------------------------------------
rvk · 2017-10-13 13:34

What are st_co and st_cx for strings. Are they multiline strings? In
that case you can't add them with just the INSERT statement and you
need to work with parameters (which is better anyway).

I would change the whole SaveQuery.SQL.Text to use parameters.

Something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SaveQuery.SQL.Add('INSERT INTO work_sp');  SaveQuery.SQL.Add('VALUES
(last_insert_id(),:ES_Index, :fdate, :customer_es_name,
:ASMI_Operator, :operator_phone,');  SaveQuery.SQL.Add(':srb_doc,
:Satellite, :SatLongitude, :ChooseTP, :Co_Up_pol, :CX_tp, :Cx_Up_pol,
:Station_Own,');  SaveQuery.SQL.Add(':User_Operator, :User_Cont_Phone,
:ES_location, :ES_latitude, :ES_longitude, :Ant_Diameter,'); 
SaveQuery.SQL.Add(':Ref_Station, :F_up, :F_dn, :CO_EIRP_f, :CPI_es_f,
:sp, :frq, :eirp, 0, :Pot_f, :Pxt_f, :Por_f,'); 
SaveQuery.SQL.Add(':Pxr_f, :G_CO_Max, :Got_f, :Gor_f, :Gxt_f, :Gxr_f,
:Gxtx_f, :CPIsum_f, :CPIsat_f, :descr_cust'); 
SaveQuery.SQL.Add(':City_Full_f, :begtime, :endTime, :SpentTime,
:F_Centre, :BW, :st_co, :st_cx);');After that you can do:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.ParamByName('ES_Index').asInteger :=
IntToStrDef(ES_Index.Text,
0);SaveQuery.ParamByName('fdate').asDateTime := Date_Time.Value; //
depending if Date_Time has a Date property or is just
textSaveQuery.ParamByName('customer_es_name').asString :=
customer_es_name.Text;//...SaveQuery.ParamByName('Pot_f').asFloat :=
StrToFloat(Pot_f.Text); // or StrToFloatDef if Text can be empty or
invalid)//...SaveQuery.ParamByName('st_co').asString :=
st_co;SaveQuery.ParamByName('st_cx').asString :=
st_cx;SaveQuery.ExecSQL
Another option could be:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.SQL.Add('SELECT * FROM
work_sp');SaveQuery.Insert;SaveQuery.FieldByName('ES_Index').asInteger
:= IntToStrDef(ES_Index.Text, 0);//... etcSaveQuery.Post;

----------------------------------------------------------------------
syalyshev · 2017-10-13 13:41

Many thanks for the idea! I'd never thought of such thing as
parameter. I'll try it but not today!
Thanks for your big work for me!

----------------------------------------------------------------------
syalyshev · 2017-10-18 09:09

Followed all your variants. Result was the same
Many thanks anyway!
Will think further...

----------------------------------------------------------------------
rvk · 2017-10-18 09:19

If you used the parameter method you can exactly pinpoint where the
error-message is coming from.

If you still get the  ""is invalid float' you are still trying to put
an empty string into a float somewhere, which isn't possible.

If you used StrToFloat(Pot_f.Text); and Pot_f.Text is an empty string,
you should use StrToFloatDef(Pot_f.Text, 0); so that an empty string
(or invalid float) is automatically converted to 0.

----------------------------------------------------------------------
syalyshev · 2017-10-23 10:36

Trying to use parameter I faced the following error message:
"MySQL50Connection: error exequting query: Column count doesn't match
value count at row 1." I think that the cause is last_insert_id()...
But what to do with that!?

----------------------------------------------------------------------
rvk · 2017-10-23 10:39

You've only showed us half your code.
And you also didn't show your complete table definition.
So we can't know where the fault is.

But you need to count the exact fields (and also correct order !!!)
and match it with the insert statement.

The insert statement I gave with parameters was just a guess of what
the fields are.

In the insert statement the fields you are going to update are not
mentioned so ALL fields need to be updated. If you don't want that you
can also specify which fields you want to update.
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
work_sp (field1, field2) VALUES (:field1, :field2)Otherwise you need
to give ALL the fields:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
work_sp VALUES (:field1, :field2, :field3 etc etc etc etc etc etc
etc)And you can't mis one field or give the fields out of order.

----------------------------------------------------------------------
syalyshev · 2017-10-23 10:57

Dear rvk, thanks a lot again!
The fields count and order are correct! My current code looks like
this
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.SQL.Add('INSERT INTO work_sp');     
SaveQuery.SQL.Add('VALUES (last_insert_id(),:ES_Index, :fdate,
:customer_es_name, :ASMI_Operator, :operator_phone,');  {6}     
SaveQuery.SQL.Add(':srb_doc, :Satellite, :SatLongitude, :ChooseTP,
:Co_Up_pol, :CX_tp, :Cx_Up_pol, :Station_Own,'); {8}     
SaveQuery.SQL.Add(':User_Operator, :User_Cont_Phone, :ES_location,
:ES_latitude, :ES_longitude, :Ant_Diameter,'); {6}     
SaveQuery.SQL.Add(':Ref_Station, :F_up, :F_dn, :CO_EIRP_f, :CPI_es_f,
:sp, :frq, :eirp, :comment, :Pot_f, :Pxt_f, :Por_f,'); {12}     
SaveQuery.SQL.Add(':Pxr_f, :G_CO_Max, :Got_f, :Gor_f, :Gxt_f, :Gxr_f,
:Gxtx_f, :CPIsum_f, :CPIsat_f, :descr_cust'); {10}     
SaveQuery.SQL.Add(':City_Full_f, :begtime, :endTime, :SpentTime,
:F_Centre, :BW, :st_co, :st_cx);');
{8} SaveQuery.ParamByName('ES_Index').asString :=
ES_Index.Text;SaveQuery.ParamByName('fdate').asDateTime :=
StrToDateTime(Date_Time.Text); // depending if Date_Time has a Date
property or is just
textSaveQuery.ParamByName('customer_es_name').asString := customer_es_
name.Text;SaveQuery.ParamByName('ASMI_Operator').asString:=ASMI_Operat
or.Text;SaveQuery.ParamByName('operator_phone').asString:=operator_pho
ne.Text;                   {5}SaveQuery.ParamByName('srb_doc').asStrin
g:=srb_doc.Text;SaveQuery.ParamByName('Satellite').asString:=Satellite
.Text;SaveQuery.ParamByName('SatLongitude').asFloat:=StrToFloatDef(Sat
Longitude.Text,12);SaveQuery.ParamByName('ChooseTP').asString:=ChooseT
P.Text;SaveQuery.ParamByName('Co_Up_Pol').asString:=Co_Up_pol.Text;Sav
eQuery.ParamByName('CX_tp').asString:=CX_tp.Text;SaveQuery.ParamByName
('Cx_Up_pol').asString:=Cx_Up_pol.Text;SaveQuery.ParamByName('Station_
Own').asString:=Station_Own.Text;                         {8}SaveQuery
.ParamByName('User_Operator').asString:=User_Operator.Text;SaveQuery.P
aramByName('User_Cont_Phone').asString:=User_Cont_Phone.Text;SaveQuery
.ParamByName('ES_location').asString:=ES_location.Text;SaveQuery.Param
ByName('ES_Latitude').asFloat:=StrToFloatDef(ES_latitude.Text,12);Save
Query.ParamByName('ES_Longitude').asFloat:=StrToFloatDef(ES_longitude.
Text,12);SaveQuery.ParamByName('Ant_Diameter').asFloat:=StrToFloatDef(
Ant_Diameter.Text,12);      {6}SaveQuery.ParamByName('Ref_Station').as
String:=Ref_Station.Text;SaveQuery.ParamByName('F_up').asFloat:=StrToF
loatDef(F_up.Text,12);SaveQuery.ParamByName('F_dn').asFloat:=StrToFloa
tDef(F_dn.Text,12);SaveQuery.ParamByName('CO_EIRP_f').asFloat:=StrToFl
oatDef(CO_EIRP_f.Text,12);SaveQuery.ParamByName('CPI_es_f').asFloat:=S
trToFloatDef(CPI_es_f.Text,12);SaveQuery.ParamByName('sp').asString:=s
p;SaveQuery.ParamByName('frq').asString:=frq;SaveQuery.ParamByName('ei
rp').asString:=eirp;SaveQuery.ParamByName('comment').asString:=Comment
.Text;SaveQuery.ParamByName('Pot_f').asFloat :=
StrToFloatDef(Pot_f.Text,12); // or StrToFloatDef if Text can be empty
or invalid)SaveQuery.ParamByName('Pxt_f').asFloat :=
StrToFloatDef(Pxt_f.Text,12);SaveQuery.ParamByName('Por_f').asFloat :=
StrToFloatDef(Por_f.Text,12);                
 {12}SaveQuery.ParamByName('Pxr_f').asFloat :=
StrToFloatDef(Pxr_f.Text,12);SaveQuery.ParamByName('G_CO_Max').asFloat
:=
StrToFloatDef(G_CO_Max.Text,12);SaveQuery.ParamByName('Got_f').asFloat
:= StrToFloatDef(Got_f.Text,12);SaveQuery.ParamByName('Gor_f').asFloat
:= StrToFloatDef(Gor_f.Text,12);SaveQuery.ParamByName('Gxt_f').asFloat
:= StrToFloatDef(Gxt_f.Text,12);SaveQuery.ParamByName('Gxr_f').asFloat
:=
StrToFloatDef(Gxr_f.Text,12);SaveQuery.ParamByName('Gxtx_f').asFloat
:= StrToFloatDef(Gxtx_f.Text,12);SaveQuery.ParamByName('CPIsum_f').asF
loat := StrToFloatDef(CPIsum_f.Text,12);SaveQuery.ParamByName('CPIsat_
f').asFloat := StrToFloatDef(CPIsat_f.Text,12);SaveQuery.ParamByName('
descr_cust').asString:=descr_cust.Text;                           {10}
SaveQuery.ParamByName('City_Full_f').asString:=City_Full_f.Text;SaveQu
ery.ParamByName('begtime').asTime:=BegTime.Time;SaveQuery.ParamByName(
'endTime').asTime:=EndTime.Time;SaveQuery.ParamByName('SpentTime').asF
loat:=StrToFloatDef(SpentTime.Text,12);SaveQuery.ParamByName('F_Centre
').asFloat:=StrToFloatDef(F_Centre.Text,12);SaveQuery.ParamByName('BW'
).asFloat:=StrToFloatDef(BW.Text,12);SaveQuery.ParamByName('st_co').as
String := st_co;SaveQuery.ParamByName('st_cx').asString := st_cx;    
                                
{8} SaveQuery.ExecSQL;SQLTransaction.Commit;

----------------------------------------------------------------------
rvk · 2017-10-23 11:02

Quote from: syalyshev on October 23, 2017, 12:57:48 pmThe fields count
and order are correct! My current code looks like this
If I lap them over each other it should be ok (see below).

But you STILL haven't given the complete DLL (description) of the
table.
Could you extract the DLL for that table work_sp?
It will also give you the all fields in the correct order which you
should use.

Otherwise you would need to restructure the INSERT sql to specify the
fields you update like I showed in the previous post.
i.e.
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
work_sp (field1, field2) VALUES (:field1, :field2)
Code: [Select]last_insert_id(),
:ES_Index,                     
 SaveQuery.ParamByName('ES_Index').asString := ES_Index.Text;
:fdate,                         
SaveQuery.ParamByName('fdate').asDateTime :=
StrToDateTime(Date_Time.Text); // depending if Date_Time has a Date
property or is just text
:customer_es_name,             
 SaveQuery.ParamByName('customer_es_name').asString :=
customer_es_name.Text;
:ASMI_Operator,                 
SaveQuery.ParamByName('ASMI_Operator').asString:=ASMI_Operator.Text;
:operator_phone,');  {6}         SaveQuery.ParamByName('operator_phone
').asString:=operator_phone.Text;                   {5}
:srb_doc,                       
SaveQuery.ParamByName('srb_doc').asString:=srb_doc.Text;
:Satellite,                     
SaveQuery.ParamByName('Satellite').asString:=Satellite.Text;
:SatLongitude,                   SaveQuery.ParamByName('SatLongitude')
.asFloat:=StrToFloatDef(SatLongitude.Text,12);
:ChooseTP,                     
 SaveQuery.ParamByName('ChooseTP').asString:=ChooseTP.Text;
:Co_Up_pol,                     
SaveQuery.ParamByName('Co_Up_Pol').asString:=Co_Up_pol.Text;
:CX_tp,                         
SaveQuery.ParamByName('CX_tp').asString:=CX_tp.Text;
:Cx_Up_pol,                     
SaveQuery.ParamByName('Cx_Up_pol').asString:=Cx_Up_pol.Text;
:Station_Own,'); {8}           
 SaveQuery.ParamByName('Station_Own').asString:=Station_Own.Text;    
                   {8}
:User_Operator,                 
SaveQuery.ParamByName('User_Operator').asString:=User_Operator.Text;
:User_Cont_Phone,                SaveQuery.ParamByName('User_Cont_Phon
e').asString:=User_Cont_Phone.Text;
:ES_location,                   
SaveQuery.ParamByName('ES_location').asString:=ES_location.Text;
:ES_latitude,                    SaveQuery.ParamByName('ES_Latitude').
asFloat:=StrToFloatDef(ES_latitude.Text,12);
:ES_longitude,                   SaveQuery.ParamByName('ES_Longitude')
.asFloat:=StrToFloatDef(ES_longitude.Text,12);
:Ant_Diameter,'); {6}            SaveQuery.ParamByName('Ant_Diameter')
.asFloat:=StrToFloatDef(Ant_Diameter.Text,12);      {6}
:Ref_Station,                   
SaveQuery.ParamByName('Ref_Station').asString:=Ref_Station.Text;
:F_up,                         
 SaveQuery.ParamByName('F_up').asFloat:=StrToFloatDef(F_up.Text,12);
:F_dn,                         
 SaveQuery.ParamByName('F_dn').asFloat:=StrToFloatDef(F_dn.Text,12);
:CO_EIRP_f,                      SaveQuery.ParamByName('CO_EIRP_f').as
Float:=StrToFloatDef(CO_EIRP_f.Text,12);
:CPI_es_f,                       SaveQuery.ParamByName('CPI_es_f').asF
loat:=StrToFloatDef(CPI_es_f.Text,12);
:sp,                           
 SaveQuery.ParamByName('sp').asString:=sp;
:frq,                           
SaveQuery.ParamByName('frq').asString:=frq;
:eirp,                         
 SaveQuery.ParamByName('eirp').asString:=eirp;
:comment,                       
SaveQuery.ParamByName('comment').asString:=Comment.Text;
:Pot_f,                         
SaveQuery.ParamByName('Pot_f').asFloat :=
StrToFloatDef(Pot_f.Text,12); // or StrToFloatDef if Text can be empty
or invalid)
:Pxt_f,                         
SaveQuery.ParamByName('Pxt_f').asFloat :=
StrToFloatDef(Pxt_f.Text,12);
:Por_f,'); {12}                 
SaveQuery.ParamByName('Por_f').asFloat :=
StrToFloatDef(Por_f.Text,12);                  {12}
:Pxr_f,                         
SaveQuery.ParamByName('Pxr_f').asFloat :=
StrToFloatDef(Pxr_f.Text,12);
:G_CO_Max,                     
 SaveQuery.ParamByName('G_CO_Max').asFloat :=
StrToFloatDef(G_CO_Max.Text,12);
:Got_f,                         
SaveQuery.ParamByName('Got_f').asFloat :=
StrToFloatDef(Got_f.Text,12);
:Gor_f,                         
SaveQuery.ParamByName('Gor_f').asFloat :=
StrToFloatDef(Gor_f.Text,12);
:Gxt_f,                         
SaveQuery.ParamByName('Gxt_f').asFloat :=
StrToFloatDef(Gxt_f.Text,12);
:Gxr_f,                         
SaveQuery.ParamByName('Gxr_f').asFloat :=
StrToFloatDef(Gxr_f.Text,12);
:Gxtx_f,                       
 SaveQuery.ParamByName('Gxtx_f').asFloat :=
StrToFloatDef(Gxtx_f.Text,12);
:CPIsum_f,                     
 SaveQuery.ParamByName('CPIsum_f').asFloat :=
StrToFloatDef(CPIsum_f.Text,12);
:CPIsat_f,                     
 SaveQuery.ParamByName('CPIsat_f').asFloat :=
StrToFloatDef(CPIsat_f.Text,12);
:descr_cust'); {10}             
SaveQuery.ParamByName('descr_cust').asString:=descr_cust.Text;       
                  {10}
:City_Full_f,                   
SaveQuery.ParamByName('City_Full_f').asString:=City_Full_f.Text;
:begtime,                       
SaveQuery.ParamByName('begtime').asTime:=BegTime.Time;
:endTime,                       
SaveQuery.ParamByName('endTime').asTime:=EndTime.Time;
:SpentTime,                      SaveQuery.ParamByName('SpentTime').as
Float:=StrToFloatDef(SpentTime.Text,12);
:F_Centre,                       SaveQuery.ParamByName('F_Centre').asF
loat:=StrToFloatDef(F_Centre.Text,12);
:BW,                           
 SaveQuery.ParamByName('BW').asFloat:=StrToFloatDef(BW.Text,12);
:st_co,                         
SaveQuery.ParamByName('st_co').asString := st_co;
:st_cx                         
 SaveQuery.ParamByName('st_cx').asString := st_cx;                   
                  {8}
I take it there is an index_field above ES_Index ?? Where you put the
last_insert_id in ??

----------------------------------------------------------------------
syalyshev · 2017-10-23 14:27

The work_sp table structure looks like this:
+-------------------------------+------------------+-------+-------+--
---------------------------------+-------------+
| Field                        | Type           | Null  | Key |
Default                         | Extra      |
+------------------------------+-------------------+-------+-------+--
---------------------------------+-------------+
| es_id                      | int(11)           | NO   | PRI | NULL 
                       | auto_increment |
| idx                          | varchar(45)   | NO   | PRI | NULL   
                     |                |
| date                        | datetime       | YES  |     | NULL   
                       |                |
| cust_station_name   | varchar(40)    | YES  |     | NULL           
               |                |
| operator                  | varchar(20)    | YES  |     | NULL     
                     |                |
| operator_phone       | varchar(5)     | YES  |     | NULL          
                |                |
| srb_doc                  | varchar(30)    | YES  |     | NULL      
                    |                |
| satellite                   | varchar(15)    | YES  |     | NULL   
                       |                |
| satellite_longitude    | decimal(6,2)  | YES  |     | NULL         
                |                |
| co_tp_name            | varchar(10)    | YES  |     | NULL         
                 |                |
| co_tp_pol                | varchar(10)   | YES  |     | NULL       
                   |                |
| cx_tp_name            | varchar(10)    | YES  |     | NULL         
                 |                |
| cx_tp_pol                | varchar(10)   | YES  |     | NULL       
                   |                |
| es_owner                | varchar(30)   | YES  |     | NULL        
                  |                |
| contact_person        | varchar(30)   | YES  |     | NULL          
                |                |
| contact_phone         | varchar(35)   | YES  |     | NULL          
                |                |
| es_location              | varchar(30)   | YES  |     | NULL       
                   |                |
| es_latitude               | decimal(10,3) | YES  |     | NULL      
                    |                |
| es_longitude            | decimal(10,3) | YES  |     | NULL        
                  |                |
| es_ant_diam            | decimal(6,2)  | YES  |     | NULL         
                 |                |
| ref_station                | varchar(20)   | YES  |     | NULL     
                     |                |
| frq_isol_up              | decimal(11,4) | YES  |     | NULL       
                   |                |
| frq_isol_dn              | decimal(11,4) | YES  |     | NULL       
                   |                |
| co_eirp_calculated  | decimal(10,2) | YES  |     | NULL            
              |                |
| cpi_es_calculated   | decimal(10,2) | YES  |     | NULL            
              |                |
| dir_spectrum           | varchar(120)  | YES  |     | spectrum     
                |                |
| dir_frq                     | varchar(120)  | YES  |     | frq     
                          |                |
| dir_eirp                   | varchar(120)  | YES  |     | eirp     
                        |                |
| comment                 | varchar(200)  | YES  |     | NULL        
                  |                |
| Pot                         | decimal(8,2)  | YES  |     | NULL    
                      |                |
| Pxt                         | decimal(8,2)  | YES  |     | NULL    
                      |                |
| Por                        | decimal(8,2)  | YES  |     | NULL     
                     |                |
| Pxr                        | decimal(8,2)  | YES  |     | NULL     
                     |                |
| G_CO_Max            | decimal(8,2)  | YES  |     | NULL            
              |                |
| Got                        | decimal(8,2)  | YES  |     | NULL     
                     |                |
| Gor                        | decimal(8,2)  | YES  |     | NULL     
                     |                |
| Gxt                        | decimal(8,2)  | YES  |     | NULL     
                     |                |
| Gxr                        | decimal(8,2)  | YES  |     | NULL     
                     |                |
| Gxtx                      | decimal(8,2)  | YES  |     | NULL      
                    |                |
| CPIsum                 | decimal(8,2)  | YES  |     | NULL         
                 |                |
| CPIsat                   | decimal(8,2)  | YES  |     | NULL       
                   |                |
| cust_fullname         | varchar(150)  | YES  |     | NULL          
                |                |
| city_full                  | varchar(60)   | YES  |     | NULL     
                     |                |
| t_strt                      | varchar(20)   | YES  |     | NULL    
                      |                |
| t_end                    | varchar(20)   | YES  |     | NULL       
                   |                |
| SpentTime             | varchar(15)   | YES  |     | NULL          
                |                |
| fcenter                   | decimal(15,6) | YES  |     | NULL      
                    |                |
| band                     | decimal(15,6) | YES  |     | NULL       
                   |                |
| spect_co               | mediumtext    | YES  |     | NULL         
                 |                |
| spect_cx               | mediumtext    | YES  |     | NULL         
                 |                |
+---------------------+---------------+------+-----+------------------
---------------+----------------+

----------------------------------------------------------------------
syalyshev · 2017-10-23 14:33

This is index:
| es_id                      | int(11)           | NO   | PRI | NULL 
                       | auto_increment |
| idx                          | varchar(45)   | NO   | PRI | NULL   
                     |                           |
last_insert_id() applied to es_id - primary key, auto_increment
idx corresponds to ES_Index.

----------------------------------------------------------------------
rvk · 2017-10-23 14:42

Yes, and according to the rest, the columns should match:
The INSERT SQL had 50 fields, and the table also has 50 fields.
So they should match.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
last_insert_id(),        | es_id              :ES_Index,             
| idx                      
 SaveQuery.ParamByName('ES_Index').asString := ES_Index.Text;:fdate, 
               | date                      
SaveQuery.ParamByName('fdate').asDateTime :=
StrToDateTime(Date_Time.Text);:customer_es_name,       |
cust_station_name        
 SaveQuery.ParamByName('customer_es_name').asString :=
customer_es_name.Text;:ASMI_Operator,          | operator            
     SaveQuery.ParamByName('ASMI_Operator').asString:=ASMI_Operator.Te
xt;:operator_phone,         | operator_phone             SaveQuery.Par
amByName('operator_phone').asString:=operator_phone.Text;:srb_doc,   
           | srb_doc                  
 SaveQuery.ParamByName('srb_doc').asString:=srb_doc.Text;:Satellite, 
           | satellite                  SaveQuery.ParamByName('Satelli
te').asString:=Satellite.Text;:SatLongitude,           |
satellite_longitude        SaveQuery.ParamByName('SatLongitude').asFlo
at:=StrToFloatDef(SatLongitude.Text,12);:ChooseTP,               |
co_tp_name                
SaveQuery.ParamByName('ChooseTP').asString:=ChooseTP.Text;:Co_Up_pol,
            | co_tp_pol                
 SaveQuery.ParamByName('Co_Up_Pol').asString:=Co_Up_pol.Text;:CX_tp, 
               | cx_tp_name                
SaveQuery.ParamByName('CX_tp').asString:=CX_tp.Text;:Cx_Up_pol,      
      | cx_tp_pol                  SaveQuery.ParamByName('Cx_Up_pol').
asString:=Cx_Up_pol.Text;:Station_Own,            | es_owner         
        SaveQuery.ParamByName('Station_Own').asString:=Station_Own.Tex
t;:User_Operator,          | contact_person             SaveQuery.Para
mByName('User_Operator').asString:=User_Operator.Text;:User_Cont_Phone
,        | contact_phone              SaveQuery.ParamByName('User_Cont
_Phone').asString:=User_Cont_Phone.Text;:ES_location,            |
es_location                SaveQuery.ParamByName('ES_location').asStri
ng:=ES_location.Text;:ES_latitude,            | es_latitude          
    SaveQuery.ParamByName('ES_Latitude').asFloat:=StrToFloatDef(ES_lat
itude.Text,12);:ES_longitude,           | es_longitude               S
aveQuery.ParamByName('ES_Longitude').asFloat:=StrToFloatDef(ES_longitu
de.Text,12);:Ant_Diameter,           | es_ant_diam                Save
Query.ParamByName('Ant_Diameter').asFloat:=StrToFloatDef(Ant_Diameter.
Text,12);:Ref_Station,            | ref_station                SaveQue
ry.ParamByName('Ref_Station').asString:=Ref_Station.Text;:F_up,      
           | frq_isol_up                SaveQuery.ParamByName('F_up').
asFloat:=StrToFloatDef(F_up.Text,12);:F_dn,                   |
frq_isol_dn                SaveQuery.ParamByName('F_dn').asFloat:=StrT
oFloatDef(F_dn.Text,12);:CO_EIRP_f,              | co_eirp_calculated
       SaveQuery.ParamByName('CO_EIRP_f').asFloat:=StrToFloatDef(CO_EI
RP_f.Text,12);:CPI_es_f,               | cpi_es_calculated          Sa
veQuery.ParamByName('CPI_es_f').asFloat:=StrToFloatDef(CPI_es_f.Text,1
2);:sp,                     | dir_spectrum              
SaveQuery.ParamByName('sp').asString:=sp;:frq,                    |
dir_frq                  
 SaveQuery.ParamByName('frq').asString:=frq;:eirp,                   |
dir_eirp                  
SaveQuery.ParamByName('eirp').asString:=eirp;:comment,              
 | comment                  
 SaveQuery.ParamByName('comment').asString:=Comment.Text;:Pot_f,     
           | Pot                      
 SaveQuery.ParamByName('Pot_f').asFloat :=
StrToFloatDef(Pot_f.Text,12); // or StrToFloatDef if Text can be empty
or invalid):Pxt_f,                  | Pxt                      
 SaveQuery.ParamByName('Pxt_f').asFloat :=
StrToFloatDef(Pxt_f.Text,12);:Por_f,                  | Por          
            SaveQuery.ParamByName('Por_f').asFloat :=
StrToFloatDef(Por_f.Text,12);:Pxr_f,                  | Pxr          
            SaveQuery.ParamByName('Pxr_f').asFloat :=
StrToFloatDef(Pxr_f.Text,12);:G_CO_Max,               | G_CO_Max     
            SaveQuery.ParamByName('G_CO_Max').asFloat :=
StrToFloatDef(G_CO_Max.Text,12);:Got_f,                  | Got       
               SaveQuery.ParamByName('Got_f').asFloat :=
StrToFloatDef(Got_f.Text,12);:Gor_f,                  | Gor          
            SaveQuery.ParamByName('Gor_f').asFloat :=
StrToFloatDef(Gor_f.Text,12);:Gxt_f,                  | Gxt          
            SaveQuery.ParamByName('Gxt_f').asFloat :=
StrToFloatDef(Gxt_f.Text,12);:Gxr_f,                  | Gxr          
            SaveQuery.ParamByName('Gxr_f').asFloat :=
StrToFloatDef(Gxr_f.Text,12);:Gxtx_f,                 | Gxtx         
            SaveQuery.ParamByName('Gxtx_f').asFloat :=
StrToFloatDef(Gxtx_f.Text,12);:CPIsum_f,               | CPIsum      
             SaveQuery.ParamByName('CPIsum_f').asFloat :=
StrToFloatDef(CPIsum_f.Text,12);:CPIsat_f,               | CPIsat    
               SaveQuery.ParamByName('CPIsat_f').asFloat :=
StrToFloatDef(CPIsat_f.Text,12);:descr_cust              |
cust_fullname            
 SaveQuery.ParamByName('descr_cust').asString:=descr_cust.Text;
:City_Full_f,            | city_full                  SaveQuery.ParamB
yName('City_Full_f').asString:=City_Full_f.Text;:begtime,            
  | t_strt                    
SaveQuery.ParamByName('begtime').asTime:=BegTime.Time;:endTime,      
        | t_end                    
 SaveQuery.ParamByName('endTime').asTime:=EndTime.Time;:SpentTime,   
         | SpentTime                  SaveQuery.ParamByName('SpentTime
').asFloat:=StrToFloatDef(SpentTime.Text,12);:F_Centre,              
| fcenter                    SaveQuery.ParamByName('F_Centre').asFloat
:=StrToFloatDef(F_Centre.Text,12);:BW,                     | band    
                
SaveQuery.ParamByName('BW').asFloat:=StrToFloatDef(BW.Text,12);:st_co,
                 | spect_co                  
SaveQuery.ParamByName('st_co').asString := st_co;:st_cx              
   | spect_cx                  
SaveQuery.ParamByName('st_cx').asString := st_cx;
You can do it another way using the automatic insert-generator of FPC.
I consider it a bit of cheating but you can examine the SQL in the
Showmessage for InsertSQL to see what's different.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SaveQuery.SQL.Text := 'SELECT * FROM work_sp';  SaveQuery.Active :=
True; // <-- this will fill in SaveQuery.  
ShowMessage(SaveQuery.UpdateSQL.Text); // <-- This will show the
insert statement   SaveQuery.Insert; // <-- this switches the dataset
to insert   SaveQuery.FieldByName('ES_Index').AsString :=
ES_Index.Text;  SaveQuery.FieldByName('fdate').AsDateTime :=
StrToDateTime(Date_Time.Text);  // depending if Date_Time has a Date
property or is just text 
SaveQuery.FieldByName('customer_es_name').AsString :=
customer_es_name.Text; 
SaveQuery.FieldByName('ASMI_Operator').AsString :=
ASMI_Operator.Text;  SaveQuery.FieldByName('operator_phone').AsString
:= operator_phone.Text;  SaveQuery.FieldByName('srb_doc').AsString :=
srb_doc.Text;  SaveQuery.FieldByName('Satellite').AsString :=
Satellite.Text;  SaveQuery.FieldByName('SatLongitude').AsFloat :=
StrToFloatDef(SatLongitude.Text, 12); 
SaveQuery.FieldByName('ChooseTP').AsString := ChooseTP.Text; 
SaveQuery.FieldByName('Co_Up_Pol').AsString := Co_Up_pol.Text; 
SaveQuery.FieldByName('CX_tp').AsString := CX_tp.Text; 
SaveQuery.FieldByName('Cx_Up_pol').AsString := Cx_Up_pol.Text; 
SaveQuery.FieldByName('Station_Own').AsString := Station_Own.Text; 
SaveQuery.FieldByName('User_Operator').AsString :=
User_Operator.Text;  SaveQuery.FieldByName('User_Cont_Phone').AsString
:= User_Cont_Phone.Text; 
SaveQuery.FieldByName('ES_location').AsString := ES_location.Text; 
SaveQuery.FieldByName('ES_Latitude').AsFloat :=
StrToFloatDef(ES_latitude.Text, 12); 
SaveQuery.FieldByName('ES_Longitude').AsFloat :=
StrToFloatDef(ES_longitude.Text, 12); 
SaveQuery.FieldByName('Ant_Diameter').AsFloat :=
StrToFloatDef(Ant_Diameter.Text, 12); 
SaveQuery.FieldByName('Ref_Station').AsString := Ref_Station.Text; 
SaveQuery.FieldByName('F_up').AsFloat := StrToFloatDef(F_up.Text,
12);  SaveQuery.FieldByName('F_dn').AsFloat :=
StrToFloatDef(F_dn.Text, 12); 
SaveQuery.FieldByName('CO_EIRP_f').AsFloat :=
StrToFloatDef(CO_EIRP_f.Text, 12); 
SaveQuery.FieldByName('CPI_es_f').AsFloat :=
StrToFloatDef(CPI_es_f.Text, 12); 
SaveQuery.FieldByName('sp').AsString := sp; 
SaveQuery.FieldByName('frq').AsString := frq; 
SaveQuery.FieldByName('eirp').AsString := eirp; 
SaveQuery.FieldByName('comment').AsString := Comment.Text; 
SaveQuery.FieldByName('Pot_f').AsFloat := StrToFloatDef(Pot_f.Text,
12);  // or StrToFloatDef if Text can be empty or invalid) 
SaveQuery.FieldByName('Pxt_f').AsFloat := StrToFloatDef(Pxt_f.Text,
12);  SaveQuery.FieldByName('Por_f').AsFloat :=
StrToFloatDef(Por_f.Text, 12);  SaveQuery.FieldByName('Pxr_f').AsFloat
:= StrToFloatDef(Pxr_f.Text, 12); 
SaveQuery.FieldByName('G_CO_Max').AsFloat :=
StrToFloatDef(G_CO_Max.Text, 12); 
SaveQuery.FieldByName('Got_f').AsFloat := StrToFloatDef(Got_f.Text,
12);  SaveQuery.FieldByName('Gor_f').AsFloat :=
StrToFloatDef(Gor_f.Text, 12);  SaveQuery.FieldByName('Gxt_f').AsFloat
:= StrToFloatDef(Gxt_f.Text, 12); 
SaveQuery.FieldByName('Gxr_f').AsFloat := StrToFloatDef(Gxr_f.Text,
12);  SaveQuery.FieldByName('Gxtx_f').AsFloat :=
StrToFloatDef(Gxtx_f.Text, 12); 
SaveQuery.FieldByName('CPIsum_f').AsFloat :=
StrToFloatDef(CPIsum_f.Text, 12); 
SaveQuery.FieldByName('CPIsat_f').AsFloat :=
StrToFloatDef(CPIsat_f.Text, 12); 
SaveQuery.FieldByName('descr_cust').AsString := descr_cust.Text; 
SaveQuery.FieldByName('City_Full_f').AsString := City_Full_f.Text; 
SaveQuery.FieldByName('begtime').asTime := BegTime.Time; 
SaveQuery.FieldByName('endTime').asTime := EndTime.Time; 
SaveQuery.FieldByName('SpentTime').AsFloat :=
StrToFloatDef(SpentTime.Text, 12); 
SaveQuery.FieldByName('F_Centre').AsFloat :=
StrToFloatDef(F_Centre.Text, 12);  SaveQuery.FieldByName('BW').AsFloat
:= StrToFloatDef(BW.Text, 12); 
SaveQuery.FieldByName('st_co').AsString := st_co; 
SaveQuery.FieldByName('st_cx').AsString := st_cx;   SaveQuery.Post; 
SaveQuery.ApplyUpdates  SQLTransaction.Commit;
Although you need to handle the last_insert_id() first and put that
manually in es_id.

B.T.W. the showmessage will give you the full format with all fields
specified so you could use that one too now because you shouldn't get
column count mismatch anymore:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
work_sp (field1, field2) VALUES (:field1, :field2)

----------------------------------------------------------------------
syalyshev · 2017-10-23 15:27

QuoteYes, and according to the rest, the columns should match:
The INSERT SQL had 50 fields, and the table also has 50 fields.
So they should match.but they don't match!
Of course the "parameter" method will let me to pinpoint the field
that causes ""is invalid float, but how to make it work!

----------------------------------------------------------------------
rvk · 2017-10-23 15:46

Wait, you said you had the following message:
"MySQL50Connection: error exequting query: Column count doesn't match
value count at row 1."

So what message do you really get.

You get set a breakpoint and step through the code until you get the
error.

----------------------------------------------------------------------
syalyshev · 2017-10-24 14:24

I really get "MySQL50Connection: error exequting query: Column count
doesn't match value count at row 1." message. The break point was on
the Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.ExecSQL statement

----------------------------------------------------------------------
rvk · 2017-10-24 14:27

Quote from: syalyshev on October 24, 2017, 04:24:19 pmI really get
"MySQL50Connection: error exequting query: Column count doesn't match
value count at row 1." message. The break point was on the Code:
Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.ExecSQL statement
Yeah, then it's not the "is invalid float" you mentioned a few posts
ago.

What does this show you?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.SQL.Text := 'SELECT * FROM work_sp';SaveQuery.Active :=
True; // <-- this will fill in
SaveQuery.ShowMessage(SaveQuery.InsertSQL.Text); // <-- This will show
the insert statement

----------------------------------------------------------------------
syalyshev · 2017-10-25 10:29

Dear rvk! It is my fault with bad explanation of the problem that
caused misunderstanding.
I'll explain it from the beginning. There was table work_sp that was
filled in with program using SaveQuery likeCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.SQL.ADD('SELECT * from  work_sp');SaveQuery.Active:=true;Sav
eQuery.Insert;SaveQuery.FieldByName('idx').asString :=
ES_Index.Text;SaveQuery.FieldByName('date').asDateTime :=
StrToDateTime(Date_Time.Text);
SaveQuery.FieldByName('cust_station_name').asString := customer_es_nam
e.Text;SaveQuery.FieldByName('operator').asString:=ASMI_Operator.Text;
SaveQuery.FieldByName('operator_phone').asString:=operator_phone.Text;
                 ....SaveQuery.FieldByName('city_full').asString:=City
_Full_f.Text;SaveQuery.FieldByName('t_strt').asString:=TimeToStr(BegTi
me.Time);SaveQuery.FieldByName('t_end').asString:=TimeToStr(EndTime.Ti
me);SaveQuery.FieldByName('SpentTime').asFloat:=StrToFloatDef(SpentTim
e.Text,12);SaveQuery.Post;SaveQuery.ApplyUpdates; It works wery well,
saves evry needed value without any error messages.
Then I modified the table work_sp by adding four extra fields: fcenter
(decimal(15,6), band (decimal(15,6), spect_co (MEDIUMBLOB or
MEDIUMTEXT) and spect_cx (MEDIUMBLOB or MEDIUMTEXT).
Sure I modified SaveQuery withCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SaveQuery.F
ieldByName('fcenter').asFloat:=StrToFloatDef(F_Centre.Text,12);SaveQue
ry.FieldByName('band').asFloat:=StrToFloatDef(BW.Text,12);SaveQuery.Fi
eldByName('spect_co').asString :=
st_co;SaveQuery.FieldByName('spect_cx').asString := st_cx;

----------------------------------------------------------------------
syalyshev · 2017-10-25 10:39

And then I began to obtain ""is invalid float. So, adding four fields
caused this. But recording into table is correct, even BLOBS are
correct!
Further I tried to use parameters like you advised for to pinpoint the
operator that causes ""is invalid float. It gives "Column count
doesn't match value count at row 1."

----------------------------------------------------------------------
rvk · 2017-10-25 17:39

You still didn't show what the showmessage(SaveQuery.InsertSQL.Text);
shows you.

Furthermore... you have a SpentTime defined as varchar(15) and you are
trying to put a float (with asFloat) into that field 

So if you use the insert string you need to build it exactly as the
showmessage shows you. You shouldn't rely on the default number of
fields in the table. Name the fields you are going to update (which
InsertSQL.Text will show you).

With the '' is invalid float... there is some line where you try to
put a empty text-string into a float (which the message suggest). If
you use that method you could set a breakpoint and go over every line
until you get that error. The invalid float error should show up
directly and not when ExecSQL is done. Only the column mismatch error
is given during the ExecSQL.

So what method do you want to use?
1) Insert statement with Parameters?
(then show what the showmessage of insertSQL.Text gives you on Select
* from table)

2) Insert statement with hard coded fields?
In that case you need to be very careful how you include your fields
(and parameters will work better then)

3) SELECT * FROM table and .Insert
In that case FieldByName can be used and you don't have any problem
because FPC constructs the InsertSQL.Text for you.

PS. StrToFloat(F_Centre.Text) will give an error '' is invalid float
if F_Centre.Text is empty. That's why you would use StrToFloatDef() to
give a default value if a invalid float is in F_Centre.Text. Or are
you sure F_Centre.Text is filed with valid floats (with point decimals
or comma decimals depending on you locale)?

----------------------------------------------------------------------
syalyshev · 2017-10-26 08:50

You wrote:
QuoteYou still didn't show what the
showmessage(SaveQuery.InsertSQL.Text); shows you.It shows blank
message.

----------------------------------------------------------------------
rvk · 2017-10-30 10:25

Quote from: syalyshev on October 26, 2017, 10:50:20 amYou wrote:
QuoteYou still didn't show what the
showmessage(SaveQuery.InsertSQL.Text); shows you.It shows blank
message.
Ok, I thought FPC would fill it dynamically but apparently it's only
filled in internally when doing the actual insert.

For the column problem you could do something like this (with
supplying the fields to insert too):
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SaveQuery.SQL.Add('INSERT INTO work_sp (');SaveQuery.SQL.Add('es_id,
idx, date, cust_station_name, operator, operator_phone,
');SaveQuery.SQL.Add('srb_doc, satellite, satellite_longitude,
co_tp_name, co_tp_pol, cx_tp_name, cx_tp_pol, es_owner,
');SaveQuery.SQL.Add('contact_person, contact_phone, es_location,
es_latitude, es_longitude, es_ant_diam,
');SaveQuery.SQL.Add('ref_station, frq_isol_up, frq_isol_dn,
co_eirp_calculated, cpi_es_calculated, dir_spectrum, dir_frq,
dir_eirp, comment, Pot, Pxt, Por, ');SaveQuery.SQL.Add('Pxr, G_CO_Max,
Got, Gor, Gxt, Gxr, Gxtx, CPIsum, CPIsat, cust_fullname,
');SaveQuery.SQL.Add('city_full, t_strt, t_end, SpentTime, fcenter,
band, spect_co, spect_cx ');SaveQuery.SQL.Add(') VALUES
(');SaveQuery.SQL.Add('last_insert_id(), :idx, :date,
:cust_station_name, :operator, :operator_phone,
');SaveQuery.SQL.Add(':srb_doc, :satellite, :satellite_longitude,
:co_tp_name, :co_tp_pol, :cx_tp_name, :cx_tp_pol, :es_owner,
');SaveQuery.SQL.Add(':contact_person, :contact_phone, :es_location,
:es_latitude, :es_longitude, :es_ant_diam,
');SaveQuery.SQL.Add(':ref_station, :frq_isol_up, :frq_isol_dn,
:co_eirp_calculated, :cpi_es_calculated, :dir_spectrum, :dir_frq,
:dir_eirp, :comment, :Pot, :Pxt, :Por, ');SaveQuery.SQL.Add(':Pxr,
:G_CO_Max, :Got, :Gor, :Gxt, :Gxr, :Gxtx, :CPIsum, :CPIsat,
:cust_fullname, ');SaveQuery.SQL.Add(':city_full, :t_strt, :t_end,
:SpentTime, :fcenter, :band, :spect_co, :spect_cx);');And then fill
the :fields with ParamByName() (except es_id which is filled with
last_insert_id()).

Do note that you should use asFloat := StrToFloatDef(xx,0) because
otherwise you get a '' is invalid float when the xx-string is empty.

----------------------------------------------------------------------
syalyshev · 2017-11-02 08:26

Dear rvk! Thanks again for your work for me. I really got to know much
new thanks to you!
The problem was fixed very simply. I just made the new table field by
field manually first had removed SQLTransaction.commit statement after
SaveQuery.ApplyUpdates; SaveQuery.ExecSQL; saving each time the new
field was added to the table. No "" is invalid float occur! Before and
after excluding the transaction commisson with old table I checked
saving query and  "" is invalid float occured in both cases. I desided
that the old table was damaged and I commited to create the new table
as described above. Then I changed the old table for just created one.
SQLTransaction.Commit is a strange thing in Lazarus and I still don't
understand completely how it works. At the beginnin of the program
there is the procedure of connecting to Database containing the
SQLTransaction.Active:=true; statement. The repetition of transaction
with commit causes "" is invalid float. That's what I think. But I can
be mistaken.

----------------------------------------------------------------------
rvk · 2017-11-03 09:32

Commit is for the database engine what ApplyUpdates is for your
client/computer.
When filling fields you eventually do a Post. This will put all the
fields in the record (simply put) but does nothing else.The
ApplyUpdates sends this record to the database. At that moment the
actual UPDATE command is send to the server. In Delphi the
ApplyUpdates is done automatically.And finally Commit does the real
closing of the transaction.Note that when using ExecSQL with an INSERT
or UPDATE statement, you don't need to use ApplyUpdates because
ExecSQL already send the command to the server. ApplyUpdates is only
used for "live datasets" i.c.w. .Post, .Delete etc.

QuoteThe repetition of transaction with commit causes "" is invalid
float.Now that you say it like that, there might be something else
going on with your code.

When you do a Commit, it also closes all the datasets you might have
open with that same transaction-control. If you have a component which
does not react well to a closed dataset, this error could come from
that.

Try using a CommitRetaining.