Error in DateTime insertion

I have encountered below mentioned problem while trying to enter DateTime programatically using c#.

ERROR MESSAGE:

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();

    }
}

}

Jon Skeet
people
quotationmark

This is the problem:

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

You've said the parameter is a DateTime, but then you're converting it to a string - and quite possibly doing so in a different culture to what the server is expecting. (I suspect that's what's actually causing the error.)

Avoid string conversions whenever you can - you just need:

pa2.Value = DateTime.Now;

Or possibly:

pa2.Value = DateTime.UtcNow;

Note that for convenience you can do this all in one line:

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

people

See more on this question at Stackoverflow