c# - 更新 C# 和 Sql server 2008 后日期错误

Question

我正在开发一个 C# windows 移动应用:

在一个表格中，我读取(自 mi mobile 起)服务器数据库(我的电脑)-SQL server 2008- 上的记录，这些记录将存储在我的移动 db - 中。 sdf 文件-之后我会用它们做一些计算，在计算之后，我的记录将返回到我的服务器数据库(又是我的电脑)-SQL Server 2008-

这是我读取记录并将它们保存在我的手机上的代码:

sql0 = new SqlCommand("Select * From ACCT WHERE FASM = '" + uss+ "'  ", conn); //"uss" is the user
//code lines
//...
//more code lines
String NUMIA, NUMIPO, NUMCTIVO;

SqlCommand sqlA = new SqlCommand("SELECT * FROM ACMCT WHERE FRASM = '" + usuario + "' ", conn);
qlA.ExecuteNonQuery();
SqlDataAdapter cbA = new SqlDataAdapter(sqlA);
DataTable dtA = new DataTable();
cbA.Fill(dtA);
foreach (DataRow drA in dtA.Rows)
{
    NUMIA = drA["NUMIA"].ToString();
    NUMIPO = drA["NUMIPO"].ToString();
    NUMCTIVO = drA["NUMCTIVO"].ToString();
    //and more code lines
    FDQIS = drA["FDQIS"].ToString(); //<<<< Here starts my problem: FDQIS gets "02/09/00 0:00:00"
    StringBuilder sbQuery0 = new StringBuilder();
    SqlCeCommand cmd00 = new SqlCeCommand();
    sbQuery0.Append("INSERT INTO ACMCT (NUMIA, NUMIPO, NUMCTIVO... )VALUES('" + NUMIA + "','" + NUMIPO + "','" + NUMCTIVO + "'... )");
    cmd00.CommandText = sbQuery0.ToString();
    cmd00.Connection = connd;
    cmd00.ExecuteNonQuery();
    cmd00.Dispose();
    if (FDQIS == "")
    {
    //code lines
    }
    else
    {
    dateTimePicker1.Value = DateTime.Parse(drA["FDQIS"].ToString());
    StringBuilder sbQuery1 = new StringBuilder();
    SqlCeCommand cmd1k = new SqlCeCommand();

    sbQuery1.Append("UPDATE ACMCT SET FDQIS = " + dateTimePicker1.Value.ToString("yyyy/MM/dd") + " WHERE NUMIA = '" + NUMIA + "' ... ... ");
    cmd1k.CommandText = sbQuery1.ToString();
    cmd1k.Connection = connd;
    cmd1k.ExecuteNonQuery();
    cmd1k.Dispose();
    }
}

我以为它是正确的，但问题是:我从我的服务器数据库中读取了我的日期 "02/09/00 0:00:00" 的值，在我更新后它被保存为 "09/02/00 0 :00:00"当我尝试在屏幕上显示它时，它显示:"22/04/00 0:00:00"。说实话，我真的不明白为什么会这样。

这是我在屏幕上显示我的数据的代码:

conn.ConnectionString = ("Data Source =" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) + "\\database.sdf;Password=****;Persist Security Info=False;");
            conn.Open();
            SqlCeCommand sql = new SqlCeCommand("SELECT * FROM ACMCT Where ACMCT.NUMCTIVO = '" + oc.Text + "' AND ACMCT.NUMIA = '" + CIA.Text + "' AND ACMCT.NUMIPO = '" + TIPO.Text + "' AND ACMCT.SUNUMCT = '" + SUBNUM.Text + "'", conn);
            sql.ExecuteNonQuery();
            SqlCeDataAdapter cb = new SqlCeDataAdapter(sql);
            DataTable dt = new DataTable();
            cb.Fill(dt);
            foreach (DataRow dr in dt.Rows)
            {
                Manntt ma = new Manntt(); 
                ma.txtCia.Text = dr["NUMIA"].ToString();
                ma.txtipo.Text = dr["NUMIPO"].ToString();
                ma.txtSubu.Text = dr["SUBUMCT"].ToString();
                ma.txtFadqFis.Text = dr["FDQIS"].ToString();//HERE the Value of txtFadqFis is "22/04/00 0:00:00"
                ma.Show();
            }

我的 INSERT/UPDATE/DELETE 操作有误吗？

知道我能做什么吗？

Answer 1

几周前我遇到过类似的问题!我相信我的 INSERTs 和 UPDATEs，甚至 DELETEs 都是错误的!但他们不是……

解决方法很简单:

试试这个:

else
    {
    dateTimePicker1.Value = DateTime.Parse(drA["FDQIS"].ToString());
    StringBuilder sbQuery1 = new StringBuilder();
    SqlCeCommand cmd1k = new SqlCeCommand();

sbQuery1.Append("UPDATE ACMCT SET FDQIS = '" + dateTimePicker1.Value.ToString("yyyy/MM/dd") + "' WHERE NUMIA = '" + NUMIA + "' ... ... "); 
//////////// NOTE: I USE '' at the beginning and at the end of your date 
cmd1k.CommandText = sbQuery1.ToString();
cmd1k.Connection = connd;
cmd1k.ExecuteNonQuery();
cmd1k.Dispose();
}