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.

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?

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

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

how are you getting this date?

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

dtpDOE dateTimePicker name


Is that right?

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

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

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

Edited 5 Years Ago by abelLazm: n/a

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

this is the sql query for insert

Edited 5 Years Ago by abelLazm: n/a

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.

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.

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

how can i mark it as resolved?

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

This article has been dead for over six months. Start a new discussion instead.