C# sql插入asp.net

sql insert into asp.net

提问by Reynan

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

我的语法有什么问题，它说“关键字'user'附近的语法不正确。”

回答by John Woo

you should escape the table name userwith delimited identifiers,

您应该user使用分隔标识符对表名进行转义，

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

• SQL Server 保留关键字
• SQL Server 分隔标识符

UPDATE 1

更新 1

Refractor your code by

折射你的代码

• using usingstatement to properly dispose objects
• using Try-Catchblock to properly handle exceptions

• usingusing语句正确处理对象
• 使用Try-Catch块正确处理异常

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)

• 添加值
• 添加（推荐一个）

回答by Soner G?nül

USERis a reserved keywordon SQL Server.

USER是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 @JWsaid, it is always a good approach to using them in a try-catch statement.

而且就像@JW所说的那样，在 try-catch 语句中使用它们总是一种好方法。

• Best Practices of Exception Management

• 异常管理最佳实践