C# Syntax Error Near Table Name

Question

I am having some problem when trying to check login credential for 3-tier project in C#.

Currently, I have a table named User with userName and password columns.

In my BusinessLogicLayer, I get the user input and pass them to dataAccessLayer:

public string checkCredential(string userName, string password)
{
    string returnMessage = "";
    User user = new User(userName, password);
    Boolean success = user.checkCredential();
    if (!success)
    {
        returnMessage += "Username and password does not match!";
    }
    else
    {
        returnMessage = "";
    }
    return returnMessage;
}

In my Data Access Layer, I got a method to check for login creddential:

public Boolean checkCredential()
{
    Boolean result = false;

    using (var connection = new SqlConnection(FFTHDb.connectionString)) // get your connection string from the other class here
    {
        SqlCommand command = new SqlCommand("SELECT userName, password FROM User WHERE userName = '" + userName + "' AND password = '" + password + "'", connection);
        connection.Open();
        using (var dr = command.ExecuteReader())
        {
            if (dr.Read())
            {
                result = true;
            }
        }
    }
    return result;
}

And I got a separated class to set the connection string:

public static string connectionString = DataAccessLayer.Properties.Settings.Default.DBConnStr;


public static SqlDataReader executeReader(string query)
{
    SqlDataReader result = null;

    System.Diagnostics.Debug.WriteLine("FFTHDb executeReader: " + query);

    SqlConnection connection = new SqlConnection(connectionString);
    SqlCommand command = new SqlCommand(query, connection);
    connection.Open();
    result = command.ExecuteReader();
    connection.Close();

    return result;
}

There is no compilation errors. And I double checked for the table name and columns in database. However, it just keeps telling me that there is syntax error near User. I wonder why is it so.

Thanks in advance.

score 4 · Accepted Answer · answered Dec 12 '13 at 12:26

4

User is a reserved keyword on T-SQL. You should use it with square brackets like [User]

Also using parameterized queries always a good practice.

And Never store passwords in plain text! Use SHA-512 hash.

answered Dec 12 '13 at 12:26

Soner Gönül

97,193
102
206
364

score 0 · Answer 2 · answered Dec 12 '13 at 12:25

0

User is a reserved keyword so you need to add square brackets around it. For a list see here. So, you should do it like this

SELECT userName, password FROM [User] WHERE userName =

answered Dec 12 '13 at 12:25

Ehsan

31,833
6
56
65

Sudhakar Tillapudi · Answer 3 · 2013-12-12T12:26:42.877

-2

Problem : the table name which you have provided is User is a Keyword in Transact-SQL.
Reserved Words

Solution: Enclose the reserved word User in square brackets [].

Solution 1:

SqlCommand command = new SqlCommand("SELECT userName, password FROM [User] WHERE userName = '" + userName + "' AND password = '" + password + "'", connection);

Solution 2:

    SqlCommand command = new SqlCommand("SELECT userName, password FROM [User] WHERE userName= @username AND password = @password", connection);
    command.Parameters.AddWithValue("@username",userName);
    command.Parameters.AddWithValue("@password",password);

edited Dec 12 '13 at 12:26

answered Dec 12 '13 at 12:21

Sudhakar Tillapudi

25,935
5
37
67

It works. But why do we need to add the square bracket? – Dec 12 '13 at 12:22
2

Explain your answers. – Ash Burlaczenko Dec 12 '13 at 12:22
I kind of get it already. Is it true that we always needed to surround table name with [] in asp.net? – Dec 12 '13 at 12:25
@Downvoter would be glad if you could improve this. – Sudhakar Tillapudi Dec 12 '13 at 12:29
@AshBurlaczenko: pleae check my explanantion. – Sudhakar Tillapudi Dec 12 '13 at 12:31
@Gwen: no need to always keep table names in square brackets , but you should enclose them if it is a reserved word. – Sudhakar Tillapudi Dec 12 '13 at 12:31

C# Syntax Error Near Table Name

3 Answers3

Linked