Reputation: 323
Working on building a simple .NET web application using a SQL Server table created. I continuely get errors in regards to the SqlDataReader, and am stuck on where I'm going wrong.
Here is my error: Additional information: Incorrect syntax near the keyword 'Table'.
Here is my code:
EDIT:
bool authenticated = AuthenticateMe(txtUsername.Text, txtPassword.Text);
if (authenticated)
{
Response.Redirect("Home.aspx");
}
else
{
Response.Redirect("Default.aspx");
}
}
private bool AuthenticateMe(string username, string password)
{
// string ErrorMessage = "";
string connectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;Connect Timeout=30";
string commandText = "SELECT Username from [Table] where Username = @name AND Password = @pwd";
// try
// {
using (SqlConnection sqlConnection1 = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(commandText, sqlConnection1))
{
sqlConnection1.Open();
cmd.Parameters.AddWithValue("@name", username);
cmd.Parameters.AddWithValue("@pwd", password);
int result = (int)cmd.ExecuteNonQuery();
if (result > 0)
{
return true;
}
else
{
return false;
}
}
}
1st Version (prior to edit):
protected void bnLogin_Click(object sender, EventArgs e)
{
bool authenticated = AuthenticateMe(txtUsername.Text, txtPassword.Text);
if (authenticated)
{
Response.Redirect("Home.aspx");
}
else
{
Response.Redirect("Default.aspx");
}
}
private bool AuthenticateMe(string userName, string password)
{
string connectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;Connect Timeout=30";
SqlConnection sqlConnection1 = new SqlConnection(connectionString);
sqlConnection1.Open();
SqlCommand cmd = new SqlCommand("SELECT Username from Table where Username = userName");
cmd.Connection = sqlConnection1;
SqlDataReader reader = cmd.ExecuteReader();
Response.Write("Entered Sucessfully");
reader = cmd.ExecuteReader();
string localUserName = (string)reader["Username"];
sqlConnection1.Close();
if (userName.Equals(localUserName))
{
return true;
}
else
{
return false;
}
Upvotes: 1
Views: 2758
Reputation: 216303
Your AuthenticateMe method seems a bit wrong and ineffective to authenticate the user
So you could rewrite the code in this way
private bool AuthenticateMe(string userName, string password)
{
string connectionString = @".....";
string commandText = "SELECT COUNT(*) from [Table] where Username = @name AND Pass = @pwd");
using(SqlConnection sqlConnection1 = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand(commandText, sqlConnection1))
{
sqlConnection1.Open();
cmd.Parameters.AddWithValue("@name", username);
cmd.Parameters.AddWithValue("@pwd", password);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return (result > 0);
}
}
Also, keep in mind that is considered a bad practice to store the passwords in the database in plain text. Some kind of hash function should be applied to the password memorized to forbid any security problem if someone get a copy of the database.
Upvotes: 1
Reputation: 980
I think there are 2 issues with your SQL query.
"SELECT Username from Table where Username = userName"
The last part, Username = username, is also wrong. If your intention was to have a constant string there, you should consider putting the username in quotes \'username\'. Don't forget about the escape symbol. And if you want to pass a parameter to the SQLCommand, use @username in the query and pass the value this way
cmd.Parameters["@username"].Value = "Bob";
Upvotes: 1
Reputation: 11216
Table is a reserved keyword in SQL. Try putting square brackets around it:
SqlCommand cmd = new SqlCommand("SELECT Username from [Table] where Username = userName");
Upvotes: 3
Reputation: 1063078
Table is a keyword. If your table is called Table
, your sql must escape it. Try [Table]
.
Note also that you'll want to use a parameter for the username - i.e. where Username = @userName
, where you also add a parameter with that name to the command with the appropriate value.
Upvotes: 2