c# - 日期时间插入错误

Question

我在尝试使用 C# 以编程方式输入 DateTime 时遇到了下面提到的问题。

错误信息:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

 public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        string str = "";

        OpenFileDialog ob = new OpenFileDialog();
        DialogResult dr;
        dr = ob.ShowDialog();
        if (dr == DialogResult.OK)
        {
            str = ob.FileName;

        }

        SqlConnection con = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();

        con.ConnectionString = @"Data Source=.;Initial Catalog=PicStore;Integrated Security=True";
        cmd.CommandText = "insert into pathstore(paths)values(@path)";
        cmd.Connection = con;

        SqlParameter pa1 = new SqlParameter("@path", DbType.String);
        pa1.Value = str;
        cmd.Parameters.Add(pa1);

        da.InsertCommand = cmd;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

    }

    private void button2_Click(object sender, EventArgs e)
    {

        SqlConnection con = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable dt = new DataTable();

        con.ConnectionString = @"Data Source=.;Initial Catalog=PicStore;Integrated Security=True";
        cmd.CommandText = "select paths from pathstore where id=@id";
        cmd.Connection = con;

        SqlParameter pa1 = new SqlParameter("@id", DbType.Int32);
        pa1.Value = textBox1.Text;
        cmd.Parameters.Add(pa1);

        da.SelectCommand = cmd;
        con.Open();
        da.Fill(dt);
        string str1 = dt.Rows[0][0].ToString();

        //loading picture into picturebox
        pictureBox1.Load(str1);
        MessageBox.Show(str1);

        //con.Close();
        ///////////////////////////////////////////////////////


        SqlConnection con1 = new SqlConnection();
        SqlCommand cmd1 = new SqlCommand();
        SqlDataAdapter da1 = new SqlDataAdapter();

        con1.ConnectionString = @"Data Source=.;Initial Catalog=PicStore;Integrated Security=True";
        cmd1.CommandText = "insert into DtTimeTable (DTime,id) values (@DTime,@id)";
        cmd1.Connection = con1;


        SqlParameter pa2 = new SqlParameter("@DTime", DbType.DateTime);
        pa2.Value = DateTime.Now.ToString();
        cmd1.Parameters.Add(pa2);

        SqlParameter pa3 = new SqlParameter("@id", DbType.Int32);
        pa3.Value = textBox1.Text;
        cmd1.Parameters.Add(pa3);


        da1.InsertCommand = cmd1;
        con1.Open();
        cmd1.ExecuteNonQuery();
        con1.Close();

    }
}

Answer 1

问题是:

SqlParameter pa2 = new SqlParameter("@DTime", DbType.DateTime);
pa2.Value = DateTime.Now.ToString();

您已经说过该参数是一个 DateTime，但随后您将其转换为一个字符串 - 并且很可能在与服务器所期望的不同的文化中这样做。 (我怀疑这才是真正导致错误的原因。)

尽可能避免字符串转换——你只需要:

pa2.Value = DateTime.Now;

或者可能:

pa2.Value = DateTime.UtcNow;

请注意，为了方便起见，您可以在一行中完成所有这些操作:

cmd1.Parameters.Add("@DTime", SqlDbType.DateTime).Value = DateTime.Now;