Skip to main content

Insert Null Value in DateTime Column SqlServer 2005 using asp.net

steps:
1) Create Simple web application (asp.net with C#).
2) create below table in the database. here i made table (table_1 in test database).
DataBase Name :Test
Table Name : Table_1
ColumnName ColumnDataType
id int (Auto Increment & Primary Key)
code varchar(50) (Allow Null True)
date datetime (Allow Null True)
3) put the below control on the form.
3.1) Calendar (id --> Calendar1)
3.2) Button (id --> btnInsert)
3.3) Button (id --> btnGet)
3.4) Label (id --> lblDataValue)
4) put the below code in to C# page.
Add below name space first :

using System.Data.SqlClient;
copy the below code and check.

 //connection string
    //please change as per your server and database 
    string constr = @"Data Source=(local);Initial Catalog=test;Integrated Security=True";
    public int insertedId
    {
        get { return Convert.ToInt32(ViewState["inserttedid"]); }
        set { ViewState["inserttedid"] = value; }
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        //if user is not selected any value from calendar at that time null is inserted
        //otherwise selected date is inserted
        string datetime = Calendar1.SelectedDate.Date.Equals(DateTime.MinValue) == true ? "Null" : "'" + Calendar1.SelectedDate + "'";
        string strins = "insert into table_1 values " +
            "('amit'," + datetime + ");select scope_identity();";
        SqlConnection sqlcon = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand(strins, sqlcon);
        sqlcon.Open();
        //get the id of inserted row using Scope_Identity function
         insertedId = Convert.ToInt32(cmd.ExecuteScalar());
        sqlcon.Close();
    }
    protected void btnGet_Click(object sender, EventArgs e)
    {
        //get the inserted record
        string strSelect = "Select * from table_1 where id =" + insertedId;
        SqlConnection sqlcon = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand(strSelect, sqlcon);
        sqlcon.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            //set the value in the label for display purpose  
            lblDataValue.Text = dr["date"] == System.DBNull.Value ? DateTime.MinValue.ToShortDateString() : Convert.ToString(dr["date"]);
        }
        sqlcon.Close();
    }


Thanks.

Comments