Click here to Skip to main content
15,867,282 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hey. I have written some code for reading a logon from a database in SQL server. So far it works however there is one line that wont work which is the ExecuteReader. More on the comment below.

        private void btnLogon_Click(object sender, EventArgs e)
        {
            try
            {

                SqlConnection dbConnection = new SqlConnection("Data Source=(local); Initial Catalog = User; Integrated Security = True; Pooling = False");
                dbConnection.Open();

                SqlCommand sqlCommand = new SqlCommand("SELECT User, Password FROM [dbo].[User]");
                sqlCommand.Connection = dbConnection;

                SqlDataReader row;

                row = sqlCommand.ExecuteReader(CommandBehavior.SingleResult);

                while (row.Read())
                {
                    if (tbxUserID.Text == row.GetString(0) && tbxPassword.Text == row.GetString(1))
// Basically the issue is here. 'button1' dosent become enabled and its all down to the code above. Is this the code or SQL? Would much appreciate an explination or help. Thanks :)
                    {
                        button1.Enabled = true;
                    }
                    else
                    {
                        button1.Text = ("True");
                    }

                }

                dbConnection.Close();

                sqlCommand.Dispose();

                row.Close();
            }

            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "Database Problem", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
Posted

Well let us dissect your code. You are executing
SELECT User, Password FROM [dbo].[User]

Which means it will return all users and their respective password. Then you limit your choice to single one by executing
sqlCommand.ExecuteReader(CommandBehavior.SingleResult);


When your reach if condition what makes you think the single record your read is going to match what is your input?

Try to change your sql query to
"SELECT User, Password FROM [dbo].[User] where User = '" + tbxUserID.Text + "' AND Password = '" + tbxPassword.Text + "'"
 
Share this answer
 
v2
Comments
WurmInfinity 1-Mar-11 11:24am    
"Incorrect syntax near 'Password' :S
Yusuf 1-Mar-11 11:28am    
That should not be very hard to fix. See updated answer.
musefan 1-Mar-11 11:33am    
woah, SQL Injection warning!!!
Yusuf 1-Mar-11 11:49am    
Chill out man. I know, I know. The OP seems to struggle with the very basic SQL, let alone to know what SQL injection is. All I was trying to do was show him why his code did not work.

I know it was lousy code and should never go into production. But I don't see any harm as a teaching tool. Once he grasps with the basics of SQL then introduce him to advanced topics.
musefan 1-Mar-11 11:56am    
And where will you be when he IS ready for advanced techniques? This is the opportunity to explain why it is bad, before he has to find out the hard way (or worse, never find out at all)
Does your code crash? try putting in some debug messages. Perhaps using MessageBox.Show();

trying checking the HasRows property before the while loop. then in the loop try showing the values of columns 1 and 2 to see if they are what you expect...

MessageBox.Show(row.HasRows.ToString());
while(row.Read())
{
   MessageBox.show(row.GetString(0) + " " + row.GetString(1));


are the values what you expect?

you should also consider using a WHERE clause in your sql query in order to search for the specific username and password - then you can enable the button based on if the result 'HasRows'

button1.Enabled = row.HasRows;


don't forget to use command parameters (search Google if needed) when you use textbox values to create an SQL where clause (see SQL Injection)

IMPORTANT: While typing the below code I realised your problem is probably the fact that you are setting CommandBehavior.SingleResult in your ExecuteReader call that means you are only returning the first result. change this to...

row = sqlCommand.ExecuteReader();


Suggested code...

SqlConnection connection; 

try{
     connection = new SqlConnection("...");//should get this from a config file
     SqlCommand cmd = new SqlCommand("SELECT ID FROM [dbo].[User] WHERE User = @0 AND Password = @1");//Consider using Username in stead of User in database
     cmd.Parameters.Add("@0", OleDbType.Char).Value = tbxUserID.Text;
     cmd.Parameters.Add("@1", OleDbType.Char).Value = tbxPassword.Text;

     SqlDataReader reader = cmd.ExecuteReader();

     button1.Enabled = reader.HasRows;

}catch(Exception ex){
     MessageBox.Show(ex.Message, "Database Problem", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally{
     if(connection != null)
          connection.Close();
}
 
Share this answer
 
v2
Comments
WurmInfinity 1-Mar-11 11:25am    
Ok it shows, Dbo, Guest. Which is not what I want to receive, so now i'm even more confused.
WurmInfinity 1-Mar-11 11:35am    
Thanks for the help got it sorted now :) all down to my own stupidity.
Try this
VB
if (tbxUserID.Text.Trim() == row.GetString(0).Trim() && tbxPassword.Text.Trim() == row.GetString(1).Trim())
 
Share this answer
 
Comments
WurmInfinity 1-Mar-11 11:18am    
Still not working :(
Piccadilly Yum Yum 1-Mar-11 11:21am    
Ahh ! use Equals() instead of '='
WurmInfinity 1-Mar-11 11:28am    
still not doing the trick. hmmmmmm

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900