SQL / C# Conversion failed when converting date from character string

Question

I changed the whole code. But now It appears an error..

Conversion failed when converting date from character string

And if i change DTP3.Value.Date to DTP3.Value.DateTime() gives me a different error.

However if you leave on today, it inserts data. But if they change and bring other data, then gives me that error.

cn.Open();
            cmd.CommandText = "INSERT INTO info(fac,dom,spec,data,form_invat,nume_prenume,cod_legitimatie,localitate,judet,tara,strada,numar,bloc,scara,etaj,apartament,sector,cod_postal,data_n,loc_nastere,judet_n,tara_n,sexul,stare_civila,cetatenie,cetatenie_op,etnie,incadrare,cnp,serie,numar_cnp,eliberat_de_catre,data_eliberare,studii,unitate_invatamant,specializare,oras_studii,judet_studii,tara_studii,forma_invatamant,media,durata_studii,data_absolvirii,tipul_diploma,serie_diploma,numar_diploma,emis,data_emiterii,numarul_foii_matricole,diploma_original,adeverinta_absolvire_original,adeverinta_absolvire_copielegalizata,atestat_original,atestat_legalizat,certificat_nastere,certificat_casatorie,foto_3_4_da,foto_3_4_nu,adeverinta_med_da,adeverinta_med_nu,copie_buletin_da,copie_buletin_nu,foaie_matricola_da,foaie_matricola_nu,taxa_achitata_da,taxa_achitata_nu,introducere_date,semnatura) values('" + C1.Text + "','" + T1.Text + "','" + T2.Text + "','" + DTP1.Value.Date + "','" + C2.Text + "','" + T3.Text + "','" + T4.Text + "','" + T5.Text + "','" + T6.Text + "','" + T7.Text + "','" + T8.Text + "','" + T9.Text + "','" + T10.Text + "','" + T11.Text + "','" + T12.Text + "','" + T13.Text + "','" + T14.Text + "','" + T15.Text + "','" + DTP2.Value.Date + "','" + T16.Text + "','" + T17.Text + "','" + T18.Text + "','" + C3.Text + "','" + C4.Text + "','" + C5.Text + "','" + T19.Text + "','" + T20.Text + "','" + C6.Text + "','" + T21.Text + "','" + T22.Text + "','" + T23.Text + "','" + T24.Text + "','" + DTP3.Value.Date + "','" + C7.Text + "','" + T25.Text + "','" + T26.Text + "','" + T27.Text + "','" + T28.Text + "','" + T29.Text + "','" + C8.Text + "','" + T30.Text + "','" + C9.Text + "','" + DTP4.Value.Date + "','" + C10.Text + "','" + T31.Text + "','" + T32.Text + "','" + T33.Text + "','" + DTP5.Value.Date + "','" + T34.Text + "','" + diploma_org + "','" + adeverinta_ab + "','" + adeverinta_ab_leg + "','" + at_original + "','" + "','" + at_leg + cert_nastere_leg + "','" + cert_cas_leg + "','" + foto_3_4_da + "','" + foto_3_4_nu + "','" + adv_med_da + "','" + adv_med_nu + "','" + copie_ci_da + "','" + copie_ci_nu + "','" + foaie_matr_da + "','" + foaie_matr_nu + "','" + bon_taxa_da + "','" + bon_taxa_nu + "','" + T35.Text + "','" + T36.Text + "')";
                cmd.ExecuteNonQuery();
                cmd.Clone();
                MessageBox.Show("Adaugat cu succes !");
                C1.Text = "";
                T1.Text = "";
                T2.Text = "";

And database tabel

