-2

I'm trying to count the number of user so that I can generate the unique id for new user. But the query which I have written shows some syntax error near User where User is my Table name.

private int GenerateAutoId()
{
    con.Open();
    SqlCommand cmd = new SqlCommand("Select Count(*) from User",con);
    var temp = cmd.ExecuteScalar().ToString();
    int i = Convert.ToInt32(temp);
    con.Close();
    i=i+1;
    return i;
}

Error Message:

[SqlException (0x80131904): Incorrect syntax near the keyword 'User'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action'1 wrapCloseInAction) +2555926
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action'1 wrapCloseInAction) +5959200 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4169
System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58
System.Data.SqlClient.SqlDataReader.get_MetaData() +89
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +430
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2598
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +1483
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +64
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +240
System.Data.SqlClient.SqlCommand.ExecuteReader() +99
SignUpPage_SignUp.GenerateAutoId() in d:\SEM-4\WT\ProjectStayHealthy\StayHealthy\templates.aucreative.co\Project\SignUpPage\SignUp.aspx.cs:22 SignUpPage_SignUp.SignUp_Click(Object sender, EventArgs e) in d:\SEM-4\WT\ProjectStayHealthy\StayHealthy\templates.aucreative.co\Project\SignUpPage\SignUp.aspx.cs:31 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9782698
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +204
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1639

halfer
  • 19,824
  • 17
  • 99
  • 186
Namrata Sanger
  • 105
  • 1
  • 1
  • 11
  • 2
    why do you generate you own "auto id" when you can have the database do it for you? – Progman May 05 '19 at 13:43
  • The SQL Server can have more than one database. The default database my be different from the you code which doesn't have a User Table. The database may not be working so I would use SQL Server Management Studio and check if database is working. – jdweng May 05 '19 at 13:43
  • @jdweng: That wouldn't cause the error message the OP is seeing. – Jon Skeet May 05 '19 at 13:44
  • When creating objects, using aliases, etc, you should really avoid the use of [Reserved Keywords](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017). You have to quote them otherwise, and code that doesn't quote them will not work. – Thom A May 05 '19 at 13:49
  • 1
    @Progman is right. What you're doing here seems like really you should be using an `IDENTITY` column. The solution doesn't even stop reuse of an `ID`. Say you have 10 Users (ID's 1-10), then delete User 7. You then try to create a new user, you now have another User with the ID 10. – Thom A May 05 '19 at 13:53

1 Answers1

2

The hint is in the error message, but you do need to read it carefully:

Incorrect syntax near the keyword 'User'

User is a reserved word in T-SQL so you need to escape it in your query:

using (SqlCommand cmd = new SqlCommand("Select Count(*) from [User]", con))
{
    // Note: avoid converting to and from strings unnecessarily.
    return (int) cmd.ExecuteScalar();
}

(Alternatively, change the table name to a non-reserved word if you can, of course...)

The using statement is to dispose of the command after you've finished using it - it's not the cause of the problem you're seeing, but it's still a good idea. It looks like you also have a single connection which you're repeatedly opening and closing. It's generally a better idea to create a new SqlConnection on each call and again use a using statement to close it... let the connection pooling infrastructure make sure that's efficient.

As noted in comments, using the count of a table isn't a good idea for generating IDs, either. Let the database do that for you.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194