0

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.

4
Contributors
18
Replies
19
Views
5 Years
Discussion Span
Last Post by abelLazm
0

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?

0

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

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

0

how are you getting this date?

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

dtpDOE dateTimePicker name


Is that right?

0

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

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

0

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 by abelLazm: n/a

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

this is the sql query for insert

Edited by abelLazm: n/a

0

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.

0

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.

0

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

how can i mark it as resolved?

0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.