Analyze to see if the logic is consistent, please. The procedure for this code is:
Query a procedure and store its data in DataTable :
private DataTable ExportPerformanceEntrega()
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("sp_ExportPerformanceEntrega");
SqlParameter[] parametros = new SqlParameter[] { new SqlParameter("@empIni", txtEmpIni.Text),
new SqlParameter("@empFim", txtEmpFim.Text),
new SqlParameter("@dtIni", Convert.ToDateTime(txtDtIni.Text).ToString("yyyy-MM-dd")),
new SqlParameter("@dtFim", Convert.ToDateTime(txtDtFim.Text).ToString("yyyy-MM-dd")),
(string.IsNullOrEmpty(ddlAreaBaixa.SelectedValue) ? new SqlParameter("@areaBaixa", DBNull.Value): new SqlParameter("@areaBaixa", ddlAreaBaixa.SelectedValue))};
return SqlDAO.consultarSQLProc(strSql, parametros);
}
catch (Exception ex)
{
throw ex;
}
}
Here's the method you should do UPDATE / INSERT in an existing worksheet already.
private void AtualizarPerformanceEntrega()
{
try
{
DataTable dt = ExportPerformanceEntrega();
string sFileXLSX = @"C:\Entrega\Performance.xlsx";// ConfigurationManager.AppSettings["ExportPerformanceEntrega"];
if (File.Exists(sFileXLSX))
{
string strConnXLSX = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + sFileXLSX + "';Extended Properties=Excel 12.0;";
foreach (DataRow row in dt.Rows)
{
using (OleDbConnection connection = new OleDbConnection(strConnXLSX))
{
//SQL para fazer a cosnulta
string strSQL;
OleDbCommand cmd;
OleDbDataReader dts;
if ((string)row["StatusEntrega"] == "No Prazo" || (string)row["StatusEntrega"] == "Fora do Prazo" || (string)row["StatusEntrega"] == "Antes do Prazo")
{
strSQL = "SELECT * FROM [BaseEntregue$] WHERE NFEmpresa = '" + row["NFEmpresa"] + "' AND NFNumero = '" + row["NFNumero"] + "' AND NFSerie = '" + row["NFSerie"] + "' AND NFCliente = '" + row["NFCliente"] + "' AND NFPedido = '" + row["NFPedido"] +
"' AND NFCidade = '" + row["NFCidade"] + "' AND NFUF = '" + row["NFUF"] + "' AND Regiao = '" + row["Regiao"] + "' AND NFTransp = '" + row["NFTransp"] + "' AND NomeTransp = '" + row["NomeTransp"] +
"' AND DataEmbarque = '" + row["DataEmbarque"] + "' AND DataPrevEntrega = '" + row["DataPrevEntrega"] + "' AND DataEntrega = '" + row["DataEntrega"] + "' AND StatusEntrega = '" + row["StatusEntrega"] + "' AND DiasAtraso = '" + row["DiasAtraso"] + "' AND CodOcorrencia = '" + row["CodOCorrencia"] +
"' AND DescrOcorrencia = '" + row["DescrOcorrencia"] + "'";
// Criando o OleDbCommand com o SQL e a conexão
cmd = new OleDbCommand(strSQL, connection);
// Abrindo a conexão
connection.Open();
//Executando o SELECT
dts = cmd.ExecuteReader();
//FECHANDO CONEXAO
connection.Close();
cmd.Parameters.AddWithValue("@NFEmpresa", row["NFEmpresa"]);
cmd.Parameters.AddWithValue("@NFNumero", row["NFNumero"]);
cmd.Parameters.AddWithValue("@NFSerie", row["NFSerie"]);
cmd.Parameters.AddWithValue("@NFCliente", row["NFCliente"]);
cmd.Parameters.AddWithValue("@NFPedido", row["NFPedido"]);
cmd.Parameters.AddWithValue("@NFCidade", row["NFCidade"]);
cmd.Parameters.AddWithValue("@NFUF", row["NFUF"]);
cmd.Parameters.AddWithValue("@Regiao", row["Regiao"]);
cmd.Parameters.AddWithValue("@NFTransp", row["NFTransp"]);
cmd.Parameters.AddWithValue("@NomeTransp", row["NomeTransp"]);
cmd.Parameters.AddWithValue("@DataEmbarque", row["DataEmbarque"]);
cmd.Parameters.AddWithValue("@DataPrevEntrega", row["DataPrevEntrega"]);
cmd.Parameters.AddWithValue("@DataEntrega", row["DataEntrega"]);
cmd.Parameters.AddWithValue("@StatusEntrega", row["StatusEntrega"]);
cmd.Parameters.AddWithValue("@DiasAtraso", row["DiasAtraso"]);
cmd.Parameters.AddWithValue("@CodOcorrencia", row["CodOcorrencia"]);
cmd.Parameters.AddWithValue("@DescrOcorrencia", row["DescrOcorrencia"]);
if (dt.Rows.Count > 0)
{
strSQL = "UPDATE [BaseEntregue$] SET NFEmpresa = @NFEmpresa, NFNumero = @NFNumero, NFSerie = @NFSerie, NFCliente = @NFCliente, NFPedido = @NFPedido, " +
"NFCidade = @NFCidade, NFUF = @NFUF, Regiao = @Regiao, NFTransp = @NFTransp, NomeTransp = @NomeTransp, DataEmbarque = @DataEmbarque, DataPrevEntrega = @DataPrevEntrega, " +
"DataEntrega = @DataEntrega, StatusEntrega = @StatusEntrega, DiasAtraso = @DiasAtraso, CodOcorrencia = @CodOcorrencia, DescrOcorrencia = @DescrOcorrencia " +
"WHERE NFEmpresa = @NFEmpresa AND NFNumero = @NFNumero AND NFSerie = @NFSerie AND NFCliente = @NFCliente AND NFPedido = @NFPedido";
cmd.Parameters.Add(new OleDbParameter("@NFEmpresa", row["NFEmpresa"]));
cmd.Parameters.Add(new OleDbParameter("@NFNumero", row["NFNumero"]));
cmd.Parameters.Add(new OleDbParameter("@NFSerie", row["NFSerie"]));
cmd.Parameters.Add(new OleDbParameter("@NFCliente", row["NFCliente"]));
cmd.Parameters.Add(new OleDbParameter("@NFPedido", row["NFPedido"]));
cmd.Parameters.Add(new OleDbParameter("@NFCidade", row["NFCidade"]));
cmd.Parameters.Add(new OleDbParameter("@NFUF", row["NFUF"]));
cmd.Parameters.Add(new OleDbParameter("@Regiao", row["Regiao"]));
cmd.Parameters.Add(new OleDbParameter("@NFTransp", row["NFTransp"]));
cmd.Parameters.Add(new OleDbParameter("@NomeTransp", row["NomeTransp"]));
cmd.Parameters.Add(new OleDbParameter("@DataEmbarque", row["DataEmbarque"]));
cmd.Parameters.Add(new OleDbParameter("@DataPrevEntrega", row["DataPrevEntrega"]));
cmd.Parameters.Add(new OleDbParameter("@DataEntrega", row["DataEntrega"]));
cmd.Parameters.Add(new OleDbParameter("@StatusEntrega", row["StatusEntrega"]));
cmd.Parameters.Add(new OleDbParameter("@DiasAtraso", row["DiasAtraso"]));
cmd.Parameters.Add(new OleDbParameter("@CodOcorrencia", row["CodOcorrencia"]));
cmd.Parameters.Add(new OleDbParameter("@DescrOcorrencia", row["DescrOcorrencia"]));
cmd = new OleDbCommand(strSQL, connection);
//Abrindo a conexão
connection.Open();
//Executando o UPDATE
cmd.ExecuteNonQuery();
//Fechando a conexão
connection.Close();
}
}
}
But I get this error:
Data type mismatch in criteria expression.