DataTime Exception

[xanawa]

I am getting the value of a datetime picker and add it in the database but it is throwing an exception saying :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime.
The statement has been terminated.

This is my SQL Statement

public bool AddStaff(Staff s)
        {
            bool StaffUnique = IsUnique(s);

            if (StaffUnique)
            {
                try
                {
                    conn.Open();

                    SqlCommand comm = new SqlCommand("INSERT INTO tblStaff VALUES ('" + s.IDCardNumber + "','" + s.FirstName + "','" + s.LastName + "','" + s.DateOfBirth + "','" + s.Gender + "','" + s.Address1 + "','" + s.Address2 + "','" + s.TownID + "','" + s.PostalCode + "','" + s.JobTypeID + "','" + s.DateOfEmployment + "','" + s.Email + "','" + s.TelephoneNumber + "','" + s.MobileNumber + "')", conn);
                    
                    comm.ExecuteNonQuery();
                    return true;
                }

                catch (Exception e)
                {
                    MessageBox.Show(e.Message.ToString());
                    return false;
                }

                finally
                {
                    conn.Close();
                }
            }

            else
            {
                return false;
            }
        }

Pls help if you have a solution.

[abelLazm]

convert you datetime to string before using in insert to statement

[xanawa]

convert you datetime to string before using in insert to statement

Do u mean like this?

DateTime dateOfBirth = Convert.ToDateTime(dtpDOE.Text.ToString())

dtpDOE is the name of the DateTimePicker

if no could u pls give me an example?

[abelLazm]

i mean

SqlCommand comm = new SqlCommand("INSERT INTO tblStaff VALUES ('" + s.IDCardNumber + "','" + s.FirstName + "','" + s.LastName + "','" + s.DateOfBirth.ToString() + "','" + s.Gender + "','" + s.Address1 + "','" + s.Address2 + "','" + s.TownID + "','" + s.PostalCode + "','" + s.JobTypeID + "','" + s.DateOfEmployment + "','" + s.Email + "','" + s.TelephoneNumber + "','" + s.MobileNumber + "')", conn);

[xanawa]

i mean

SqlCommand comm = new SqlCommand("INSERT INTO tblStaff VALUES ('" + s.IDCardNumber + "','" + s.FirstName + "','" + s.LastName + "','" + s.DateOfBirth.ToString() + "','" + s.Gender + "','" + s.Address1 + "','" + s.Address2 + "','" + s.TownID + "','" + s.PostalCode + "','" + s.JobTypeID + "','" + s.DateOfEmployment + "','" + s.Email + "','" + s.TelephoneNumber + "','" + s.MobileNumber + "')", conn);

didnt work

[abelLazm]

how are you getting this date?

[xanawa]

how are you getting this date?

DateTime dateOfBirth = Convert.ToDateTime(dtpDOE.Text.ToString())

dtpDOE dateTimePicker name

Is that right?

[abelLazm]

Oho this is not the way remove

DateTime dateOfBirth = Convert.ToDateTime(dtpDOE.Text.ToString())

this line just write this code

SqlCommand comm = new SqlCommand("INSERT INTO tblStaff VALUES ('" + s.IDCardNumber + "','" + s.FirstName + "','" + s.LastName + "','" + s.dtoDOE.Value.ToString() + "','" + s.Gender + "','" + s.Address1 + "','" + s.Address2 + "','" + s.TownID + "','" + s.PostalCode + "','" + s.JobTypeID + "','" + s.DateOfEmployment + "','" + s.Email + "','" + s.TelephoneNumber + "','" + s.MobileNumber + "')", conn);

[xanawa]

Oho this is not the way remove this line just write this code

SqlCommand comm = new SqlCommand("INSERT INTO tblStaff VALUES ('" + s.IDCardNumber + "','" + s.FirstName + "','" + s.LastName + "','" + s.dtoDOE.Value.ToString() + "','" + s.Gender + "','" + s.Address1 + "','" + s.Address2 + "','" + s.TownID + "','" + s.PostalCode + "','" + s.JobTypeID + "','" + s.DateOfEmployment + "','" + s.Email + "','" + s.TelephoneNumber + "','" + s.MobileNumber + "')", conn);

still did not work

[abelLazm]

dtoDOE.Value; This is the way we get any value from a datetimepicker control it is already in datetime formate so no need to convert it and when you want to use in a sql query then we simply convert it to String in this way dtoDOE.Value.ToString(); I dnt know why you are getting exception

[abelLazm]

INSERT INTO Table_name (Col1,Col2,Col3,.....) VALUES( " +val1+ " , '" +val2 + "' ,'" + val3 + "'..........' )"

this is the sql query for insert

[xanawa]

i had to do .toString("MM/dd/yyyy")

soo solved

[jbrock31]

I noticed you also have a date of employment field in this insert... Try making sure both are strings before passing into insert. Otherwise we need to see more code.

[jbrock31]

nevermind i just saw your other post. please disregard. Thanks

[abelLazm]

if you find the solution please mark this thread as solved :)

[virusisfound]

as per your posts I think the size of datetime filed in database tables is small. either increase the size of that field in database tables or set format property of datatimepiker to short.

[xanawa]

if you find the solution please mark this thread as solved :)

how can i mark it as resolved?

[abelLazm]

just above the message box you will fine some lines written in the first line there will be a link Mark this thread solved

[abelLazm]

This is the image