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() ;
}
}
}