Data type Mismatch in criteria Expression + Excel data handling

Question

Check to see if the logic is consistent, please. The procedure of this code is:

Consult a procedure and store her data in a 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 is the method should do the UPDATE / INSERT in an existing spreadsheet 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 it keeps returning me this mistake:

Data type Mismatch in criteria Expression.

Answer 1

The problem is that things are being done in the wrong order.

You are running the command before specifying the parameters with the values.

 dts = cmd.ExecuteReader(); // Executa o comando (query)
 //FECHANDO CONEXAO
 connection.Close();
 cmd.Parameters.AddWithValue("@NFEmpresa", row["NFEmpresa"]); // Adiciona os parâmetros da query depois de ter executado ela, não adianta muita coisa...

The correct is to specify the parameters before to execute the command, because he needs them to execute the command, not after.

Example:

cmd.Parameters.AddWithValue("@NFEmpresa", row["NFEmpresa"]);
cmd.Parameters.AddWithValue("@NFNumero", row["NFNumero"]);
cmd.Parameters.AddWithValue("@NFSerie", row["NFSerie"]);
// Outros parâmetros...
connection.Open();
dts = cmd.ExecuteReader();
// Use o datareader e após usar ele aí você fecha a conexão.

Another thing is that you are reusing the same command for two queries. It might work, but before declaring the parameters of the second query, clear the parameters of the first query.

cmd.Parameters.Clear();
// Agora adicione os parâmetros da segunda query...

This should be set on your two commands.