Saving values from datetimepicker to SQL Server database

Question

When I try to save the details I get an error message saying the following.

How do I convert this to string value? Thanks in advance! Here is my C# code and the error message.

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\GCSCFC.mdf;Integrated Security=True;Connect Timeout=30");

    try
    {
        string sql = "INSERT INTO Leave (Employee_ID, Leave_Type,Leave_Date,Leave_Time_From, Leave_Time_To) values('" + txtEmpID.Text + "','" + cmbLeaveType.Text + "','" + PickerLeaveDate.Text + "','" + txtTimeFrom.Text + "','" + txtTimeTo.Text + "')";

        SqlCommand exesql = new SqlCommand(sql, cn);
        cn.Open();

        exesql.ExecuteNonQuery();

        MessageBox.Show("New Employee Added Successfully!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        cn.Close();
    }
}

Possible SQL Injection Use parameter binding and your code will work instead of manually adding '. — Lukasz Szozda
– Lukasz Szozda, Commented Dec 25, 2015 at 19:20
My input values would be like, Employee_ID=0001, Leave_Type=Casual, Leave_Date=26/12/2015, Leave_Time_From= 12 am, Leave_Time_To=7 pm @Sybren — Eshqna
– Eshqna, Commented Dec 25, 2015 at 19:34
The error messge is pretty clear. You are trying to store a string inside a field that expects a datetime value. Putting your values between single quote means the everything is a string not a datetime. You really need to start using parameterized queries where you could prepare parameters of the correct type expected by the database table — Steve
– Steve, Commented Dec 25, 2015 at 19:39
Right, but you are the mercy of whatever the database programmer has thought a string representing a date should be. No, there is only a correct way to do this. Parameters. — Steve
– Steve, Commented Dec 25, 2015 at 20:05

jdweng · Accepted Answer · 2015-12-26 13:37:12Z

1

Use parameters when database column is a DataTime like code below

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cn = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\GCSCFC.mdf;Integrated Security=True;Connect Timeout=30");
            try
            {
                string sql = "INSERT INTO Leave (Employee_ID, Leave_Type,Leave_Date,Leave_Time_From, Leave_Time_To) values('" + @txtEmpID + "','" + @cmbLeaveType + "','" + @PickerLeaveDate + "','" + @txtTimeFrom + "','" + @txtTimeTo + "')";
                SqlCommand exesql = new SqlCommand(sql, cn);
                exesql.Parameters.Add("@txtEmpID", SqlDbType.VarChar);
                exesql.Parameters.Add("@cmbLeaveType", SqlDbType.VarChar);
                exesql.Parameters.Add("@PickerLeaveDate", SqlDbType.DateTime);
                exesql.Parameters.Add("@txtTimeFrom", SqlDbType.VarChar);
                exesql.Parameters.Add("@txtTimeTo", SqlDbType.VarChar);

                exesql.Parameters["@txtEmpID"].Value = txtEmpID.Text;
                exesql.Parameters["@cmbLeaveType"].Value = cmbLeaveType.Text;
                exesql.Parameters["@PickerLeaveDate"].Value = PickerLeaveDate.Value;
                exesql.Parameters["@txtTimeFrom"].Value = txtTimeFrom;
                exesql.Parameters["@txtTimeTo"].Value = txtTimeTo.Text;

                cn.Open();
                exesql.ExecuteNonQuery();
                MessageBox.Show("New Employee Added Successfully!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                cn.Close();
            }
        }

edited Dec 26, 2015 at 13:37

answered Dec 25, 2015 at 20:53

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

And even better: use your SqlConnection and SqlCommand inside a using(.....) { ...... } construct which frees you from having to use a finally block and ensures proper and immediate disposal of those objects when no longer needed!

jdweng Over a year ago

The code is in a function so the variables are local on the stack and will be automatically dispose when the function terminates.

Collectives™ on Stack Overflow

Saving values from datetimepicker to SQL Server database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related