How do I execute SQL command

Question

0.00/5 (No votes)

See more:

I am writing a login function for an application and I am getting the

C#

Incorrect syntax near the keyword 'USER'. at System.Data.SqlClient.SqlConnection

This is the code for when the login button is clicked

C#

protected void loginButton_Click(object sender, EventArgs e)
     {
         try
         {

             dbConnection.Open();
             SqlCommand cmd = new SqlCommand("SELECT * FROM USER WHERE EmailAddress = @EmailAddress and Password = @Password", dbConnection);
             cmd.Parameters.AddWithValue("@EmailAddres", tbEmail.Text);
             cmd.Parameters.AddWithValue("@Password", tbPass.Text);
             SqlDataAdapter da = new SqlDataAdapter(cmd);
             DataTable dt = new DataTable();
             da.Fill(dt);
             if (dt.Rows.Count > 0)
             {
                 Response.Redirect("Default.aspx");
             }
             else
             {
                 lblText.Text = "WRONG USERNAME OR PASSWORD";
                
             }

         }

         catch (SqlException en)
         {
             lblText.Text = en.ToString();
         }
         finally
        {
             dbConnection.Close();
         }

     }

What I have tried:

Google searches have not been helpful.

Posted 14-Jun-16 10:46am

KevinClaassens

Updated 14-Jun-16 11:56am

Add a Solution

Comments

KevinClaassens 14-Jun-16 17:48pm

The connection string is in the web.config. It is as follows..
<connectionstrings>
<add name="dbConnection"
="" connectionstring="server=KEVIN-PC;Integrated Security=true;database=XCellIT;">

PIEBALDconsult 14-Jun-16 18:19pm

Try [USER]

3 solutions

Solution 3

The user is a reserved keyword you could try using it in square brackets and it should work

select * from [user]

Posted 14-Jun-16 11:56am

SSunayana

Solution 1

One thing it appears typo at EmailAddress at(cmd.Parameters.AddWithValue(@EmailAddres, tbEmail.Text);) where s is missing.
Also if possible try to run same query in SQL management studio

Posted 14-Jun-16 11:35am

Member 12076824

Comments

KevinClaassens 14-Jun-16 17:50pm

I still get the same error in SQL management studio

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2016-06-14T11:53:00

USER is a reserved keyword in SQL:
Reserved Keywords (Transact-SQL)[^]

You either need to change your table name to something else, or enclose it in square brackets:

SQL

SELECT * FROM [USER] WHERE ...

You also don't need to select all columns and load a DataTable just to test whether a row exists:

C#

using (SqlCommand cmd = new SqlCommand("SELECT 1 FROM USER WHERE EmailAddress = @EmailAddress and Password = @Password", dbConnection))
{
    cmd.Parameters.AddWithValue("@EmailAddres", tbEmail.Text);
    cmd.Parameters.AddWithValue("@Password", tbPass.Text);
    
    object result = cmd.ExecuteScalar();
    if (result == null || Convert.IsDBNull(result))
    {
        lblText.Text = "WRONG USERNAME OR PASSWORD";
    }
    else
    {
        Response.Redirect("Default.aspx");
    }
}

You're storing passwords as plain-text. That's a security vulnerability waiting to happen. You should only ever store a salted hash of the user's password:
Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

And why are you re-inventing the wheel? ASP.NET has several perfectly good authentication systems available already - for example:
ASP.NET Identity | The ASP.NET Site[^]
BrockAllen.MembershipReboot[^]
Introduction to Membership[^]