Wpf C # OledbParameters Update where select

Question

Wpf C # OledbParameters Update where select

Navigation

#1 by (2 votes)

2

I am trying to change a query to use parameters but when I execute using parameter I get the error "Data type mismatch in criteria expression." No parameters works normally. Follow the two queries .

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 made a test to check if it was returning the pendencyId correctly and only the 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:

PendingValueFator: Text, 255
Pending ID: Automatic numbering
Contract: Text, 255 Pending Level: Text, 1
DateDate: Date / Time
DateDate: Date / Time

c# oledb ms-access

asked by anonymous 23.12.2016 / 16:21

1 answer

Error: The Response content must be string or object implementing __toString (), "object" given Passing and Catching the CommandParameter of a Xamarin Forms Button

score 2 · Accepted Answer

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;

E:

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();
}