Click here to Skip to main content
15,112,232 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 + "'"
   
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)
WurmInfinity 1-Mar-11 11:35am
   
I'm a total pillock xD. Thanks a lot! It works now, however I think my biggest issue was the fact the column for Username was called UserName and not user. Appended code was ""SELECT UserName, Password FROM [dbo].[User] where UserName = '" + tbxUserID.Text + "' AND Password = '" + tbxPassword.Text + "'". Thanks!!! :D
musefan 1-Mar-11 11:41am
   
don't concatenate strings in an SQL query (this is very bad and known as "SQL injection" - google it!), see my solution for using command parameters
WurmInfinity 1-Mar-11 11:42am
   
Ok cheers man.
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();
}
   
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())
   
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month



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