Insert is not working / Excel

Asked

Viewed 38 times

0

I created a process to make a Insert in a spreadsheet on EXCEL but the data is not being entered. No error is returned.

Code:

private void AtualizarPerformanceEntrega()
{
    try
    {

        DataTable dt = ExportPerformanceEntrega();

        //string sFileXLSX =  @"C:\Entrega\Performance.xlsx"; //ConfigurationManager.AppSettings["ExportPerformanceEntrega"]
        if (File.Exists(@"C:\Entrega\Performance.xlsx"))
        {
            string strConnXLSX = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Entrega\Performance.xlsx; Extended Properties='Excel 12.0 Xml;HDR=YES;ReadOnly=False';");

            foreach (DataRow row in dt.Rows)
            {
                using (OleDbConnection connection = new OleDbConnection(strConnXLSX))
                {

                    string strSQL;
                    OleDbCommand cmd;
                    //OleDbDataReader dts;
                    if ((string)row["StatusEntrega"] == "No Prazo" || (string)row["StatusEntrega"] == "Fora do Prazo" || (string)row["StatusEntrega"] == "Antes do Prazo")
                    {

                        if (dt.Rows.Count > 0)
                        {
                            strSQL = "INSERT INTO [BaseEntregue$] " +
                           " (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
                           " VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
                           " @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
                            cmd = new OleDbCommand(strSQL, connection);
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));

                            connection.Open();
                            //Executando o INSERT
                            cmd.ExecuteNonQuery();
                            //Fechando a conexão
                            connection.Close();
                        }

                    }
                    if ((string)row["StatusEntrega"] == "Não Entregue")
                    {


                        if (dt.Rows.Count > 0)
                        {
                            strSQL = "INSERT INTO [BaseNaoEntregue$] " +
                          " (NFEmpresa,NFNumero,NFSerie,NFCliente,NFPedido,NFCidade,NFUF,Regiao,NFTransp,NomeTransp,DataEmbarque,DataPrevEntrega,DataEntrega,StatusEntrega,DiasAtraso,CodOcorrencia,DescrOcorrencia) " +
                          " VALUES (@NFEmpresa, @NFNumero, @NFSerie, @NFCliente, @NFPedido, @NFCidade, @NFUF, @Regiao, @NFTransp, @NomeTransp, @DataEmbarque, @DataPrevEntrega, " +
                          " @DataEntrega, @StatusEntrega, @DiasAtraso, @CodOcorrencia, @DescrOcorrencia)";
                            cmd = new OleDbCommand(strSQL, connection);
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFEmpresa"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFNumero"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFSerie"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCliente"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFPedido"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFCidade"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFUF"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["Regiao"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NFTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["NomeTransp"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEmbarque"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataPrevEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DataEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["StatusEntrega"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DiasAtraso"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["CodOcorrencia"]));
                            cmd.Parameters.Add(new OleDbParameter("?", row["DescrOcorrencia"]));

                            connection.Open();
                            //Executando o INSERT
                            cmd.ExecuteNonQuery();
                            //Fechando a conexão
                            connection.Close();
                        }

                    }

                    connection.Close();   
                }

            }

        }
    }
    catch (Exception ex)
    {

        throw ex;
    }
}
  • Already made some debug, to check if it is satisfying us first if and else???

  • @Virgilionovic Yes, he satisfies all decisions.

  • I think it should look like this: new OleDbParameter("@NFEmpresa", row["NFEmpresa"]), changes in all fields doing please and test! This link explains: http://cbsa.com.br/post/manipular-dados-do-excel-em-aspnet-com-insert-e-update.aspx

  • @Virgilionovic didn’t work.

  • What’s the mistake?.

No answers

Browser other questions tagged

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