Error query Sql Server insert, not correct value format C#

Question

I have a problem with inserting data into a sql server 2012.
My problem is that when I save Quantitaand Prezzothe values are changed! what can it be?. Quantita and Prezzo as Money values are taken from a datagridview I leave you under an example with the image of the datagridview used for insertion and the result of insertion into the database.

VALUE WITHIN DATAGRIDVIEW:

AFTER INSERT INTO SQL-SERVER:

 SqlConnection conn = db.apriconnessione();
            SqlTransaction sqlTran = conn.BeginTransaction();
            try
            {
                //avvio la transazione

                SqlCommand command = conn.CreateCommand();
                command.Transaction = sqlTran;

                //InserimentoBolla
                DateTime dataconvertita = Convert.ToDateTime(labelDATADDTMOD.Text);
                command.CommandText = "SET IDENTITY_INSERT Bolla ON";
                command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO Bolla(NumeroDDT,IdCantiere,DataDDT,Agente,RagioneSociale,CodiceCliente,RiferimentiInterni,Importo,Destinazione,Filiale,Magazzino,Preparato,Vettore,TipoTrasporto) VALUES('" + labelNUMDDTMOD.Text+"','"+IdCantiere+"',convert(datetime,'"+dataconvertita+"', 103),'" + labelAgenteMOD.Text+"','"+labelRagioneSocialeMOD.Text+"','"+int.Parse(labelCodiceClienteMOD.Text)+"','"+labelRIFInternoMOD.Text+"','"+float.Parse(labelImportoMOD.Text)+"','"+labelDestMOd.Text+"','"+labelFilialeMOD.Text+"','"+labelMagazzinoMOD.Text+"','"+labelPreparatodaMOD.Text+"','"+labelvettoreMOD.Text+"','"+labelTipoTrasportoMOD.Text+"')";
                command.ExecuteNonQuery();
                command.CommandText = "SET IDENTITY_INSERT Bolla OFF";
                command.ExecuteNonQuery();
                //fine bolla

                //inserimento articolo

                for (int rows = 0; rows < dataGridViewArticoli.Rows.Count; rows++)
                {

                        string Fornitore = dataGridViewArticoli.Rows[rows].Cells[0].Value.ToString();

                        string ModelloFornitore = dataGridViewArticoli.Rows[rows].Cells[1].Value.ToString();

                        string SiglaMetel = dataGridViewArticoli.Rows[rows].Cells[2].Value.ToString();

                        string CodiceMetel = dataGridViewArticoli.Rows[rows].Cells[3].Value.ToString();

                        string CodiceInterno = dataGridViewArticoli.Rows[rows].Cells[4].Value.ToString();

                        string Descrizione = dataGridViewArticoli.Rows[rows].Cells[5].Value.ToString();

                        float prezzo = float.Parse(dataGridViewArticoli.Rows[rows].Cells[6].Value.ToString());
                       // MessageBox.Show(" "+prezzo);
                        float quantita = float.Parse(dataGridViewArticoli.Rows[rows].Cells[8].Value.ToString());

                   // MessageBox.Show("Quantita: "+quantita);
                        command.CommandText = "INSERT INTO ArticoloCantiere(IdCantiere,IdUtente,CodArt,CodMarca,CodiceInterno,ModelloFornitore,Prezzo,Quantita,Fornitore,Importato) VALUES('" + IdCantiere + "','"+u.IdUtente+"','" + CodiceMetel + "','" + SiglaMetel + "','" + CodiceInterno + "','" + ModelloFornitore + "','" + prezzo + "','" + quantita + "','COMET','BOLLA')";
                        command.ExecuteNonQuery();

                }
                //fine inserimento articolo

                //conferma delle transazioni con la commit
                sqlTran.Commit();


            }

            catch (Exception ex)
            {
                sqlTran.Rollback();
                MessageBox.Show("Errore nell'inserimento "+ex);
            }

            conn.Close();

            this.DialogResult = DialogResult.OK;
            this.Close();

Please i have trouble to understand what are you saying can you please correct a little your grammar ? — Hicham Bouchikhi
– Hicham Bouchikhi, Commented Jul 28, 2017 at 14:16
Try : float prezzo = dataGridViewArticoli.Rows[rows].Cells[6].Value; — jdweng
– jdweng, Commented Jul 28, 2017 at 14:16
Please read about and start using Prepared Statements. Your code is highly susceptible to SQL Injection and very prone to experience odd quirks and problems (and very likely to be why you opened this question to begin with) because of the string concatenation used to build the query. — Siyual
– Siyual, Commented Jul 28, 2017 at 14:17

sepupic · Accepted Answer · 2017-07-28 14:38:28Z

1

You insert string values with ',' separator into money data type, and this is a problem.

First of all, in SQL Server the decimal separator is ALWAYS '.', not the ','.

Second, you should not pass numbers as strings

Here is the code to reproduce your problem:

declare @t table(col money)
insert into @t values
('1,44');

select *
from @t;
----------

 -- 144,00

Money data type accepts input with ',' separator, but it treats it not as decimal separator but thousand separator.

So to resolve it, the best way is pass in numbers as numbers, the worst thing you can do is just replace ',' with '.' before insert:

declare @t table(col money)
insert into @t values
(replace ('1,44', ',', '.'));

