CODEWITHSUNDEEP
c#asp.netdatabasems-access
user2211486
user2211486

Reputation: 237

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

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 :)

Upvotes: 0

Views: 1481

Answers (3)

Zeeshan Anjum
Zeeshan Anjum

Reputation: 450

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)";

Upvotes: 2

Steve
Steve

Reputation: 216263

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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Upvotes: 1

Laurent S.
Laurent S.

Reputation: 6946

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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

Upvotes: 0

Related Questions