CODEWITHSUNDEEP
c#sqlsql-update
Micael Florêncio
Micael Florêncio

Reputation: 199

Update SQL command doesn't affect any rows

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";

Upvotes: 1

Views: 1241

Answers (2)

meda
meda

Reputation: 45500

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;

Upvotes: 4

Bill Martin
Bill Martin

Reputation: 4943

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.

Upvotes: 0

Related Questions