C# error sql string or binary data

Question

I'm trying to carry data from one table, however there are several types that I converted with the C# code. I put in the error image that comes out.

private void button5_Click(object sender, EventArgs e)
{
    try
    {
        //SORGENTE 

        string dbserver = textBox5.Text;
        string dbname = textBox8.Text;
        string dbusername = textBox7.Text;
        string dbpassword = textBox6.Text;

        SqlConnection conn = new SqlConnection("Data Source=" + dbserver + ";Initial Catalog=" + dbname + ";User ID=" + dbusername + ";Password=" + dbpassword + "");

        conn.Open();
        //PER QUERY PESANTI:
        string queryset2 = "SET IDENTITY_INSERT Preventivi ON ";

        SqlCommand myCommandset2 = new SqlCommand(queryset2, conn);

        myCommandset2.ExecuteNonQuery();

        //FINE QUERY PESANTI

        SqlCommand cmd = new SqlCommand("SELECT IDPreventivo,IDCliente,Data,RiferimentoInterno,Note,Totale,CostoImpianto,SpeseGen,PercSpeseGen,Utile,PercUtile,Margine,PercMargine,Testata,Chiusura,CodiceFirma FROM Preventivi WHERE DittaPreventivo = '100Impianti'", conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);

        String idpreventivo = null;
        String idcliente = null;
        String datacreazione = null;
        String riferimentointerno = null;
        String note = null;
        String totaleimpianto = null;
        String costoimpianto = null;
        String spesegenerali = null;
        String percentualespesegenerali = null;
        String utile = null;
        String percentualeutile = null;
        String margine = null;
        String percentualemargine = null;
        String testata = null;
        String chiusura = null;
        String codicefirma = null;

        //CONVERSIONI
        foreach (DataRow dr in dt.Rows)
        {
            idpreventivo = dr["IDPreventivo"].ToString();
            idcliente = dr["IDCliente"].ToString();
            datacreazione = dr["Data"].ToString();
            riferimentointerno = dr["RiferimentoInterno"].ToString();
            note = dr["Note"].ToString();
            totaleimpianto = dr["Totale"].ToString();
            costoimpianto = dr["CostoImpianto"].ToString();     
            spesegenerali = dr["SpeseGen"].ToString();
            percentualespesegenerali = dr["PercSpeseGen"].ToString(); 
            utile= dr["Utile"].ToString();
            percentualeutile = dr["PercUtile"].ToString();
            margine = dr["Margine"].ToString();
            percentualemargine = dr["PercMargine"].ToString();             
            testata = dr["Testata"].ToString();
            chiusura = dr["Chiusura"].ToString();
            codicefirma = dr["CodiceFirma"].ToString();

            //Destinazione 

            string dbserver2 = textBox4.Text;
            string dbname2 = textBox1.Text;
            string dbusername2 = textBox2.Text;
            string dbpassword2 = textBox3.Text;

            SqlConnection conn2 = new SqlConnection("Data Source=" + dbserver2 + ";Initial Catalog=" + dbname2 + ";User ID=" + dbusername2 + ";Password=" + dbpassword2 + "");

            conn2.Open();

            //PER QUERY PESANTI:
            string queryset3 = "SET IDENTITY_INSERT Preventivo ON ";

            SqlCommand myCommandset3 = new SqlCommand(queryset3, conn2);

            myCommandset3.ExecuteNonQuery();

            //FINE QUERY PESANTI

            string query2 = "INSERT INTO Preventivo (IdPreventivo,IdCliente,DataInserimento,RiferimentoInterno,Note,Totale,CostoImpianto,SpeseGenerali,PercentualeSpeseGenerali,Utile,PercentualeUtile,Margine,PercentualeMargine,Testata,Chiusura,CodiceFirma) VALUES(@IdPreventivo,@IdCliente,@DataInserimento,@RiferimentoInterno,@Note,@Totale,@CostoImpianto,@SpeseGenerali,@PercentualeSpeseGenerali,@Utile,@PercentualeUtile,@Margine,@PercentualeMargine,@Testata,@Chiusura,@CodiceFirma)";

            SqlCommand myCommand = new SqlCommand(query2, conn2);

            int idp = System.Convert.ToInt32(idpreventivo);

            myCommand.Parameters.AddWithValue("@IdPreventivo", idp);

            int idc = System.Convert.ToInt32(idcliente);

            myCommand.Parameters.AddWithValue("@IdCliente", idc);

            myCommand.Parameters.AddWithValue("@DataInserimento", datacreazione);

            decimal totimp = System.Convert.ToDecimal(totaleimpianto);

            myCommand.Parameters.AddWithValue("@Totale", totimp);

            decimal costimp = System.Convert.ToDecimal(costoimpianto);

            myCommand.Parameters.AddWithValue("@CostoImpianto", costimp);

            decimal speseimp = System.Convert.ToDecimal(spesegenerali);

            myCommand.Parameters.AddWithValue("@SpeseGenerali", speseimp);

            decimal perspeseimp = System.Convert.ToDecimal(percentualespesegenerali);

            myCommand.Parameters.AddWithValue("@PercentualeSpeseGenerali", perspeseimp);

            decimal ut = System.Convert.ToDecimal(utile);

            myCommand.Parameters.AddWithValue("@Utile", ut);

            decimal perut = System.Convert.ToDecimal(percentualeutile);

            myCommand.Parameters.AddWithValue("@PercentualeUtile", perut);

            decimal mar = System.Convert.ToDecimal(margine);

            myCommand.Parameters.AddWithValue("@Margine", mar);

            decimal permar = System.Convert.ToDecimal(percentualemargine);

            myCommand.Parameters.AddWithValue("@PercentualeMargine", permar);

            myCommand.Parameters.AddWithValue("@Testata", testata);

            myCommand.Parameters.AddWithValue("@Chiusura", chiusura);

            int codf = Int32.Parse(codicefirma.ToString());

            myCommand.Parameters.AddWithValue("@CodiceFirma", codf);

            myCommand.Parameters.AddWithValue("@RiferimentoInterno", riferimentointerno);

            myCommand.Parameters.AddWithValue("@Note", note);

            myCommand.ExecuteNonQuery();

            conn2.Close();

            //fine destinazione
        }

        // textBox9.Text = val.ToString();
        //MessageBox.Show(val);
        MessageBox.Show("Dati Esportati ");

        conn.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(" " + ex);
    }
}

