1

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());
    }
}
1
  • 1
    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. Commented Dec 30, 2013 at 17:37

4 Answers 4

4

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());
        }
    }
Sign up to request clarification or add additional context in comments.

Comments

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

2 Comments

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
Look at my update. I forgot to update the method call to use Add instead of AddWithValue.
2

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());
    }
}

Comments

1

You should use:

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

instead of:

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.