C# `INSERT INTO` MS Access db not inserting

Question

so I'm working on a database program and it requires to insert a Log into the database using INSERT INTO command, however the table remains empty after executing the query. Please help! Thanks~ Here are the codes.

    //Predefined connection string
    OleDbConnection vcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\xxx\Desktop\data.mdb;Jet OLEDB:Database Password=xxx;");




    private void Login_Load(object sender, EventArgs e)
    {
        Log("StartUp", "System", "NULL", "System StartUp");
    }

    public void Log(string Type, string User, string Table_affected, string Remarks)
    {

        string NowDateStamp = string.Format("{0}/{1}/{2}", 
                              DateTime.Now.Day.ToString(), 
                              DateTime.Now.Month.ToString(), 
                              DateTime.Now.Year.ToString());
        string NowTimeStamp = string.Format("{0}:{1}:{2}",
                              DateTime.Now.Hour.ToString(),
                              DateTime.Now.Minute.ToString(),
                              DateTime.Now.Second.ToString());
        string tempSQL = string.Format("INSERT INTO 'Log' VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')",
                                        NowDateStamp,
                                        NowTimeStamp,
                                        Type,
                                        User,
                                        Table_affected,
                                        Remarks);
        vcon.Open();
        OleDbCommand vcom = new OleDbCommand(tempSQL, vcon);
        vcom.ExecuteNonQuery();
        MessageBox.Show("Done"); // <-- This MessageBox isn't even showing.
        vcon.Close();
    }

So basically the program will start up then log the time, however it seemed done nothing to the database, anyone can help me out? Thanks!

Are you seeing any runtime errors? Also do you have to use Access - SQLServer CE4.0 is a great alternative? — James Culshaw, Mar 04 '13 at 15:05
Run in debug mode, guessing there is an error in there somewhere otherwise you'd get the messagebox, as you indicated. — Mike C., Mar 04 '13 at 15:06
For your timestamps, you could just use `var nowDateStamp = DateTime.Now.ToString("dd/MM/yyyy");` and `var nowTimeStamp = DateTime.Now.ToString("HH:mm:ss");`, not really related to your question but may make your code more readable =) — JMK, Mar 04 '13 at 15:14
Your INSERT INTO command is vulnerable to [sql injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. You should always [use parameters with QleDbCommand](http://stackoverflow.com/a/5893956/19241) instead of formatting strings with values. — Danko Durbić, Mar 04 '13 at 15:23
+1 for Danko. You should NEVER use formatted string in all of your query string. — jomsk1e, Mar 04 '13 at 21:08

score 6 · Answer 1 · answered Mar 04 '13 at 15:07

6

Executing the query causes an exception, but somewhere that exception is caught and ignored.

You are using a string literal where the table name should be. It should either be just the identifier:

INSERT INTO Log VALUES ...

or the identifier inside square brackets:

INSERT INTO [Log] VALUES ...

answered Mar 04 '13 at 15:07

Guffa

687,336
108
737
1,005

+1 - However, I would recommend always using Square brackets. You will need to for reserved words and column names with spaces anyway, so I would always do this for consistency – JMK Mar 04 '13 at 15:11

score 0 · Answer 2 · answered Mar 04 '13 at 15:08

0

Replace this:

INSERT INTO 'Log' VALUES

With this one:

INSERT INTO Log VALUES

answered Mar 04 '13 at 15:08

Yaqub Ahmad

27,569
23
102
149

I'm sorry, I've figured out that the query itself is alright, however since the table has its own primary key, such that inserting to the table will be 7 items instead of 6. Therefore I've removed the primary key and everything is fine now =] Thanks for the help! =] – Mar 04 '13 at 15:34

score 0 · Answer 3 · answered Mar 04 '13 at 15:29

In addition to what others have said I would guess that you have a syntax error too. You should be specifying into which columns you want your values to be inserted.

INSERT INTO Log (<column1>,<column2>,...)
VALUES (<Value to insert into column1>, <Value to insert into column 2>,...)

C# `INSERT INTO` MS Access db not inserting

3 Answers3