Data truncated for column 'Value1' at row 1 in C#

Question

I am developing an app in VS2010 c# to fetch a single row data from SQL Server and insert it to MySQL.

In this I am fetching data and trying to insert in MySQL using below code.

var msCon = new MySqlConnection(Properties.Settings.Default.dConstr);//open MySql connection
                var mcmd = msCon.CreateCommand();
                conSsDate = DateTime.Parse(ssDate).ToString("yyyy-MM-dd HH:mm:ss");
                mcmd.CommandText = "INSERT INTO sfr_raw_aqi(date_time,station_id,Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9) VALUES(@date_time,@station_id,@Value1,@Value2,@Value3,@Value4,@Value5,@Value6,@Value7,@Value8,@Value9)";//passing insert command.
                //adding parameters
                mcmd.Parameters.AddWithValue("@date_time", conSsDate);
                mcmd.Parameters.AddWithValue("@station_id", station);
                mcmd.Parameters.AddWithValue("@Value1", Value1);
                mcmd.Parameters.AddWithValue("@Value2", Value2);
                mcmd.Parameters.AddWithValue("@Value3", Value3);
                mcmd.Parameters.AddWithValue("@Value4", Value4);
                mcmd.Parameters.AddWithValue("@Value5", Value5);
                mcmd.Parameters.AddWithValue("@Value6", Value6);
                mcmd.Parameters.AddWithValue("@Value7", Value7);
                mcmd.Parameters.AddWithValue("@Value8", Value8);
                mcmd.Parameters.AddWithValue("@Value9", Value9);
                msCon.Open();
                int count = mcmd.ExecuteNonQuery();//executing query.
                msCon.Close();

when control comes to line

int count = mcmd.ExecuteNonQuery();//executing query.

I am getting error Data truncated for column 'Value1' at row 1. I don't know how it is giving this error.

My Database schema is as below .

CREATE TABLE `sfr_raw_aqi` (
`r_aqi` int(11) unsigned NOT NULL AUTO_INCREMENT,
`station_id` varchar(50) DEFAULT NULL,
`date_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Value1` float DEFAULT NULL,
`Value2` float DEFAULT NULL,
`Value3` float DEFAULT NULL,
`Value4` float DEFAULT NULL,
`Value5` float DEFAULT NULL,
`Value6` float DEFAULT NULL,
`Value7` float DEFAULT NULL,
`Value8` float DEFAULT NULL,
`Value9` float DEFAULT NULL,
`benzene` float DEFAULT NULL,
`mpxylene` float DEFAULT NULL,
`oxylene` float DEFAULT NULL,
`toluene` float DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`r_aqi`)
) ENGINE=InnoDB AUTO_INCREMENT=227 DEFAULT CHARSET=latin1;

Advise me how to resolve this.

Answer 1

float is 32 bit single precision both in C# and mySQL so if your Value1 is double then it is 64 bit double precision then you have a problem there. I am not saying that your Value1 is double but this is only a guess. So, why not convert your Value1 to float by either of the following below:

mcmd.Parameters.AddWithValue("@Value1", Convert.ToSingle(Value1));

or

mcmd.Parameters.AddWithValue("@Value1", float.Parse(Value1.ToString()));

or

mcmd.Parameters.AddWithValue("@Value1", (float)Value1);

Answer 2

you could check for Empty values before inserting as Empty value is not equal to Null.

Try This:

if(!value1.Trim().Eqlals("") && !value2.Trim().Eqlals("") && !value3.Trim().Eqlals("") &&   
!value4.Trim().Eqlals("") && !value5.Trim().Eqlals("") && !value6.Trim().Eqlals("") &&    
!value7.Trim().Eqlals(""))
{
//Execute query
}

Answer 3

You probably try to insert data longer than the column definition,change the float definition to accomodate it.Maybe use double.