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

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?

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

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

this is the sql query for insert

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

soo solved

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.

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

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

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 is the image

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.