sql insert into asp.net

Question

con.Open();
SqlCommand cmd=new SqlCommand("INSERT INTO user(Firstname,Lastname,Email,Pass,Type)
 values(@first,@last,@email,@pass,@type)",con);
cmd.Parameters.Add("@first",SqlDbType.NVarChar).Value = txtfirst.Text;
cmd.Parameters.Add("@last",SqlDbType.NVarChar).Value = txtlast.Text;
cmd.Parameters.Add("@email",SqlDbType.NVarChar).Value = txtemail.Text;
cmd.Parameters.Add("@pass",SqlDbType.NVarChar).Value = txtpass.Text;
cmd.Parameters.Add("@type",SqlDbType.NVarChar).Value = "customer";
cmd.ExecuteNonQuery();
con.Close();

what is the problem with my syntax it says "Incorrect syntax near the keyword 'user'."

• Aniket Inge: show us your connection string. Nothing wrong with the SQL. Seems like your connection string isn't set correctly to point to a particular DB
• Aniket Inge: Also, please use cmd.Parameters.AddWithValue() instead of the deprecated cmd.Parameters.Add()
• Aniket Inge: and yes user is a keyword. JW. gave the solution

Answer 1

you should escape the table name user with delimited identifiers,

SqlCommand cmd=new SqlCommand("INSERT INTO [user] (Firstname,Lastname,Email,Pass,Type) values(@first,@last,@email,@pass,@type)",con);

• SQL Server Reserved Keywords
• SQL Server Delimited Identifiers

UPDATE 1

Refractor your code by

• using using statement to properly dispose objects
• using Try-Catch block to properly handle exceptions

code snippet:

string _connStr = "connectionString here";
string _query = "INSERT INTO [user] (Firstname,Lastname,Email,Pass,Type) values (@first,@last,@email,@pass,@type)";
using (SqlConnection conn = new SqlConnection(_connStr))
{
 using (SqlCommand comm = new SqlCommand())
 {
 comm.Connection = conn;
 comm.CommandType = CommandType.Text;
 comm.CommandText = _query;
 comm.Parameters.AddWithValue("@first", txtfirst.Text);
 comm.Parameters.AddWithValue("@last", txtlast.Text);
 comm.Parameters.AddWithValue("@email", txtemail.Text);
 comm.Parameters.AddWithValue("@pass", txtpass.Text);
 comm.Parameters.AddWithValue("@type", "customer");
 try
 {
 conn.Open();
 comm.ExecuteNonQuery();
 }
 catch(SqlException ex)
 {
 // other codes here
 // do something with the exception
 // don't swallow it.
 }
 }
}

• AddWithValue
• Add (recommended one)

• Laird Streak: Try use stored procedures and input validation will save you headaches down the line.

Answer 2

USER is a reserved keyword on SQL Server.

You should use your table name with brackets [] like;

INSERT INTO [user]

You can try like;

con.Open();
SqlCommand cmd=new SqlCommand("INSERT INTO [user] (Firstname,Lastname,Email,Pass,Type) values(@first,@last,@email,@pass,@type)",con);
cmd.Parameters.AddWithValue("@first", txtfirst.Text);
cmd.Parameters.AddWithValue("@last", txtlast.Text);
cmd.Parameters.AddWithValue("@email", txtemail.Text);
cmd.Parameters.AddWithValue("@pass", txtpass.Text);
cmd.Parameters.AddWithValue("@type", "customer");
cmd.ExecuteNonQuery();
con.Close();

And also like @JW said, it is always a good approach to using them in a try-catch statement.

• Best Practices of Exception Management