I'm having problems with INSERT and UPDATE because on my machine dates are being converted automatically, but not on users' machines.
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 to the date 11/07/2011, but if a user runs on his machine it will save correct: 07/11/2011.
In desperation I changed the language of office windows and even VS2013 on my machine, but it did not work.
Parameters: (error datatype mismatch)
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 mismatch.
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();