Syntax error in INSERT INTO statement. System.Data.OleDb.OleDbErrorCollection

Question

Hi I am creating basic form in Visual Studio 2012 Express. I am using Microsoft Access 2012 as database. My problem is when I press submit button I nothing happens.

Syntax error in INSERT INTO statement. System.Data.OleDb.OleDbErrorCollection

My code is given below. Please help me to resolve this issue.

protected void Button1_Click(object sender, EventArgs e)
{
    string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\admin\Desktop\del\SHAFI\db.accdb";
    OleDbConnection con = new OleDbConnection(conString);
    OleDbCommand cmd = con.CreateCommand();
    string text = "INSERT INTO TEST (Number, Amount) VALUES (?, ?)";
    cmd.CommandText = text;
    try
    {
        con.Open();
        cmd.Parameters.AddWithValue("@Number", txtAmount.Text);
        cmd.Parameters.AddWithValue("@Amount", txtOrder.Text);
        cmd.ExecuteNonQuery();
    }
    catch (OleDbException ex)
    {
        txtAmount.Text = "Sorry";
        Response.Write(ex.Message.ToString() + "<br />" + ex.Errors.GetType());
    }
}

I think the problem is you are using Number which is clearly a reserved word. Try to either change it or wrap it around [] as in [Number] and see what happens now. — Kenny, Dec 30 '13 at 17:37

score 4 · Answer 1 · edited May 23 '17 at 11:56

Your code is correct, apart from the lacking of using statement around disposable objects, but the real problem is the word NUMBER. It is a reserved keyword for MSAccess.
Use it enclosed in square brackets.

string text = "INSERT INTO TEST ([Number], Amount) VALUES (?, ?)";

However, if it is still possible, I really suggest you to change that column name. This problem will become very annoying.

protected void Button1_Click(object sender, EventArgs e)
{
    string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\admin\Desktop\del\SHAFI\db.accdb";
    using(OleDbConnection con = new OleDbConnection(conString))
    using(OleDbCommand cmd = con.CreateCommand())
    {
        string text = "INSERT INTO TEST ([Number], Amount) VALUES (?, ?)";
        cmd.CommandText = text;
        try
        {
            con.Open();
            cmd.Parameters.AddWithValue("@Number", txtAmount.Text);
            cmd.Parameters.AddWithValue("@Amount", txtOrder.Text);
            cmd.ExecuteNonQuery();
        }
        catch (OleDbException ex)
        {
            txtAmount.Text = "Sorry";
            Response.Write(ex.Message.ToString() + "<br />" + ex.Errors.GetType());
        }
    }

Dave Zych · Answer 2 · 2013-12-30T17:44:02.963

2

OleDbCommand does not support named parameters. You have to add the parameters in the order you want them.

//Code above
con.Open();
cmd.Parameters.Add(txtAmount.Text);
cmd.Parameters.Add(txtOrder.Text);
cmd.ExecuteNonQuery();
//Code below

edited Dec 30 '13 at 17:44

answered Dec 30 '13 at 17:37

Dave Zych

21,581
7
51
66

I changed my code and now getting this error.No overload for method 'AddWithValue' takes 1 arguments C:\Users\admin\Desktop\del\SHAFI\excel-database\excel-database\Test.aspx.cs – Mohammad Shafi Shaikh Dec 30 '13 at 17:50
Look at my update. I forgot to update the method call to use `Add` instead of `AddWithValue`. – Dave Zych Dec 30 '13 at 17:51

score 2 · Answer 3 · answered Dec 30 '13 at 18:00

You are using @Number and @Amount variables for Number and Amount but not writing these values in query.

? used in java not in asp.net(c#). so you are mixing these two.

protected void Button1_Click(object sender, EventArgs e)
{
    string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\admin\Desktop\del\SHAFI\db.accdb";
    OleDbConnection con = new OleDbConnection(conString);
    OleDbCommand cmd = con.CreateCommand();
    string text = "INSERT INTO TEST (Number, Amount) VALUES (@Number, @Amount)";
    cmd.CommandText = text;
    try
    {
        con.Open();
        cmd.Parameters.AddWithValue("@Number", txtAmount.Text);
        cmd.Parameters.AddWithValue("@Amount", txtOrder.Text);
        cmd.ExecuteNonQuery();
    }
    catch (OleDbException ex)
    {
        txtAmount.Text = "Sorry";
        Response.Write(ex.Message.ToString() + "<br />" + ex.Errors.GetType());
    }
}

score 1 · Answer 4 · answered Dec 30 '13 at 17:36

1

You should use:

string text = "INSERT INTO TEST (Number, Amount) VALUES (@Number, @Amount)";

instead of:

INSERT INTO TEST (Number, Amount) VALUES (?, ?)

answered Dec 30 '13 at 17:36

Poornima

918
5
11

Syntax error in INSERT INTO statement. System.Data.OleDb.OleDbErrorCollection

4 Answers4

Linked