Adding to Access DB with INSERT INTO?

Question

I have an Access DB with a table (DV1) that have [ID TIME CODE REASON] columns. I'm just trying to update the table. I keep getting a INSERT INTO sytax error. Everything I see looks fine. I've tried everything. I am opening the database, then I get the error. Any thoughts?

private void WRTODB_Click(object sender, EventArgs e)
    {
        OleDbConnection machStopDB = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+@"C:\Users\sgarner\Google Drive\Visual Studio 2012\Timer test\WRITE TO DB\WRITE TO DB\Machine_Stop.accdb");
        machStopDB.Open();
        string str = "INSERT INTO DV1(TIME,CODE,REASON)" +
            "VALUES( ('" + DateTime.Now + "'),('" + textBox1.Text + "'),('" + textBox2.Text + "'))";
        OleDbCommand insertCmd = new OleDbCommand(str, machStopDB);
        insertCmd.ExecuteNonQuery();
        machStopDB.Close();
    }

This is just a test program I am working with.

Try using parameters msdn.microsoft.com/en-GB/library/… to make your code more readable — Yahya
– Yahya, Commented Mar 20, 2013 at 16:17
@Yahya and perhaps more importantly, less vulnerable to SQL injection — Cristian Lupascu
– Cristian Lupascu, Commented Mar 20, 2013 at 16:18
I might be wrong, but why you have Parenthesis around each value? single quote should be enough I think. — Yahya
– Yahya, Commented Mar 20, 2013 at 16:18

ron tornambe · Accepted Answer · 2013-03-20 20:20:02Z

1

The following code incorporates the sound ideas offered by the comments above:

private void WRTODB_Click(object sender, EventArgs e)        
{
    try
    {
        using (SqlConnection dbConnection = new SqlConnection()) 
        {
            string Source = @"C:\Users\sgarner\Google Drive\Visual Studio 2012\Timer test\WRITE TO DB\WRITE TO DB\Machine_Stop.accdb";
            dbConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Source;
            dbConnection.Open();
            using (SqlCommand command = new SqlCommand("INSERT INTO DV1([TIME],CODE,REASON) VALUES ([pTime],[pCode],[pReason])", dbConnection))
            {
                command.Parameters.AddWithValue("pTime", DateTime.Now);
                command.Parameters.AddWithValue("pCode", textBox1.Text);
                command.Parameters.AddWithValue("pReason", textBox2.Text);
                command.ExecuteNonQuery();
            }
            dbConnection.Close();
        }
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

edited Mar 20, 2013 at 20:20

answered Mar 20, 2013 at 16:49

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

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

5 Comments

Fionnuala Over a year ago

Time is a reserved word, you will need to escape it. Why insert ptime when MS Access has Now()?

ron tornambe Over a year ago

Thanks @Remou - I overlooked TIME - will edit post. I am not clear what you mean by MS-Access has Now()? Do you mean that the Field definition should include a default that specifies Now()? - I would agree with that.

Fionnuala Over a year ago

+1 Yes, but not just that, which would indeed be good, you can simply use now in the sql string VALUES (Now(),[pCode], It saves quite a bit of pain.

RJ Moeller Over a year ago

About parameters and SqlCommand/OleDbCommand: You might want to take a look here; covers creating parameters manually in depth: stackoverflow.com/questions/14838368/…; might save a lot of pain especially with datetime and float/double.

ron tornambe Over a year ago

Not sure why you are getting this error message, but I modified the connection string in the answer and perhaps that will correct it.

Collectives™ on Stack Overflow

Adding to Access DB with INSERT INTO?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related