HOW TO INSERT & UPDATE NULL dateTimePicker VALUE INTO DATABASE

[Amiet_1]

Guys I want to insert a Null dateTime Value into SQL database connected to C# desktop application. I'm using following code.

I can insert Null dateTime Value into database, but I am having problem with how to update dateTime picker value and saving it into database.. I'm posting my code below.. Please help me guys..

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Datetimepicker
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //to load the images
            butadd.Image = Imageslist.Images[0];
            butsave.Image = Imageslist.Images[1];
            butadd.Focus();  
        }

        private void butadd_Click(object sender, EventArgs e)
        {
            clean();
            butadd.Enabled = false;
            txtname.Focus();            
        }

        public void clean()
        {
            txtname.Text = " ";
            //make the custom datetimepicker
            dateTimePicker1.CustomFormat = " ";
            dateTimePicker1.Format = DateTimePickerFormat.Custom;
        }

        private void butsave_Click(object sender, EventArgs e)
        {

            SqlConnection cnsql;            
            //connection to the sql server
            cnsql = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=testDB;Integrated Security=True");
            string sql;
            string d;
            //to check whether the datetime picker is checked or not
            //if it is checked enter the naull value
            if (dateTimePicker1.Checked == true)
            {
                d = dateTimePicker1.Value.ToString();
            }
            else
            {
                d = " ";
            }

            // to check that user enter the valid value
            if (Valid() == false)
            {
                return;
            }

            // insert into database
            sql = "insert into person values(" + str(txtname.Text) + "," + str(d) + ")";
            //open connection
            cnsql.Open();
            SqlCommand comm = new SqlCommand(sql,cnsql);
            //execute the query
            comm.ExecuteNonQuery();
            MessageBox.Show("item enter successfully");
            butadd.Enabled = false;
            //destroy the resource allocated
            cnsql.Dispose();
            comm.Dispose();
        }

        public bool Valid()
        {
            if (txtname.Text == " ")
            {
                MessageBox.Show("please enter the name");
                txtname.Focus();
                return false;
            }
            else
            {
                return true;
            }
        }

        private string str(string Value)
        {           
            if (Value.Trim() == "")
            {
                return "NULL";
            }
            else
            {
                return "'" + Value.Trim() + "'";
            }
        }

        private void dateTimePicker1_text(object sender, EventArgs e)
        {

            // to make the datetime picker in it's original format
            dateTimePicker1.Format = DateTimePickerFormat.Long;
        }

    }
}

[JerrimePatient]

Change line 68 to
sql = "insert into person values(" + str(txtname.Text) + "," + str(d) + ")";
change it to.
sql = "insert into person values('" + txtname.Text + "'," + d + ")"

from line 52 - 59

if (dateTimePicker1.Checked == true)
{
    d = dateTimePicker1.Value.ToString();
}
else
{
    d = " ";
}

change it to

if (dateTimePicker1.Checked == true)
{
    d = "'" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "'";
}
else
{
    d = "null";
}

and set the date to be nullable.

This will be the 2 posible result.
1.) insert into person values('Jerrime','2015-04-29')
2.) insert into person values('Jerrime',null)

or just change line 97
if (Value.Trim() == "")
to
if (Value.Trim() == " ")

[Amiet_1]

Jerrime, I loved your reply... But buddy I am stuck at one problem... My code works fine for inserting value.. but I am having trouble for UPDATE code.. please tell me, how can i do coding for update button with reference to my above code. I post my code for UPDATE code in just a little time. Please help me buddy..

[JerrimePatient]

private void butsave_Click(object sender, EventArgs e)
{
    sql = "update person set person_name = "+ str(txtName.Text) +", person_birthdate = "+ srt(d)+ " where person_id='person_id';"
    DataTable t = new DataTable();
    SqlDataAdaptor d = new SqlDataAdaptor();
    d.SelectCommand = new SqlCommand(sql, cnsql);
    d.Fill(t);
}

[Amiet_1]

int idValue = Convert.ToInt32(label6.Text);
            sql = "update person set Name = " + str(txtname.Text) + ", RequiredDate = " + str(d) + " where id= '" + idValue + "' ";
            DataTable t = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = new SqlCommand(sql, cnsql);
            da.Fill(t);

With this little modification, now my code works fine..

Thanks a LOOOOOTTT Jerrime. Many many thanks :)