c# Microsoft Access converting data

Question

I am having problems with INSERT and UPDATE because on my machine the dates are being converted automatically, but on users' machines not.

Ex:

OleDbCommand cmd = new OleDbCommand("UPDATE tblPendencia " +
                                      " SET CarenciaInicio = " + (String.IsNullOrEmpty(pendencia.CarenciaInicioData.Trim()) ? "null" : "#" + pendencia.CarenciaInicioData + "#") + "," +
                                          " CarenciaFim = " + (String.IsNullOrEmpty(pendencia.CarenciaFimData.Trim()) ? "null" : "#" + pendencia.CarenciaFimData + "#") + "," +
                                          " AberturaData = " + (pendencia.AberturaData == null ? "null" : "#" + pendencia.AberturaData + "#") + "," +
                                          " VencimentoData = " + (pendencia.VencimentoData == null ? "null" : "#" + pendencia.VencimentoData + "#") + "," +
                                          " LiquidacaoData = " + (pendencia.LiquidacaoData == null ? "null" : "#" + pendencia.LiquidacaoData + "#") + "," +
                                          " CentroCusto = '" + pendencia.CentroCusto + "'," +
                                          " DiasDecorridos = " + pendencia.DiasDecorridos + "," +
                                          " Lastro = '" + pendencia.Lastro + "'," +
                                          " Corretora = '" + pendencia.Corretora + "'," +
                                          " AdAm = '" + pendencia.AdAm + "'," +
                                          " Observacao = '" + pendencia.Observacao + "'," +
                                          " Officer = '" + pendencia.Officer + "'," +
                                          " Segmento = '" + pendencia.Segmento + "'," +
                                          " Rating = '" + pendencia.Rating + "'," +
                                          " AlcadaTipo = '" + pendencia.AlcadaTipo + "'," +
                                          " Tipo = '" + pendencia.Tipo + "'" +
                                    " WHERE pendenciaNassauId = " + pendencia.PendenciaId, conn);

cmd.ExecuteNonQuery();

Running these queries on my machine will save on access the date 11/07/2011, but if a user runs on his machine will save correct: 07/11/2011.

In desperation I changed the language of office windows and even VS2013 on my machine, but it didn’t work.

Parameters: (Ismatch datatype error)

using (OleDbConnection conn = new OleDbConnection(Conexao.getConexao()))
{
    conn.Open();

    using(OleDbCommand cmd = conn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO tblLog "+
                          "([operadorId], [logTipoId], [logData], [pendenciaId], [valorFatorAnterior], [valorFatorNovo], [statusIdAnterior], [statusIdNovo], [porContratoIdAnterior], [porContratoIdNovo], [isAtivo]) " +
                          "VALUES ( @operadorId, @logTipoId, @logData, @optionalPendenciaId, @optionalValorFatorAnterior, @optionalValorFatorNovo, @optionalStatusIdAnterior, @optionalStatusIdNovo, @optionalPorContratoIdAnterior, @optionalPorContratoIdNovo, @optionalIsAtivo);";

        cmd.Parameters.AddRange(new OleDbParameter[]
        {
            new OleDbParameter("@operadorId", operadorId),
            new OleDbParameter("@logTipoId", logTipoId),
            new OleDbParameter("@logData", logData ),
            new OleDbParameter("@optionalPendenciaId", optionalPendenciaId),
            new OleDbParameter("@optionalValorFatorAnterior", "'" + optionalValorFatorAnterior + "'"),
            new OleDbParameter("@optionalValorFatorNovo", "'" + optionalValorFatorNovo + "'"),
            new OleDbParameter("@optionalStatusIdAnterior", optionalStatusIdAnterior),
            new OleDbParameter("@optionalStatusIdNovo", optionalStatusIdNovo),
            new OleDbParameter("@optionalPorContratoIdAnterior", optionalPorContratoIdAnterior),
            new OleDbParameter("@optionalPorContratoIdNovo", optionalPorContratoIdNovo),
            new OleDbParameter("@optionalIsAtivo", optionalIsAtivo.ToString())                        
        });

        cmd.ExecuteNonQuery();
    }
}

I tested the form below but is giving datatype Ismatch.

cmd1 = new OleDbCommand();
cmd1.CommandType = CommandType.Text;

cmd1.CommandText = "INSERT INTO tblLog " +
                           "([operadorId], [logTipoId], [logData], [pendenciaId], [valorFatorAnterior], [valorFatorNovo], [statusIdAnterior], [statusIdNovo], [porContratoIdAnterior], [porContratoIdNovo], [isAtivo]) " +
                   "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

cmd1.Parameters.AddWithValue("@operadorId", operadorId);
cmd1.Parameters.AddWithValue("@logTipoId", logTipoId);
cmd1.Parameters.AddWithValue("@logData", logData );
cmd1.Parameters.AddWithValue("@optionalPendenciaId", optionalPendenciaId);
cmd1.Parameters.AddWithValue("@optionalValorFatorAnterior", optionalValorFatorAnterior);
cmd1.Parameters.AddWithValue("@optionalValorFatorNovo", optionalValorFatorNovo);
cmd1.Parameters.AddWithValue("@optionalStatusIdAnterior", optionalStatusIdAnterior);
cmd1.Parameters.AddWithValue("@optionalStatusIdNovo", optionalStatusIdNovo);
cmd1.Parameters.AddWithValue("@optionalPorContratoIdAnterior", optionalPorContratoIdAnterior);
cmd1.Parameters.AddWithValue("@optionalPorContratoIdNovo", optionalPorContratoIdNovo);
cmd1.Parameters.AddWithValue("@optionalIsAtivo", optionalIsAtivo);

cmd1.Connection = conn1;
cmd1.ExecuteNonQuery();

conn1.Close();

Answer 1

Always when using specific field recordings (datetime, decimal, double, bit, etc), there is the Parameters of Oledbcommand to correctly convert the data sent to your table into database ACCESS (this recommendation is for all databases).

Minimal example:

Model class:

public class TableData
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Created { get; set; }
    public decimal Value { get; set; }
    public bool Status { get; set; }
}

Table Layout:

Execution of INSERT in this table TableData

TableData tableData = new TableData();
tableData.Name = "Name 1";
tableData.Created = DateTime.Now;
tableData.Value = 550.90m;
tableData.Status = false;  

string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\db2000.mdb";

using (OleDbConnection connection = new OleDbConnection(ConnStr))
using (OleDbCommand command = connection.CreateCommand())
{

    connection.Open();

    string strIns = "INSERT INTO TableData([Name], [Created], [Value], [Status]) ";
    strIns += " VALUES(@Name, @Created, @Value, @Status);";

    command.CommandType = CommandType.Text;
    command.CommandText = strIns;
    command.Parameters.Add("@Name", OleDbType.VarChar, 50).Value = tableData.Name;
    command.Parameters.Add("@Created", OleDbType.Date).Value = tableData.Created;
    command.Parameters.Add("@Value", OleDbType.Single).Value = tableData.Value;
    command.Parameters.Add("@Status", OleDbType.Boolean).Value = tableData.Status;
    command.ExecuteNonQuery();

}

To UPDATE follows the same idea of INSERT only change the SQL.

_{Observing: Always make a class to represent your data, this helps in maintenance, standardizes your code and organizes each information.}

References:

• Oledbcommand class
• Property Oledbcommand.Parameters
• Estrutura Single