none
C# / SQL : input string was not in a correct format

    Question

  • I tried all the possibilities, I wrote ' comm.Parameters.Add("@facultate", C1.Text); ' and give me the same error.. I do not understand why .. and if I try to debug and see exactly what value it has and stopped at line "
    comm.Parameters.Add(new SqlParameter("@numar", SqlDbType.Int) { Value = int.Parse(T9.Text ) }); he stoped here and give me: 1. when coverting a string to datetime, parse the string to take before putting each variable into the datetime object... 2. make sure your method arguments are in the right format. " I can send the whole project with everything in.

    PS. : i take date from FORM DataTimePiker..


    comm.Parameters.Add(new SqlParameter("@facultate", SqlDbType.VarChar) { Value = C1.Text });
                    comm.Parameters.Add(new SqlParameter("@domeniul", SqlDbType.VarChar) { Value = T1.Text });
                    comm.Parameters.Add(new SqlParameter("@specializare", SqlDbType.VarChar) { Value = T2.Text });
                    comm.Parameters.Add(new SqlParameter("@forma_invatamant", SqlDbType.VarChar) { Value = C2.Text });
                    comm.Parameters.Add(new SqlParameter("@d_inscriere", SqlDbType.DateTime) { Value = DTP1.Value.ToString("MM/dd/yyyy") });
                    comm.Parameters.Add(new SqlParameter("@nume_prenume", SqlDbType.VarChar) { Value = T3.Text });
                    comm.Parameters.Add(new SqlParameter("@cod", SqlDbType.VarChar) { Value = T4.Text });
                    comm.Parameters.Add(new SqlParameter("@localitate", SqlDbType.VarChar) { Value = T5.Text });
                    comm.Parameters.Add(new SqlParameter("@judet", SqlDbType.VarChar) { Value = T6.Text });
                    comm.Parameters.Add(new SqlParameter("@tara", SqlDbType.VarChar) { Value = T7.Text });
                   comm.Parameters.Add(new SqlParameter("@strada", SqlDbType.VarChar) { Value = T8.Text });
                    comm.Parameters.Add(new SqlParameter("@numar", SqlDbType.Int) { Value = int.Parse(T9.Text ) });
                    comm.Parameters.Add(new SqlParameter("@bloc", SqlDbType.VarChar) { Value = T10.Text });
                    comm.Parameters.Add(new SqlParameter("@scara", SqlDbType.VarChar) { Value = int.Parse(T11.Text) });
                    comm.Parameters.Add(new SqlParameter("@etaj", SqlDbType.Int) { Value = int.Parse(T12.Text) });
                    comm.Parameters.Add(new SqlParameter("@apartament", SqlDbType.Int) { Value = int.Parse(T3.Text) });
                    comm.Parameters.Add(new SqlParameter("@sector", SqlDbType.VarChar) { Value = T14.Text });
                    comm.Parameters.Add(new SqlParameter("@cod_p", SqlDbType.Int) { Value = int.Parse(T15.Text) });
                    comm.Parameters.Add(new SqlParameter("@data_nasterii", SqlDbType.DateTime) { Value = DTP2.Value.ToString("MM/dd/yyyy") });
                    comm.Parameters.Add(new SqlParameter("@locul_nasterii", SqlDbType.VarChar) { Value = T16.Text });
                    comm.Parameters.Add(new SqlParameter("@judet_n", SqlDbType.VarChar) { Value = T17.Text });
                    comm.Parameters.Add(new SqlParameter("@tara_n", SqlDbType.VarChar) { Value = T18.Text });
                    comm.Parameters.Add(new SqlParameter("@sex", SqlDbType.VarChar) { Value = C3.Text });
                    comm.Parameters.Add(new SqlParameter("@stare_civila", SqlDbType.VarChar) { Value = C4.Text });
                    comm.Parameters.Add(new SqlParameter("@cetatenie", SqlDbType.VarChar) { Value = C5.Text });
                    comm.Parameters.Add(new SqlParameter("@cetatenie_op", SqlDbType.Int) { Value = int.Parse(T19.Text) });
                    comm.Parameters.Add(new SqlParameter("@etnie", SqlDbType.VarChar) { Value = T20.Text });
                    comm.Parameters.Add(new SqlParameter("@incadrare", SqlDbType.VarChar) { Value = C6.Text });
                    comm.Parameters.Add(new SqlParameter("@cnp", SqlDbType.Int) { Value = int.Parse(T21.Text) });
                    comm.Parameters.Add(new SqlParameter("@serie", SqlDbType.VarChar) { Value = T22.Text });
                    comm.Parameters.Add(new SqlParameter("@numar_cnp", SqlDbType.Int) { Value = T23.Text });
                    comm.Parameters.Add(new SqlParameter("@eliberat", SqlDbType.VarChar) { Value = T24.Text });
                    comm.Parameters.Add(new SqlParameter("@e_data", SqlDbType.DateTime) { Value = DTP3.Value.ToString("MM/dd/yyyy") });
                    comm.Parameters.Add(new SqlParameter("@studii_preuni", SqlDbType.VarChar) { Value = C7.Text });
                    comm.Parameters.Add(new SqlParameter("@nume_unitate", SqlDbType.VarChar) { Value = T25.Text });
                    comm.Parameters.Add(new SqlParameter("@spec_fili_profil", SqlDbType.VarChar) { Value = T26.Text });
                    comm.Parameters.Add(new SqlParameter("@oras_s", SqlDbType.VarChar) { Value = T27.Text });
                    comm.Parameters.Add(new SqlParameter("@judet_s", SqlDbType.VarChar) { Value = T28.Text });
                    comm.Parameters.Add(new SqlParameter("@tara_s", SqlDbType.VarChar) { Value = T29.Text });
                    comm.Parameters.Add(new SqlParameter("@forma_de_invatamant", SqlDbType.VarChar) { Value = C8.Text });
                    comm.Parameters.Add(new SqlParameter("@medie_bac", SqlDbType.Decimal) { Value = decimal.Parse(T30.Text) });
                    comm.Parameters.Add(new SqlParameter("@durata_studii", SqlDbType.Int) { Value = int.Parse(C9.Text) });
                    comm.Parameters.Add(new SqlParameter("@data_absolvirii", SqlDbType.DateTime) { Value = DTP4.Value.ToString("MM/dd/yyyy") });
                    comm.Parameters.Add(new SqlParameter("@tipul_diploma", SqlDbType.VarChar) { Value = C10.Text });
                    comm.Parameters.Add(new SqlParameter("@serie_diploma", SqlDbType.VarChar) { Value = T31.Text });
                    comm.Parameters.Add(new SqlParameter("@numar_diploma", SqlDbType.Int) { Value = int.Parse(T32.Text) });
                    comm.Parameters.Add(new SqlParameter("@emis_de_catre", SqlDbType.VarChar) { Value = T33.Text });
                    comm.Parameters.Add(new SqlParameter("@data_emiterii", SqlDbType.DateTime) { Value = DTP5.Value.ToString("MM/dd/yyyy") });
                    comm.Parameters.Add(new SqlParameter("@nr_foi_matricole", SqlDbType.Int) { Value = int.Parse(T34.Text) });
                    comm.Parameters.Add(new SqlParameter("@introducere_date", SqlDbType.VarChar) { Value = T35.Text });
                    comm.Parameters.Add(new SqlParameter("@semnatura", SqlDbType.VarChar) { Value = T36.Text });




    • Edited by IulianG06 Saturday, April 8, 2017 9:43 PM
    Saturday, April 8, 2017 8:21 PM

