Syntax error with inserting sql into access db using C# and asp.net

Question

I get an error on the line

cmd.ExecuteNonQuery();

which says

Syntax error in INSERT INTO statement

I think my query should be valid, I don't understand the problem and I've been working on it for hours. It's in the right order and putting [] around Users did not work for me, would appreciate help.

<%@ Page Language ="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>   
    <script runat="server">
    protected void Page_Load()
    {

            String username = Request.Form["username"];
            String email = Request.Form["email"];
            String password = Request.Form["password1"];
            var age = Request.Form["age"];
            String country = Request.Form["country"];
            String hobbie = Request.Form["skin"];
            String sql;

            sql = "INSERT INTO Users(UserName, Email, Password, Age, Country, Hobbie) VALUES('" + username + "','" + email + "','" + password + "'," + age + ",'" + country + "','" + skin + "')";

            String Path = Server.MapPath("App_Data/Users.accdb");
            String connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Path;

            OleDbConnection conn = new OleDbConnection(connStr);
            conn.Open();

            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.ExecuteNonQuery();

            conn.Close();
    }

Maybe try using String.Format instead of string concatenation with + (so it's more clear if there are errors). Also, if you can, try outputting the values of username, email, password... so you can see if they are malformed. SQL injection isn't a pretty thing. — Luka Horvat
– Luka Horvat, Commented Apr 26, 2014 at 10:33

Steve · Accepted Answer · 2014-04-26 10:17:51Z

4

PASSWORD is a reserved keyword for MS-Access. Use it enclosed in square brackets

    sql = "INSERT INTO Users(UserName, Email, [Password] ....

But please, change that sql text to use a parameterized query. Also, if you are able to send this command with the actual values your query remains open to Sql Injection vulnerability and a parsing problem could arise when your input string contains a single quote

   sql = @"INSERT INTO Users(UserName, Email, [Password], Age, Country, Hobbie) 
           VALUES(?,?,?,?,?,?)";
   ....

   OleDbCommand cmd = new OleDbCommand(sql, conn);
   cmd.Parameters.AddWithValue("@p1", username);
   .... and so on for the other 5 parameters required by the query ....

answered Apr 26, 2014 at 10:17

Steve

217k22 gold badges242 silver badges296 bronze badges

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

2 Comments

marc_s Over a year ago

+1 for recommending - once again - to use parametrized queries instead of slapping together a SQL statement!

TG Person Over a year ago

Thanks alot ! it worked, and this is a mini project for school so I kind of work how they tell me to and my knowledge is very limited in the subject

Vignesh Kumar A · Accepted Answer · 2014-04-26 10:26:28Z

I think Password is a reserved word. use like [Password]

Try this

sql = "INSERT INTO Users(UserName,Email,[Password],Age,Country,Hobbie) VALUES('" + username + "','" + email + "','" + password + "'," + age + ",'" + country + "','" + skin + "')";

And this is not recommended. Use parameterized query. like below:

 sql = "INSERT INTO Users(UserName, Email, [Password], Age, Country, Hobbie) 
           VALUES(@UserName,@Email,@Password,@Age,@Country,@Hobbie)";

   OleDbCommand cmd = new OleDbCommand(sql, conn);
   cmd.Connection = conn;
   cmd.CommandType = CommandType.Text;   
   cmd.Parameters.AddWithValue("@UserName", username);
   cmd.Parameters.AddWithValue("@Email", Email);
   cmd.Parameters.AddWithValue("@Password", Password);
   cmd.Parameters.AddWithValue("@Age", Age);
   cmd.Parameters.AddWithValue("@Country", Country);
   cmd.Parameters.AddWithValue("@Hobbie", Hobbie);

KarthikManoharan · Accepted Answer · 2014-04-26 10:22:58Z

1

use [Password] instead of Password

answered Apr 26, 2014 at 10:22

KarthikManoharan

8035 silver badges12 bronze badges

Collectives™ on Stack Overflow

Syntax error with inserting sql into access db using C# and asp.net

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related