sql insert into asp.net
24,813
Solution 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);
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.
}
}
}
Solution 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.
Related videos on Youtube
Comments
-
Reynan over 4 years
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 Ingeand yes
user
is a keyword. JW. gave the solution -
Aniket IngeAlso, please use
cmd.Parameters.AddWithValue()
instead of the deprecatedcmd.Parameters.Add()
-
-
Laird Streak over 11 yearsTry use stored procedures and input validation will save you headaches down the line.