Update SQL command doesn't affect any rows

Question

As general question I'd like to ask what can result in an update sql command making no changes in the table without giving any error?

Now more specifically, I have an update with no errors, all variables containing correct values, there is at least one value that's different from the one in the table, the value in the WHERE clause existing in the table and no errors given. So what is possibly going wrong?

I'm working on Microsoft Visual Studio 2012 with an OleDB connection to MS Access 2010 (more precisly .accdb file) Here is the code:

public int updateCert(Calibracao cal, string certificadoAnterior)
 {
 OleDbConnection l = OleDbConnectionDAO.createConnection();
 int result = 0;

  try
    {   
       l.Open();

       OleDbCommand cmd = l.CreateCommand();
       cmd.Parameters.Add(new OleDbParameter("@data", cal.Data));
       cmd.Parameters.Add(new OleDbParameter("@entidade", cal.EntidadeCal));
       cmd.Parameters.Add(new OleDbParameter("@observacao", cal.Observacao));
       cmd.Parameters.Add(new OleDbParameter("@certificado", cal.Certificado));
       cmd.Parameters.Add(new OleDbParameter("@resultado", cal.Resultado));
       cmd.Parameters.Add(new OleDbParameter("@selecionar", cal.Selecionar));
       cmd.Parameters.Add(new OleDbParameter("@certificadoAnterior", certificadoAnterior));

       cmd.CommandText = "UPDATE [Movimento Ferramentas] SET " +
                 "[Data saida] = @data, " +
        "[Entidade] = @entidade, " +
        "Estado = 'Calibração', " +
        "[Observações1] = @observacao," +
        "Certificado = @certificado, " +
        "Resultado = @resultado " +
        "WHERE Certificado = @certificadoAnterior";

        result = cmd.ExecuteNonQuery();
        l.Close();
    }
     catch (Exception ex)
        {
            l.Close();
            System.Diagnostics.Debug.WriteLine("DAO Exception: " + ex.Message);
            return result;
        }
        return result;

}

EDIT: Corrected the SET, the issue remains

UPDATE: Problem solved with old school debugging removing everything then adding a field at the time. I don't what exactly was wrong but it ended up working. In case it helps someone else, this is how the code ended up:

cmd.Parameters.Add(new OleDbParameter("@entidade", cal.EntidadeCal));
cmd.Parameters.Add(new OleDbParameter("@data", cal.Data));
cmd.Parameters.Add(new OleDbParameter("@certificado", cal.Certificado));
cmd.Parameters.Add(new OleDbParameter("@resultado", cal.Resultado));
cmd.Parameters.Add(new OleDbParameter("@observacao", cal.Observacao));
cmd.Parameters.Add(new OleDbParameter("@certificadoAnterior", certificadoAnterior));

cmd.CommandText = "UPDATE [Movimento Ferramentas] SET " +
            "[Entidade] = @entidade, " +
            "[Data saida] = @data, " +
            "Certificado = @certificado, " +
            "Resultado = @resultado, " +
            "[Observações1] = @observacao " +
            "WHERE Certificado = @certificadoAnterior";

Datatype mismatch maybe? Post all the relevant code, please. — acfrancis
– acfrancis, Commented Nov 4, 2013 at 14:28
UPDATE SET [Movimento Ferramentas] is wrong for a start, it should be UPDATE [Movimento Ferramentas] SET ... — Bridge
– Bridge, Commented Nov 4, 2013 at 14:29
It might also help to know what kind of database engine this is going to. (You never know.) — Adam Miller
– Adam Miller, Commented Nov 4, 2013 at 14:30
Possible duplicate of stackoverflow.com/questions/19767534/… And there it was laready mentioned to use the correct syntax for your update statement... — oerkelens
– oerkelens, Commented Nov 4, 2013 at 14:33
@oerkelens That was an issue already resolved, this is a different one of there being no errors and no update. Or should I reedit the code adn questions there? — Micael Florêncio
– Micael Florêncio, Commented Nov 4, 2013 at 14:37

meda · Accepted Answer · 2013-11-06 05:14:13Z

4

You have to create the command text before attempting to add parameters:

cmd.CommandText = "UPDATE [Movimento Ferramentas] " +
                 "SET [Data saida] = @data, " +
                     "[Entidade] = @entidade, " +
                     "Estado = 'Calibração', " +
                     "[Observações1] = @observacao," +
                     "Certificado = @certificado, " +
                     "Resultado = @resultado " +
                     "WHERE Certificado = @certificadoAnterior";


cmd.Parameters.Add(new OleDbParameter("@data", cal.Data));
cmd.Parameters.Add(new OleDbParameter("@entidade", cal.EntidadeCal));
cmd.Parameters.Add(new OleDbParameter("@observacao", cal.Observacao));
cmd.Parameters.Add(new OleDbParameter("@certificado", cal.Certificado));
cmd.Parameters.Add(new OleDbParameter("@resultado", cal.Resultado));
cmd.Parameters.Add(new OleDbParameter("@selecionar", cal.Selecionar));
cmd.Parameters.Add(new OleDbParameter("@certificadoAnterior", certificadoAnterior));

Also, your update syntax is wrong, the format is:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

edited Nov 6, 2013 at 5:14

answered Nov 4, 2013 at 14:28

meda

45.5k14 gold badges96 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Micael Florêncio Over a year ago

If I remove the @ sign won't there be a conflict for example in the "Certificado = @certificado, " ?

meda Over a year ago

the parameter would look for @ then assign the value to it msdn.microsoft.com/en-us/library/50xtbfet.aspx

Micael Florêncio Over a year ago

Tried that, still not updating nor anything (result value is 0. And I'm sure it didn't update without me noticing because I have a message box that's supposed to show when the that method returns > 0)

meda Over a year ago

what is selecionar I dont see it in your update query, other than that I don't see anymore problem with your code

Bill Martin Over a year ago

Sounds like you need some good old fashioned debugging. Remove all but 1 parameter from your param list and sql statement and see if that value gets updated. If so, add the next, and so on. Could be a type mismatch issue.

Bill Martin · Accepted Answer · 2013-11-04 14:43:03Z

0

Use ? rather than the parameter name:

cmd.CommandText = "UPDATE [Movimento Ferramentas] " +
                 "SET [Data saida] = ?, " +
        "[Entidade] = ?, " +
        "Estado = 'Calibração', " +
        "[Observações1] = ?," +
        "Certificado = ?, " +
        "Resultado = ? " +
        "WHERE Certificado = ?";

Make sure your params are added to the list in the order they're referenced in the statement. Some database providers allow the @ and some require the ?. Just depends on what you're hitting, like Adam said above.

edited Nov 4, 2013 at 14:43

answered Nov 4, 2013 at 14:34

Bill Martin

4,96310 gold badges57 silver badges87 bronze badges

2 Comments

Micael Florêncio Over a year ago

As for database provider is a bit complex. Using an OleDB connection to connect to an access datasource which has a linked table from a Oracle server/database.

Micael Florêncio Over a year ago

Tried that, still not updating nor anything (result value is 0. And I'm sure it didn't update without me noticing because I have a message box that's supposed to show when the that method returns > 0)

Collectives™ on Stack Overflow

Update SQL command doesn't affect any rows

2 Answers 2

5 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Linked

Related