select *
from @t;
-----
---1,44

edited Jul 28, 2017 at 14:38

answered Jul 28, 2017 at 14:31

sepupic

8,7371 gold badge11 silver badges21 bronze badges

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

5 Comments

user7538827 Over a year ago

I did not know this, however the values of the datagridview I have to include are loaded by a file containing the articles, if I try to replace all the strings with quantita and prezzo instead of, I put the. , It should work?

sepupic Over a year ago

Yes, exactly so, you should pass in values with a point(.) as decimal separator, not with comma (,)

user7538827 Over a year ago

It works perfectly, if I find you in the street I offer you a coffee

sepupic Over a year ago

Tanti saluti da Roma :)

user7538827 Over a year ago

Saluti da Bologna

peter.stankowski · Accepted Answer · 2017-07-28 14:29:29Z

1

I would suggest adding a breakpoint at:

command.ExecuteNonQuery();

and double check that both values are correct for:

prezzo and quantita

edit: if the values are as expected, please check sql data types.

I would also suggest adding sql parameters, otherwise your code is vulnerable to sql injections:

INSERT  INTO 
        ArticoloCantiere
        (field1, field2...) 
        VALUES  
        (@value1, @value2...)

then:

command.Parameters.Add(new SqlParameter("@value1", <somevalue>));
command.Parameters.Add(new SqlParameter("@value1", <somevalue>));
...
command.ExecuteNonQuery();

edited Jul 28, 2017 at 14:29

answered Jul 28, 2017 at 14:23

peter.stankowski

866 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2017-07-28 14:36:08Z

You need to use parameterized queries. This is a BIG DEAL in terms of security, to the point where if you're not doing this you're practically begging to get hacked.

But this isn't just about security. Parameterized queries will also likely fix your formatting issue, because they can also automatically account for things like date formats and text values with single quotes in the data. As a bonus, you'll also typically get a (very small, but measurable) performance boost.

Here's how it will look:

//putting this all in one sql string to execute in one DB call eliminates the need for C# to manage transactions.
// If you're nervous about it, you can add "BEGIN TRANSACTION" and "COMMIT" statements to the SQL.
string sql = "SET IDENTITY_INSERT Bolla ON;\n"
    + "INSERT INTO Bolla (NumeroDDT,IdCantiere,DataDDT,Agente,RagioneSociale,CodiceCliente,RiferimentiInterni,Importo,Destinazione,Filiale,Magazzino,Preparato,Vettore,TipoTrasporto)\n"
    + " VALUES(\n" 
    + "@NumeroDDT, @IdCantiere, @DataDDT, @Agente, @RagioneSociale, @CodiceCliente, @RiferimentiInterni, @Importo, @Destinazione, @Filiale, @Magazzino, @Preparato, @Vettore, @TipoTrasporto);\n"
    + "SET IDENTITY_INSERT Bolla OFF;";


using (var conn = db.apriconnessione())
using (var cmd = new SqlCommand(sql, conn))
{
    //I have to guess at column types and lengths. Update these parameters to match your DB columns
    cmd.Paramerers.Add("@NumeroDDT", SqlDbType.NVarChar, 50).Value = labelNUMDDTMOD.Text;
    cmd.Paramerers.Add("@IdCantiere", SqlDbType.Int).Value = IdCantiere;
    cmd.Paramerers.Add("@DataDDT", SqlDbType.DateTime).Value = Convert.ToDateTime(labelDATADDTMOD.Text);
    cmd.Paramerers.Add("@Agente", SqlDbType.NVarChar, 50).Value = labelAgenteMOD.Text;
    cmd.Paramerers.Add("@RagioneSociale", SqlDbType.NVarChar, 50).Value = labelRagioneSocialeMOD.Text;
    cmd.Paramerers.Add("@CodiceCliente", SqlDbType.Int).Value = int.Parse(labelCodiceClienteMOD.Text);
    cmd.Paramerers.Add("@RiferimentiInterni", SqlDbType.NVarChar, 50).Value = labelRIFInternoMOD.Text;
    cmd.Paramerers.Add("@Importo", SqlDbType.Float).Value = float.Parse(labelImportoMOD.Text); //probably should be double or decimal
    cmd.Paramerers.Add("@Destinazione", SqlDbType.NVarChar, 50).Value = labelDestMOd.Text;
    cmd.Paramerers.Add("@Filiale", SqlDbType.NVarChar, 50).Value = labelFilialeMOD.Text;
    cmd.Paramerers.Add("@Magazzino", SqlDbType.NVarChar, 50).Value = labelMagazzinoMOD.Text;
    cmd.Paramerers.Add("@Preparato", SqlDbType.NVarChar, 50).Value = labelPreparatodaMOD.Text;
    cmd.Paramerers.Add("@Vettore", SqlDbType.NVarChar, 50).Value = labelvettoreMOD.Text;
    cmd.Paramerers.Add("@TipoTrasporto", SqlDbType.NVarChar, 50).Value = labelTipoTrasportoMOD.Text;

    conn.Open();
    cmd.ExecuteNonQuery();
}

Note there are some other improvements in here, too. I didn't re-write the other parts of your code just for the fun of it. But this query parameters are the important part.

Collectives™ on Stack Overflow

Error query Sql Server insert, not correct value format C#

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related