插入日期时间时出错

Question

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

错误信息:

将nvarchar数据类型转换为日期时间数据类型导致值超出范围。

 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;

Answer 2

您的参数类型为DateTime。使用DateTime参数代替string

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

Answer 3

改变这一行

 pa2.Value = DateTime.Now.ToString();

 pa2.Value = DateTime.Now;

你定义了一个DateTime作为类型的参数，但是你试图在需要DateTime的地方插入一个字符串。

作为代码的边注。SqlDataAdapter的所有初始化和设置其插入命令都是无用的，因为您直接调用(应该这样做)命令上的ExecuteNonQuery。

我还建议在一次性对象周围使用Using语句

using(SqlConnection con = new SqlConnection(... connection string ....))
using(SqlCommand cmd = new SqlCommand(...command text...., con))
{
     con.Open();
     cmd.Parameters.Add(......);
     cmd.ExecuteNonQuery();
}

Answer 4

问题就在这里:

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

在第一行中，您将pa定义为DateTime对象，但在下一行中，该对象被转换为字符串。

这是正确的代码

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