Donovan Kight Donovan Kight - 4 years ago 200
SQL Question

sql command issue for database login

I am attempting to create a login form using a username and password from a database table named "Table". I have watched several videos and looked at several other pages and can not seem to get the query to run correctly. My second try/catch block shows the message box "could not run query". Will someone look at my code to see what is wrong, please and thank you.

SqlConnection con = new SqlConnection();
string connectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Donovan\Documents\Work\Time Clock program\Time Clock program1.2\Time Clock program\Database1.mdf;Integrated Security=True;Connect Timeout=30;";
string query = "SELECT Count(*) FROM [Table] WHERE Username='" + usernameTextBox.Text +
"' AND Password = '" + this.passwordTextBox.Text + "'";
try
{
con = new SqlConnection(connectionString);
}
catch(Exception ex)
{
MessageBox.Show("Could not connect to Database");
MessageBox.Show(ex.Message);
}

try
{
if (!(usernameTextBox.Text == string.Empty))
{
if (!(passwordTextBox.Text == string.Empty))
{
SqlCommand cmd = new SqlCommand(query, con);
SqlDataReader dbr;
con.Open();
dbr = cmd.ExecuteReader();
int count = 0;
while (dbr.Read())
{
count = count + 1;
}
if (count == 1)
{
MessageBox.Show("username and password is correct");
}
else if (count > 1)
{
MessageBox.Show("Duplicate username and password", "login page");
}
else
{
MessageBox.Show(" username and password incorrect", "login page");
}
}
else
{
MessageBox.Show(" password empty", "login page");
}
}
else
{
MessageBox.Show(" username empty", "login page");
}
// con.Close();

}
catch(Exception ex)
{
MessageBox.Show(ex.Message);

}

Answer Source

My money is for Table is a reserved keyword in TSQL. You might wanna use it with [Table] instead. As a better way, change your table name to non-reserved word.

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Do not store your passwords as a plain text. Read Best way to store password in database

Use using statement to dispose your connection and command automatically instead of calling Close or Dispose methods manually.

By the way, I strongly suspect you may wanna use SELECT COUNT(*).. with ExecuteScalar method since you don't do anything with other things.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download