Insert Date ( DatePicker ) to SQL using C#

Question

//Hi! I am facing a problem whenever I try to insert Date using DatePicker

       DateTime birth_date=Convert.ToDateTime(datePickerEBirthDate.SelectedDate);
            SqlConnection SQLconnection = new SqlConnection(@"Server=MyMachineName\SQLEXPRESS;Database=MyDataBase;Integrated Security=SSPI");
            SqlCommand command = SQLconnection.CreateCommand();
            SQLconnection.Open();
            command.CommandText = "INSERT INTO courses " +
            "(name, birthdate) VALUES " +
            "(@name, @birthdate)";
            command.Parameters.AddWithValue("@name", txtName.Text);
            command.Parameters.AddWithValue("@birthdate", birth_date);
            command.ExecuteNonQuery();
        SQLconnection.Close();

//and this is the error message enter image description here

use visual studio breakpoints and debug the code. See what value is coming in birth_date variable ? — Shyju, Jun 07 '12 at 15:10
Could this be of use to you? [http://stackoverflow.com/questions/2191120/net-datetime-to-sqldatetime-conversion][1] [1]: http://stackoverflow.com/questions/2191120/net-datetime-to-sqldatetime-conversion — Mr Wilde, Jun 07 '12 at 15:10
value is coming in birth_date variable {1/1/0001 12:00:00 AM} — Yasser, Jun 07 '12 at 15:12

myermian · Answer 1 · 2012-06-07T16:51:23.760

Just like you grab the TextBox.Text property (txtName.Text) you should be grabbing the DatePicker.SelectedDate property (dpSBirthDate.SelectedDate). Instead, you are trying to convert the DataContext for some reason? Also, yo u can get rid of the Convert.ToDateTime(...) call because dpSBirthDate.SelectedDate will return a DateTime object.

UPDATE (Here is a completely fixed up code section):

var name = txtName.Text;
var birthdate = dpSBirthDate.SelectedDate;

using (var connection = new SqlConnection(@"Server=MyMachineName\SQLEXPRESS;Database=MyDataBase;Integrated Security=SSPI")
using (var command = connection.CreateCommand())
{
    command.CommandText = "INSERT INTO courses " +
                          "(name, birthdate) VALUES " +
                          "(@name, @birthdate)";

    if (name == null)
        throw new Exception("Name cannot be null.");
    if (!birthdate.HasValue)
        throw new Exception("Birthdate must contain a value.");

    command.Parameters.AddWithValue("@name", name);
    command.Parameters.AddWithValue("@birthdate", birthdate.Value);
    connection.Open();
    command.ExecuteNonQuery();
}

Now, if this code fails it will tell you why... either you're name or birthdate are invalid. If the birthdate is still invalid, then you must check that dpSBirthDate (the DatePicker) is being set to a correct DateTime before executing this code. Hope that helps.

Also note that I cleaned up the code by adding using statements, exception handling and opening the connection as late as possible.

I tried DateTime birthdate = Convert.ToDateTime( datePickerEBirthDate.SelectedDate); — Yasser, Jun 07 '12 at 15:20
Although it shouldn't make a difference, get rid of the Convert.ToDateTime. It's unnecessary. — myermian, Jun 07 '12 at 15:31
I couldn't it gives an error: Error Cannot implicitly convert type 'System.DateTime?' to 'System.DateTime'. An explicit conversion exists (are you missing a cast?) — Yasser, Jun 07 '12 at 15:38
@yasser: I updated my answer to help you get better debugging on the situation and cleaner code. Though, I have a feeling that the issue is that you are not setting your date in the DatePicker before executing this code. Otherwise, there is **no reason** for this to fail. — myermian, Jun 07 '12 at 16:52

score 0 · Answer 2 · answered Jun 07 '12 at 15:19

DateTime in .NET can store dates from 1 Jan 0001 till 31 Dec 9999. In SQL 1 Jan 1753 — 31 Dec 9999. So, date between 1 Jan 0001 and 1 Jan 1753 can't be saved to database. So, check parsed birth_date. Possible you must parse date with correct Culture?

score 0 · Answer 3 · answered Jun 07 '12 at 15:42

0

You should use datePickerEBirthDate.Value which will give you the datetime type so no need to convert to DateTime but you will end up with the time on that and might not want to search by this but that is your preference.

answered Jun 07 '12 at 15:42

Troublesum

285
1
3
14

I tried it but it gives a run time error: Nullable object must have a value. – Yasser Jun 07 '12 at 15:57
Well you are using WPF which is different in this case using the datePickerEBirthDate.SelectedDate is corrent but you must make sure it has a date to start with, you can get around this by setting a defualt date in the window_loaded event i.e datePickerEBirthDate.SelectedDate = DateTime.Now; SelectedDate is a nullable value type which means this does no hold the value so you can use this to see if it is not null first by if(datePickerEBirthDate.SelectedDate != null) { DateTime birth_date = datePickerEBirthDate.SelectedDate.Value;} hope this helps :) – Troublesum Jun 08 '12 at 08:09

Insert Date ( DatePicker ) to SQL using C#

3 Answers3