Syntax error in INSERT INTO Statement while trying to input values in the Database

Question

Hi i basically am creating a registration page. I am getting an error saying "Syntax error in INSERT INTO statement." Also sometimes am getting an Error saying that the connection is open or something. It was working before in a different table and different fields etc...The code is as follows

public partial class Registration : System.Web.UI.Page
{
    static OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\New folder\Project 1.0\WebSite1\New Microsoft Office Access 2007 Database.accdb");
    OleDbDataAdapter ada = new OleDbDataAdapter();
    OleDbCommand cmd = new OleDbCommand();
    OleDbDataReader dr;
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
    string str = "insert into User_Registeration (First_Name, Last_name, Phone_No, Username, Password, Email, Address, City, Country, Zipcode)" +
         "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    con.Open();
    cmd = new OleDbCommand(str, con);
    cmd.Parameters.AddWithValue("@p1", TextBox1.Text);
    cmd.Parameters.AddWithValue("@p2", TextBox2.Text);
    cmd.Parameters.AddWithValue("@p3", TextBox3.Text);
    cmd.Parameters.AddWithValue("@p4", TextBox4.Text);
    cmd.Parameters.AddWithValue("@p5", TextBox5.Text);
    cmd.Parameters.AddWithValue("@p6", TextBox6.Text);
    cmd.Parameters.AddWithValue("@p7", TextBox8.Text);
    cmd.Parameters.AddWithValue("@p8", TextBox12.Text);
    cmd.Parameters.AddWithValue("@p9", TextBox9.Text);
    cmd.Parameters.AddWithValue("@p10", TextBox11.Text);
    cmd.ExecuteNonQuery();
    con.Close();
}

}

And my mc access table has the following structure...

ID First_Name Last_name Phone_No Username Password Email Address City Country Zipcode

Can someone please help me? :) Thank you :)

1.The connection was not closed. The connection's current state is open. — user2211486
– user2211486, Commented Mar 29, 2013 at 13:51

Zeeshan Anjum · Accepted Answer · 2013-03-29 14:20:48Z

2

You should use this query:

  string str = "insert into User_Registeration (First_Name, Last_name, Phone_No, [Username], [Password], [Email], [Address], City, Country, Zipcode)" +
     " values (@p1, @p2, @p3,@p4, @p5,@p6, @p7,@p8,@p9,@p10)";

edited Mar 29, 2013 at 14:20

answered Mar 29, 2013 at 13:56

Zeeshan Anjum

4511 gold badge5 silver badges22 bronze badges

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

8 Comments

Zeeshan Anjum Over a year ago

check if cmd.ExecuteNonQuery() >0 then your data is inserted.

user2211486 Over a year ago

Still an error this time its different - "Number of query values and destination fields are not the same."

user2211486 Over a year ago

@ Zeeshand Anjum Hi :) How do i check that? Could you please tell me? Am really new to all this >.<

grahamj42 Over a year ago

user2107703 has put @p8 twice in the string

user2211486 Over a year ago

Yea there was @p8 twice! Thanks it worked! Thank you :) But the other error that i get sometimes "The connection was not closed. The connection's current state is open." Is there something that i can do about that? It doesn't come every time it comes nonetheless..

|

Steve · Accepted Answer · 2013-03-29 14:08:50Z

1

The problem is caused by the word PASSWORD. This word is a reserved word in JET (MS-Access)
To use that word in your sql commands you need to encapsulate it with square brackets.

Of course when you add parameters to your query you should be sure to add the exact number of parameters expected by the placeholders in your query.
You have 10 placeholders (?) so you need 10 parameters and in the exact order expected by the respective fields

So, summarizing

string str = "insert into User_Registeration (First_Name, Last_name, Phone_No, " + 
             "Username, [Password], Email, Address, City, Country, Zipcode)" +
             "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

answered Mar 29, 2013 at 14:08

Steve

217k22 gold badges242 silver badges296 bronze badges

1 Comment

user2211486 Over a year ago

Yup got it! Thanks a lot! Btw thanks for helping earlier too with that question i had asked :)

Laurent S. · Accepted Answer · 2013-03-29 13:52:36Z

0

You're missing a space in this line :

string str = "insert into User_Registeration (First_Name, Last_name, Phone_No, Username, Password, Email, Address, City, Country, Zipcode)" +
     "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

should be :

string str = "insert into User_Registeration (First_Name, Last_name, Phone_No, Username, Password, Email, Address, City, Country, Zipcode)" +
     " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

answered Mar 29, 2013 at 13:52

Laurent S.

6,9373 gold badges32 silver badges42 bronze badges

1 Comment

user2211486 Over a year ago

Still getting the same error "Syntax error in INSERT INTO statement." pointing at cmd.ExecuteNonQuery();

Collectives™ on Stack Overflow

Syntax error in INSERT INTO Statement while trying to input values in the Database

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related