Wpf C# Oledbparameters Update Where select

Asked

Viewed 52 times

2

I’m trying to change a query to use Parameters but when running using Parameter I get the "Data type Mismatch in criteria Expression" error. Without Parameters works normally. Follow the two darlings.

cmd = new OleDbCommand("UPDATE tblPendencia " +
                         " SET PendenciaValorFator = @pendenciaValorFator" +
                       " WHERE pendenciaId = " +
                             " (SELECT TOP 1 P.pendenciaId " +
                                " FROM tblPendencia P " +
                               " WHERE P.Contrato = @contrato" +
                                 " AND P.PendenciaNivel = @pendenciaNivel" +
                                 " AND P.AberturaData = @aberturaData" +
                                 " AND P.VencimentoData = @vencimentoData)", conn);

cmd.Parameters.Add("@pendenciaValorFator", OleDbType.VarChar, 255).Value = ((object)item.FatorValor) ?? DBNull.Value;
cmd.Parameters.Add("@contrato", OleDbType.VarChar, 255).Value = ((object)item.Contrato) ?? DBNull.Value;
cmd.Parameters.Add("@pendenciaNivel", OleDbType.VarChar, 1).Value = ((object)item.PendenciaNivel) ?? DBNull.Value;
cmd.Parameters.Add("@aberturaData", OleDbType.Date).Value = !String.IsNullOrEmpty(item.DataAbertura) ? (((object)Convert.ToDateTime(item.DataAbertura)) ?? DBNull.Value) : DBNull.Value;
cmd.Parameters.Add("@vencimentoData", OleDbType.Date).Value = !String.IsNullOrEmpty(item.DataVencimento) ? (((object)Convert.ToDateTime(item.DataVencimento)) ?? DBNull.Value) : DBNull.Value;

cmd = new OleDbCommand("UPDATE tblPendencia " +
                         " SET PendenciaValorFator = '" + item.FatorValor + "'" +
                       " WHERE pendenciaId = " +
                             " (SELECT TOP 1 P.pendenciaId " +
                                " FROM tblPendencia P " +
                               " WHERE P.Contrato = '" + item.Contrato + "'" +
                                 " AND P.PendenciaNivel = '" + item.PendenciaNivel + "'" +
                                 " AND P.AberturaData = " + (String.IsNullOrEmpty(item.DataAbertura.Trim()) ? "null" : "#" + item.DataAbertura + "#") +
                                 " AND P.VencimentoData = " + (String.IsNullOrEmpty(item.DataVencimento.Trim()) ? "null)" : "#" + item.DataVencimento + "#)"), conn);

I did a test to check if it was returning the pendenciaId correctly and only select works:

cmd = new OleDbCommand("SELECT TOP 1 P.pendenciaId " +
                        " FROM tblPendencia P " +
                       " WHERE P.Contrato = @contrato" +
                         " AND P.PendenciaNivel = @pendenciaNivel" +
                         " AND P.AberturaData = @aberturaData" +
                         " AND P.VencimentoData = @vencimentoData", conn);

cmd.Parameters.Add("@contrato", OleDbType.VarChar, 255).Value = ((object)item.Contrato) ?? DBNull.Value;
cmd.Parameters.Add("@pendenciaNivel", OleDbType.VarChar, 1).Value = ((object)item.PendenciaNivel) ?? DBNull.Value;
cmd.Parameters.Add("@aberturaData", OleDbType.Date).Value = !String.IsNullOrEmpty(item.DataAbertura) ? (((object)Convert.ToDateTime(item.DataAbertura)) ?? DBNull.Value) : DBNull.Value;
cmd.Parameters.Add("@vencimentoData", OleDbType.Date).Value = !String.IsNullOrEmpty(item.DataVencimento) ? (((object)Convert.ToDateTime(item.DataVencimento)) ?? DBNull.Value) : DBNull.Value;

Types in the database:

  • Pendenciavalorfator: Texto, 255
  • Pendenciaid: Automatic numbering
  • Contract: Texto, 255 Pendencianivel: Texto, 1
  • Open Date: Date/Time
  • Expirationdate: Date/Time
c# ms-access oledb

  • Just inform you that select returns correctly, what is the result? Some value is returned?

  • returns the id 103092

1 answer

2


I ended up doing it in two steps:

cmd = new OleDbCommand("SELECT TOP 1 P.pendenciaId " +
                        " FROM tblPendencia P " +
                       " WHERE P.Contrato = @contrato" +
                         " AND P.PendenciaNivel = @pendenciaNivel" +
                         " AND P.AberturaData = @aberturaData" +
                         " AND P.VencimentoData = @vencimentoData", conn);

cmd.Parameters.Add("@contrato", OleDbType.VarChar, 255).Value = ((object)item.Contrato) ?? DBNull.Value;
cmd.Parameters.Add("@pendenciaNivel", OleDbType.VarChar, 1).Value = ((object)item.PendenciaNivel) ?? DBNull.Value;
cmd.Parameters.Add("@aberturaData", OleDbType.Date).Value = !String.IsNullOrEmpty(item.DataAbertura) ? (((object)Convert.ToDateTime(item.DataAbertura)) ?? DBNull.Value) : DBNull.Value;
cmd.Parameters.Add("@vencimentoData", OleDbType.Date).Value = !String.IsNullOrEmpty(item.DataVencimento) ? (((object)Convert.ToDateTime(item.DataVencimento)) ?? DBNull.Value) : DBNull.Value;

And:

reader = cmd.ExecuteReader();

while (reader.Read())
{
    cmd = new OleDbCommand("UPDATE tblPendencia " +
                         " SET PendenciaValorFator = @pendenciaValorFator" +
                       " WHERE pendenciaId = @pendenciaId", conn);

    cmd.Parameters.Add("@pendenciaValorFator", OleDbType.VarChar, 255).Value = ((object)item.FatorValor) ?? DBNull.Value;
    cmd.Parameters.Add("@pendenciaId", OleDbType.Integer).Value = ((object)reader[0]) ?? DBNull.Value;


    cmd.ExecuteNonQuery();
}

Browser other questions tagged c# ms-access oledb

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