CREATE TABLE [dbo].[info] (
    [Id]                                   INT          IDENTITY (1, 1) NOT NULL,
    [fac]                                  VARCHAR (50) NULL,
    [dom]                                  VARCHAR (50) NULL,
    [spec]                                 VARCHAR (50) NULL,
    [data]                                 DATE         NULL,
    [form_invat]                           VARCHAR (50) NULL,
    [nume_prenume]                         VARCHAR (50) NULL,
    [cod_legitimatie]                      VARCHAR (50) NULL,
    [localitate]                           VARCHAR (50) NULL,
    [judet]                                VARCHAR (50) NULL,
    [tara]                                 VARCHAR (50) NULL,
    [strada]                               VARCHAR (50) NULL,
    [numar]                                INT          NULL,
    [bloc]                                 VARCHAR (50) NULL,
    [scara]                                VARCHAR (50) NULL,
    [etaj]                                 INT          NULL,
    [apartament]                           INT          NULL,
    [sector]                               INT          NULL,
    [cod_postal]                           INT          NULL,
    [data_n]                               DATE         NULL,
    [loc_nastere]                          VARCHAR (50) NULL,
    [judet_n]                              VARCHAR (50) NULL,
    [tara_n]                               VARCHAR (50) NULL,
    [sexul]                                VARCHAR (50) NULL,
    [stare_civila]                         VARCHAR (50) NULL,
    [cetatenie]                            VARCHAR (50) NULL,
    [cetatenie_op]                         VARCHAR (50) NULL,
    [etnie]                                VARCHAR (50) NULL,
    [incadrare]                            VARCHAR (50) NULL,
    [cnp]                                  VARCHAR (13) NULL,
    [serie]                                VARCHAR (2)  NULL,
    [numar_cnp]                            VARCHAR (6)  NULL,
    [eliberat_de_catre]                    VARCHAR (50) NULL,
    [data_eliberare]                       DATE         NULL,
    [studii]                               VARCHAR (50) NULL,
    [unitate_invatamant]                   VARCHAR (50) NULL,
    [specializare]                         VARCHAR (50) NULL,
    [oras_studii]                          VARCHAR (50) NULL,
    [judet_studii]                         VARCHAR (50) NULL,
    [tara_studii]                          VARCHAR (50) NULL,
    [forma_invatamant]                     VARCHAR (50) NULL,
    [media]                                VARCHAR (5)  NULL,
    [durata_studii]                        INT          NULL,
    [data_absolvirii]                      DATE         NULL,
    [tipul_diploma]                        VARCHAR (50) NULL,
    [serie_diploma]                        VARCHAR (10) NULL,
    [numar_diploma]                        VARCHAR (50) NULL,
    [emis]                                 VARCHAR (50) NULL,
    [data_emiterii]                        DATE         NULL,
    [numarul_foii_matricole]               INT          NULL,
    [diploma_original]                     VARCHAR (2)  NULL,
    [adeverinta_absolvire_original]        VARCHAR (2)  NULL,
    [adeverinta_absolvire_copielegalizata] VARCHAR (2)  NULL,
    [atestat_original]                     VARCHAR (2)  NULL,
    [atestat_legalizat]                    VARCHAR (2)  NULL,
    [certificat_nastere]                   VARCHAR (2)  NULL,
    [certificat_casatorie]                 VARCHAR (2)  NULL,
    [foto_3_4_da]                          VARCHAR (2)  NULL,
    [foto_3_4_nu]                          VARCHAR (2)  NULL,
    [adeverinta_med_da]                    VARCHAR (2)  NULL,
    [adeverinta_med_nu]                    VARCHAR (2)  NULL,
    [copie_buletin_da]                     VARCHAR (2)  NULL,
    [copie_buletin_nu]                     VARCHAR (2)  NULL,
    [foaie_matricola_da]                   VARCHAR (2)  NULL,
    [foaie_matricola_nu]                   VARCHAR (2)  NULL,
    [taxa_achitata_da]                     VARCHAR (2)  NULL,
    [taxa_achitata_nu]                     VARCHAR (2)  NULL,
    [introducere_date]                     VARCHAR (50) NULL,
    [semnatura]                            VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Change it from a string concatenation to a parameterized query and pass the date (and all the other parameters) in directly. — MarcE
– MarcE, Commented Apr 9, 2017 at 21:02

Sergey Kalinichenko · Accepted Answer · 2017-04-09 22:33:52Z

2

The problem is that you make SQL Server parse your date object, which is converted to string using .NET's default conversion method.

Your approach introduces a much bigger problem - your code is vulnerable to SQL Injection Attack, which is very dangerous.

You can fix both problems at once by converting your SQL statement to one using parameters:

cmd.CommandText = "INSERT INTO info(fac,dom,spec,date,date2,...) values(@fac,@dom,@spec,@date1,@date2,...)";
cmd.Parameters.AddWithValue("@fac", C1.Text); 
cmd.Parameters.AddWithValue("@dom", T1.Text); 
...
cmd.Parameters.AddWithValue("@date", DTP4.Value.Date);
cmd.Parameters.AddWithValue("@date2", DTP7.Value.Date);

edited Apr 9, 2017 at 22:33

answered Apr 9, 2017 at 21:05

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Iulian Glăvan Over a year ago

it is SqlParameters?

Iulian Glăvan Over a year ago

it works but.. i have onther error.. The variable name '@data' has already been declared. Variable names must be unique within a query batch or stord procedure. It occurs when I'm in other dates

Sergey Kalinichenko Over a year ago

@IulianGlăvan As far as collisions go, parameter names have no meaning beyond the SQL statement, so picking the name is up to you. You can have @date1, @date2, @date3, or even @x, @y, @z.

Scott Chamberlain Over a year ago

@dasblinkenlight the overload you are calling is marked as obsolete. The op should use cmd.Parameters.AddWithValue("@fac", C1.Text); or even better cmd.Parameters.Add("@fac", SqlDbType.VarChar, 50).Value = C1.Text;

Sergey Kalinichenko Over a year ago

@ScottChamberlain Thanks! When you see things like that, feel free to suggest an edit, it tends to get approved very quickly. Thank you!

|

Collectives™ on Stack Overflow

SQL / C# Conversion failed when converting date from character string

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related