OleDB INSERT command error

Question

I have a database (i use MS ACCESS) I have this insert code, I can read data but got error when writing, I follow instruction but it didn't work here is my code

OleDbConnection con = new OleDbConnection(@" provider=Microsoft.ace.Oledb.12.0; data source=\\sisc-erelim\4_Printing\VTDB\DB\VirginiTEADB2.accdb; Persist Security Info=False");

private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Accountstbl (Username, Password)" + "VALUES ('" + textBox1.Text + "','" + textBox2.Text + "')";
                cmd.Parameters.AddWithValue("@Username", textBox1.Text);
                cmd.Parameters.AddWithValue("@Password", textBox2.Text);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                textBox1.Text = ex.ToString();
            }

And I always got this error,

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at VirginiTEAcorp.Form3.button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\12-014s\My Documents\applications\Database\WindowsFormsApplication1\Form3.cs:line 34

When you debug, what the is the value of `cmd.CommandText` before you call `ExecuteNonQuery()`? — Arran, Feb 12 '13 at 09:18

Soner Gönül · Accepted Answer · 2013-02-12T15:12:21.070

9

You defined @Username and @Password parameters but you never used in your sql command.

How about?

cmd.CommandText = "INSERT INTO Accountstbl (Username, [Password]) VALUES (@Username, @Password)";
cmd.Parameters.AddWithValue("@Username", textBox1.Text);
cmd.Parameters.AddWithValue("@Password", textBox2.Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();

Also you should use Password in square brackets on your sql command like [Password] because it is a reserved keyword in MS Access.

If you don't this can cause an error like;

Incorrect syntax near the keyword "Password"

edited Feb 12 '13 at 15:12

answered Feb 12 '13 at 09:10

Soner Gönül

97,193
102
206
364

1

@AlfredSanz Good to hear it. – Soner Gönül Feb 13 '13 at 07:41

score 1 · Answer 2 · answered Feb 12 '13 at 09:09

1

Try to change this way

 cmd.CommandText = "INSERT INTO Accountstbl (Username, Password) VALUES (@Username,@Password)";

answered Feb 12 '13 at 09:09

Alex

8,827
3
42
58

score 0 · Answer 3 · answered Feb 12 '13 at 09:09

0

A space is required in your insert statement:

ssword)" + "VALUES ('"

for example in your statement you have concatenated without allowing for a space.

Should be:

ssword)" + " VALUES ('"

For example.

answered Feb 12 '13 at 09:09

Mr. Mr.

4,257
3
27
42

Red Gabanan · Answer 4 · 2013-02-28T02:08:34.010

0

You can have this :

cmd.CommandText = "INSERT INTO Accountstbl (Username, [Password]) VALUES (@Username,@Password)";
            cmd.Parameters.AddWithValue("@Username", textBox1.Text);
            cmd.Parameters.AddWithValue("@Password", textBox2.Text);

I hope this will work.

Cheer-up.

edited Feb 28 '13 at 02:08

answered Feb 28 '13 at 01:50

Red Gabanan

166
1
1
9

OleDB INSERT command error

4 Answers4

Linked