asp.net sql error running a select statement

Question

i follow the tutorial from youtube writing a code of login & registration but got errors.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class Registration : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["earchConnectionString"].ConnectionString);
            con.Open();
            string cmdStr = "Select count(*) from user where UserName='" + TextBoxUN.Text + "'";
            SqlCommand userExist = new SqlCommand(cmdStr, con);
            int temp = Convert.ToInt32(userExist.ExecuteScalar().ToString());
            con.Close();
            if (temp == 1)
            {
                Response.Write("User Name Already Exist....<br /> Please Choose Another User Name.");
            }
        }
        
    }
    protected void Submit_Click(object sender, EventArgs e)
    {
        /*if (IsPostBack)
        {
            Response.Write("You have successfully registered");
        }*/
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["earchConnectionString"].ConnectionString);
        con.Open();
        string insCmd = "Insert into user (UserName, Password, EmailAddress, FullName, level) values (@UserName,@Password,@EmailAddress, @FullName, @level)";
        SqlCommand insertUser = new SqlCommand(insCmd, con);
        insertUser.Parameters.AddWithValue("@UserName", TextBoxUN.Text);
        insertUser.Parameters.AddWithValue("@Password", TextBoxPass.Text);
        insertUser.Parameters.AddWithValue("@EmailAddress", TextBoxEA.Text);
        insertUser.Parameters.AddWithValue("@FullName", TextBoxFN.Text);
        insertUser.Parameters.AddWithValue("@level", level.SelectedItem.ToString());

        try
        {
            insertUser.ExecuteNonQuery();
            con.Close();
            Response.Redirect("Login.aspx");
        }
        catch (Exception er)
        {
            Response.Write("Something wrong");
        }
        finally
        {
            //Any Special Action You Want To Add
        }

    }
}

but there is error messages:

Incorrect syntax near the keyword 'user'.

Description: An unhandled exception occurred during the execution of the current web request. > Please review the stack trace for more information about the error and where it originated in > the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'user'.

Source Error:

Line 18: string cmdStr = "Select count(*) from user where UserName='" + TextBoxUN.Text + "'"; Line 19: SqlCommand userExist = new SqlCommand(cmdStr, con); Line 20: int temp = Convert.ToInt32(userExist.ExecuteScalar().ToString()); Line 21: con.Close(); Line 22: if (temp == 1)

Source File: c:\inetpub\web1\Registration.aspx.cs Line: 20

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near the keyword 'user'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2042118
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5043644
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2294
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +86
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteScalar() +139
Registration.Page_Load(Object sender, EventArgs e) in c:\inetpub\web1\Registration.aspx.cs:20
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35 System.Web.UI.Control.OnLoad(EventArgs e) +91
System.Web.UI.Control.LoadRecursive() +74
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

What's the problem?

ethorn10 ethorn10 · Accepted Answer · 2012-10-10T02:31:38

User is a reserved keyword. Place it in square brackets and you should be good.

select count(*) from [user]

asp.net sql error running a select statement

2 Answers