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'."

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
– Aniket Inge, Commented Jan 26, 2013 at 13:11
Also, please use cmd.Parameters.AddWithValue() instead of the deprecated cmd.Parameters.Add() — Aniket Inge
– Aniket Inge, Commented Jan 26, 2013 at 13:11

John Woo · Accepted Answer · 2013-01-26 13:52:51Z

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

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.
        }
    }
}

Try use stored procedures and input validation will save you headaches down the line.

Community · Accepted Answer · 2017-05-23 11:46:52Z

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

Collectives™ on Stack Overflow

sql insert into asp.net

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related