All replies

  • Hello,

    Can you please reformat your question as it's difficult to read. When doing so include a sample of the code which you are having issues with using the code button.

    Lastly, when working with parameters you need to use assertion first e.g. if a value to a parameter is a Date then use DateTime.TryParse if a if statement, same can be done for int e.g. int.TryParse. Neither is to be done when constructing the parameter as you have shown as if the DateTime can not be parsed then an exception is thrown while TryParse returns a bool, true if the string was parsed as a DateTime while false if not. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 8, 2017 9:41 PM
    Moderator
  • Sorry, first time it did not work but now went to add.
    Saturday, April 8, 2017 9:46 PM
  • On reviewing your code, you are applying formatting to the DateTime parameters e.g.

    new SqlParameter("@data_emiterii", SqlDbType.DateTime) { Value = DTP5.Value.ToString("MM/dd/yyyy") }

    You don't apply formatting, any formatting is done either when returning data via a SELECT statement in code or for instance in SQL Server Management Studio as shown below

    So you should be using

    new SqlParameter("@data_emiterii", SqlDbType.DateTime) { Value = DTP5.Value }

    Simple example for working with a decimal parameter

    decimal medie_bac;
    if (decimal.TryParse(T30.Text,out medie_bac))
    {
        // use medie_bac
    }
    else
    {
        // T30.Text was not a valid decimal
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, April 8, 2017 11:23 PM
    Moderator
  • I rezolved. I changed the whole code. But now It appears another error..

    • Conversion failed when converting date from character string

     

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

    New code.

    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, table.

    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)
    );
    
    


    • Edited by IulianG06 Sunday, April 9, 2017 8:54 PM
    Sunday, April 9, 2017 8:53 PM
  • >I changed the whole code

    Yuck.  The parameters are better.  Why not just do what Kareninstructor suggested and set the parameter values correctly, instead of converting the values to strings?

    David


    Microsoft Technology Center - Dallas
    My blog

    Sunday, April 9, 2017 9:18 PM
  • Right now i do what Kareninstructor said.

    But i have another error. : first i insert all the data and insert them without any error, and even data from other years. But after the first insert, when I want to insert data gives me this error: The variable name '@data' has already been declared. Variable names must be unique within a query batch or stord procedure..


    Sunday, April 9, 2017 9:43 PM
  • When using this method and doing multiple inserts say in a for loop the parameters need to be added prior to the for loop while adding them in the for loop will add the parameters again and thus raise an exception as the first parameter added again gets the error you just reported. So add the parameters outside the loop.

    comm.Parameters.Add(new SqlParameter() { ParameterName = "@Total",  SqlDbType = SqlDbType.Int });
    In the loop we do

    comm.Parameters["@Total"].Value = ...


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 9, 2017 11:16 PM
    Moderator
  • Thank you for all your help Karen :)!  i used cmd.Parameters.Clear(); and the error dissappeared.
    • Edited by IulianG06 Sunday, April 9, 2017 11:20 PM
    Sunday, April 9, 2017 11:19 PM
  • Thank you for all your help Karen :)!  i used cmd.Parameters.Clear(); and the error dissappeared.
    With no disrespect, that is fostering poor programming practices and realize that me telling you to re-code this is not something you might want to do but have to say it anyways. Next time you write a command don't create the parameters as done this time.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, April 9, 2017 11:45 PM
    Moderator