Home > ASP.Net, SQL Server > Insert Null Value in DateTime Column SqlServer 2005 using asp.net

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


Hi

try this example:

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.

Advertisements
Categories: ASP.Net, SQL Server Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: