how to do time stamping in access db using c#

Question

private void button1_Click(object sender, EventArgs e)
{
    OleDbConnection mycon = new OleDbConnection();
mycon.ConnectionString =@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Dinesh\C#\GIS_Power\WindowsFormsApplication1\bin\Power_DB1.accdb";
    OleDbCommand command = new OleDbCommand();
    command.CommandText = "INSERT INTO Table1 (Emp_ID,Asset_ID)VALUES('" + textBox1.Text + "','" + textBox2.Text + "')";
    mycon.Open();
    command.Connection = mycon;
    command.ExecuteNonQuery();
    mycon.Close();
}

this is the code that I have written to insert some details in my access db. I want that this button click also add the date and time of click into a column in my db. I tried to directly add a function GetDate within INSERT but it failed to execute. Any suggestions?

1. You shouldn't be passing TextBox.Text directly to a query. Use Parameters to guard against data injection. 2. Why can't you just use DateTime.Now and update your insert query? — Harrison
– Harrison, Commented Jan 22, 2014 at 19:37
you did the right thing using GetDate , what you need to do is show the error that you got and we can help you fix it — Mzf
– Mzf, Commented Jan 22, 2014 at 19:38
@Harrison - using DateTime.Now may be a problem if you have several servers with different clocks. — Mzf
– Mzf, Commented Jan 22, 2014 at 19:40

Harrison · Accepted Answer · 2014-01-22 21:27:44Z

1

I haven't tested this code, but it should get you started...

private void button1_Click(object sender, EventArgs e)
{
    using OleDbConnection mycon = new OleDbConnection()
    {
        mycon.ConnectionString =@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Dinesh\C#\GIS_Power\WindowsFormsApplication1\bin\Power_DB1.accdb";

        OleDbCommand command = new OleDbCommand();
        command.CommandText = "INSERT INTO Table1 (Emp_ID, Asset_ID, Date_Column) VALUES (?, ?, ?)";

        command.Parameters.Add("@EmpID", OleDbType.VarChar, 80).Value = textBox1.Text;
        command.Parameters.Add("@AssetID", OleDbType.VarChar, 80).Value = textBox2.Text;
        command.Parameters.Add("@Timestamp", OleDbType.Date).Value = DateTime.Now;

        command.Connection = mycon;
        mycon.Open();
        command.ExecuteNonQuery();
    }
}

edited Jan 22, 2014 at 21:27

answered Jan 22, 2014 at 20:15

Harrison

3,9817 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3219362 Over a year ago

still not working. what value do i give for date in insert query? coz the other two are taking textbox values but what about date?

Harrison Over a year ago

@user3219362 I was using DateTime.Now. Based on your situation, you may want to use the UTC time? What error are you getting?

user3219362 Over a year ago

Data type mismatch in criteria expression. i used DateTime.Now too

Gord Thompson Over a year ago

@user3219362 Use OleDbType.Date.

Harrison Over a year ago

@user3219362 I changed the OleDbType to Date. Gord was making the comment as I was updating the answer ;-)

|

Mzf · Accepted Answer · 2014-01-22 19:42:26Z

0

Add datetime column with default value of now

It can be done like the below

ALTER TABLE YourTable ADD CONSTRAINT DF_YourTable DEFAULT GETDATE() FOR YourColumn

answered Jan 22, 2014 at 19:42

Mzf

5,2902 gold badges27 silver badges38 bronze badges

2 Comments

user3219362 Over a year ago

will i have to write another command.CommandText statement? where do i add your line in my code? Cause if i add it, too many errors popping up

Gord Thompson Over a year ago

The Access Database Engine says "Syntax error in CONSTRAINT clause."

Collectives™ on Stack Overflow

how to do time stamping in access db using c#

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related