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();
}
}
}
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;
See more on this question at Stackoverflow