How to Insert a Date Into DateTime Column Using ADO.NET & C#

In this article, we will show how to insert a date into a date column using ADO.NET and C#.

Here we have an Access database with the 2 columns ID (type number) and date (type datetime). Now the problem is the date column does not accept a date as a string directly unless we make changes to it. The following code shows how to convert a date into a proper data that the database accepts just fine.

using System;
using
System.Drawing;
using
System.Collections;
using
System.ComponentModel;
using
System.Windows.Forms;
using
System.Data;
using
System.Data.OleDb;
using System.Globalization;

namespace MSaccessDateInsert
{
///
<summary>
///
Summary description for Form1.
///
</summary>
public class
MSaccessDateInsert : System.Windows.Forms.Form
{
private
System.Windows.Forms.TextBox t_ID;
private
System.Windows.Forms.TextBox t_Dates;
private
System.Windows.Forms.Label intLabel;
private
System.Windows.Forms.Label dateLabel;
private
System.Windows.Forms.Button DateInsert;
///
<summary>
///
Required designer variable.
///
</summary>
private System.ComponentModel.Container components = null
;
public
MSaccessDateInsert()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
<summary>
///
Clean up any resources being used.
///
</summary>
protected override void Dispose( bool
disposing )
{
if
( disposing )
{
if (components != null
)
{
components.Dispose();
}
}
base
.Dispose( disposing );
}
#region
Windows Form Designer generated code
///
<summary>
///
Required method for Designer support - do not modify
///
the contents of this method with the code editor.
///
</summary>
private void
InitializeComponent()
{
this.t_ID = new
System.Windows.Forms.TextBox();
this.t_Dates = new
System.Windows.Forms.TextBox();
this.DateInsert = new
System.Windows.Forms.Button();
this.intLabel = new
System.Windows.Forms.Label();
this.dateLabel = new
System.Windows.Forms.Label();
this
.SuspendLayout();
//
// t_ID
//
this.t_ID.Location = new
System.Drawing.Point(88, 32);
this
.t_ID.Name = "t_ID";
this
.t_ID.TabIndex = 0;
this
.t_ID.Text = "";
//
// t_Dates
//
this.t_Dates.Location = new
System.Drawing.Point(88, 64);
this
.t_Dates.Name = "t_Dates";
this
.t_Dates.TabIndex = 1;
this
.t_Dates.Text = "";
//
// DateInsert
//
this.DateInsert.Location = new
System.Drawing.Point(113, 96);
this
.DateInsert.Name = "DateInsert";
this
.DateInsert.TabIndex = 2;
this
.DateInsert.Text = "Insert Date";
this.DateInsert.Click += new System.EventHandler(this
.DateInsert_Click);
//
// intLabel
//
this.intLabel.AutoSize = true
;
this.intLabel.Location = new
System.Drawing.Point(32, 32);
this
.intLabel.Name = "intLabel";
this.intLabel.Size = new
System.Drawing.Size(49, 16);
this
.intLabel.TabIndex = 3;
this
.intLabel.Text = "Int Value";
//
// dateLabel
//
this.dateLabel.AutoSize = true
;
this.dateLabel.Location = new
System.Drawing.Point(32, 64);
this
.dateLabel.Name = "dateLabel";
this.dateLabel.Size = new
System.Drawing.Size(28, 16);
this
.dateLabel.TabIndex = 4;
this
.dateLabel.Text = "Date";
//
// MSaccessDateInsert
//
this.AutoScaleBaseSize = new
System.Drawing.Size(5, 13);
this.ClientSize = new
System.Drawing.Size(232, 166);
this.Controls.Add(this
.dateLabel);
this.Controls.Add(this
.intLabel);
this.Controls.Add(this
.DateInsert);
this.Controls.Add(this
.t_Dates);
this.Controls.Add(this
.t_ID);
this
.Name = "MSaccessDateInsert";
this
.Text = "MSaccess_DateInsert";
this.ResumeLayout(false
);
}
#endregion
///
<summary>
///
The main entry point for the application.
///
</summary>
[STAThread]
static void
Main()
{
Application.Run(
new
MSaccessDateInsert());
}
private void DateInsert_Click(object
sender, System.EventArgs e)
{
string
strConn="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=Dates.mdb" ;
OleDbConnection myConn =
new
OleDbConnection(strConn) ;
myConn.Open();
//the string to get values from the textboxes and form an "INSERT INTO"
// statement.
string
strInsert = "INSERT INTO fDate (ID, firstDate) VALUES ( ";
//reset all the textboxes
int i=int
.Parse(t_ID.Text);
System.DateTime ddt = DateTime.Parse(t_Dates.Text,System.Globalization.CultureInfo.CreateSpecificCulture("en-AU").DateTimeFormat);
string
sNow = "";
sNow = ddt.ToShortDateString();
t_ID.Text=i.ToString();
t_Dates.Text = '#'+sNow+'#';
//NOTE for integers do not have apostrophe (') in the string text
strInsert += t_ID.Text+", ";
strInsert += "CDate("+t_Dates.Text+')'+")";
OleDbCommand inst =
new
OleDbCommand(strInsert,myConn) ;
//Execute the statement
inst.ExecuteNonQuery() ;
t_Dates.Text = "";
myConn.Close() ;
}
}
}