The error is:

ERROR IMAGE

SQL Server table structures:

CREATE TABLE [dbo].[Preventivi]
(
    [IDPreventivo] [int] IDENTITY(1,1) NOT NULL,
    [Data] [datetime] NOT NULL,
    [RiferimentoInterno] [varchar](50) NOT NULL,
    [IDCliente] [int] NOT NULL,
    [IDProgettista] [int] NULL,
    [Contatto] [varchar](50) NOT NULL,
    [Stato] [varchar](50) NOT NULL,
    [TipoFornitura] [varchar](50) NOT NULL,
    [Note] [varchar](500) NOT NULL,
    [DirettoreLavori] [varchar](50) NOT NULL,
    [CostoImpianto] [decimal](18, 2) NOT NULL,
    [PercSpeseGen] [decimal](9, 2) NOT NULL,
    [SpeseGen] [decimal](18, 2) NOT NULL,
    [PercUtile] [decimal](9, 2) NOT NULL,
    [Utile] [decimal](18, 2) NOT NULL,
    [PercMargine] [decimal](9, 2) NOT NULL,
    [Margine] [decimal](18, 2) NOT NULL,
    [Totale] [decimal](18, 2) NOT NULL,
    [FlagCalcoloUtile] [char](1) NOT NULL,
    [Testata] [varchar](5000) NOT NULL,
    [Chiusura] [varchar](5000) NOT NULL,
    [CodiceFirma] [int] NOT NULL,
    [Firma] [image] NULL,
    [DittaPreventivo] [varchar](50) NULL,
    [Testata1] [varchar](5000) NOT NULL,
    [Chiusura1] [varchar](5000) NOT NULL,
    [IDtemp] [varchar](100) NULL,
)

CREATE TABLE [dbo].[Preventivo]
(
    [IdPreventivo] [int] IDENTITY(1,1) NOT NULL,
    [IdCliente] [int] NULL,
    [RiferimentoInterno] [varchar](60) NULL,
    [Note] [varchar](200) NULL,
    [DataInserimento] [datetime] NULL,
    [Stato] [varchar](20) NULL,
    [DataAccettazione] [datetime] NULL,
    [Totale] [decimal](18, 5) NULL,
    [CostoImpianto] [decimal](18, 5) NULL,
    [SpeseGenerali] [decimal](18, 5) NULL,
    [PercentualeSpeseGenerali] [decimal](18, 5) NULL,
    [Utile] [decimal](18, 5) NULL,
    [PercentualeUtile] [decimal](18, 5) NULL,
    [Margine] [decimal](18, 5) NULL,
    [PercentualeMargine] [decimal](18, 5) NULL,
    [Testata] [varchar](255) NULL,
    [Chiusura] [varchar](255) NULL,
    [CodiceFirma] [int] NULL,
)

Possible duplicate of [SQLException : String or binary data would be truncated](http://stackoverflow.com/questions/779082/sqlexception-string-or-binary-data-would-be-truncated) — Bridge, Feb 18 '16 at 10:35
check the length of passing parameters with equivalent columns length — Jaydip Jadhav, Feb 18 '16 at 10:37
@Wapac i have change [Testata] [varchar](255) NULL, [Chiusura] [varchar](255) NULL, into 5000 but now i have this error: [ERROR IMAGE][1] [1]: http://i.stack.imgur.com/UGiSv.png — rikideveloper, Feb 18 '16 at 10:41
This means that my answer answered the original question, which is good :-) Now you have to make sure that you put correctly formatted values into your datetime columns - i.e. `DataInserimento` and `DataAccettazione`. — Wapac, Feb 18 '16 at 13:00

score 2 · Answer 1 · answered Feb 18 '16 at 10:34

2

Among other things, in the first table, you have

[Testata1] [varchar](5000) NOT NULL,
[Chiusura1] [varchar](5000) NOT NULL,

and in the second table, you have

[Testata] [varchar](255) NULL,
[Chiusura] [varchar](255) NULL,

If there is a row with Testata1 value longer than 255 characters, it won't fit into Testata and give you that "String or binary data will be truncated" error.

You have to make sure that what you are going to insert into the new table will fit there.

answered Feb 18 '16 at 10:34

Wapac

4,058
2
20
33

i have change [Testata] [varchar](255) NULL, [Chiusura] [varchar](255) NULL, into 5000 but now i have this error: [ERROR IMAGE](http://i.stack.imgur.com/UGiSv.png) – rikideveloper Feb 18 '16 at 10:41
1

This means that my answer answered the original question, which is good :-) Now you have to make sure that you put correctly formatted values into your datetime columns - i.e. DataInserimento and DataAccettazione. – Wapac Feb 18 '16 at 15:01

score 0 · Answer 2 · edited Feb 19 '16 at 08:35

I belive that the below statement is to add a date parameter with a string variable. And it looks that the value of the parameter does not comply with the date format as expected by SQL. Please convert the variable "datacreazione" as datetime object in c# and pass it to SQL as parameter.

myCommand.Parameters.AddWithValue("@DataInserimento", datacreazione);

Maybe you can try the following

DateTime creationDate;
string[] formats = { "dd/MM/yyyy", "dd/M/yyyy", "d/M/yyyy", "d/MM/yyyy",
                    "dd/MM/yy", "dd/M/yy", "d/M/yy", "d/MM/yy"};

DateTime.TryParseExact(datacreazione, formats, 
        System.Globalization.CultureInfo.InvariantCulture,
        DateTimeStyles.None, out creationDate)
myCommand.Parameters.AddWithValue("@DataInserimento", creationDate);

OR

You can also change the reading part of the sql statement like this

DateTime datacreazione;

for 
{
   ...
   datacreazione = dr["Data"]
}

C# error sql string or binary data

2 Answers2