How to convert and save Datagridview data to Mysql database

Asked

Viewed 130 times

1

I have a field in the Mysql database of type Double(10,2) and a grid where the field is like this:

VALUE: R$10,00

When I try to save from error by saying:

The input character string was not in an incorrect format

Pointing to that line:

double col4 = Convert.ToDouble(dtgridprodutos.Rows[i].Cells[3].Value); // Valor Unitário
double col5 = Convert.ToDouble(dtgridprodutos.Rows[i].Cells[4].Value); // Valor Total

Notice that I am converting the value that comes from the grid to the same type defined in the bank! Hold can help me?

try
{
     if (dtgridprodutos.Rows.Count > 1)
     {
         ConectarBanco.Open();
         for (int i = 0; i <= dtgridprodutos.Rows.Count - 1; i++)
         {
             int colV = Convert.ToInt32(CodigoVenda.CodVenda_);
             int col1 = Convert.ToInt32(dtgridprodutos.Rows[i].Cells[0].Value); //Id
             string col2 = dtgridprodutos.Rows[i].Cells[1].Value.ToString(); //Descrição
             double col3 = Convert.ToDouble(dtgridprodutos.Rows[i].Cells[2].Value); //Quantidade
             double col4 = Convert.ToDouble(dtgridprodutos.Rows[i].Cells[3].Value); //Valor Unitario
             double col5 = Convert.ToDouble(dtgridprodutos.Rows[i].Cells[4].Value); //Valor Total

             MySqlCommand cmd = new MySqlCommand
                ("insert into itens_venda(cod_venda, cod_produto, descricao, qtd, valorunitario, valortotal)" +
                " values " +
                "(@cod_venda, @cod_produto, @descricao, @qtd, @valorunitario, @valortotal)", ConectarBanco);

             cmd.Parameters.AddWithValue("@cod_venda", colV);
             cmd.Parameters.AddWithValue("@cod_produto", col1);
             cmd.Parameters.AddWithValue("@descricao", col2);
             cmd.Parameters.AddWithValue("@qtd", col3);
             cmd.Parameters.AddWithValue("@valorunitario", col4);
             cmd.Parameters.AddWithValue("@valortotal", col5);
             cmd.ExecuteNonQuery();
        }
    }            
}
catch (MySqlException e)
{
    MessageBox.Show("ERRO" + e);
}
finally
{
    ConectarBanco.Close();
}
c# mysql

  • What value dtgridprodutos.Rows[i].Cells[3].Value? Comes with the R$? I think that’s the problem...

  • Yes, the value displayed in the grid is in curency form "R$ 0.000,00" to help the user in the compression, but when I will convert to the voice type appears this error!

  • You have to do a scam, if you only have that amount there, take the $ with replace, remove the stitch, then replace the comma by stitch, and remove the spaces. That already solves the problem, as it doesn’t have so much information basically that’s it. You can’t convert direct, need to put in American number pattern.

  • So the case is that I can not convert to Double strings that contain Letters? I will test!

1 answer

1

Create a method that allows you to convert string for double:

private double ConverteStringParaDouble(object valor)
{
    double dblValor = 0;

    string strValor = Convert.ToString(valor);

    strValor = strValor.Replace("R$", string.Empty).Replace(",", CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator);

    if (double.TryParse(strValor, out dblValor))
        return dblValor;
    else return 0;
}

Then invoke the method when you want to pass the information to the database:

double col4 = ConverteStringParaDouble(dtgridprodutos.Rows[i].Cells[3].Value); // Valor Unitário
double col5 = ConverteStringParaDouble(dtgridprodutos.Rows[i].Cells[4].Value); // Valor Total

Basically we remove the symbol "R$", we replace the comma with the tab that is configured in the Regional Settings and convert to double, correctly indicated by colleague @Virgilionovic.

Browser other questions tagged c# mysql

You are not signed in. Login or sign up in order to post.