error in select then begining with update statement

Question

5.00/5 (2 votes)

See more:

i am a newbie and i am making a update password field in windows form but the error is coming,
firstly user will enter old password and if old password is correct then the new password will be updated but my password is not updating
i used my login code and then updation code.but the password is not updating

private void button1_Click(object sender, EventArgs e)
       {

           SqlConnection con = new SqlConnection();
           con.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\project\sample project\prject xample2 perfect\login\Database1.mdf;Integrated Security=True;User Instance=True";
           try
           {
               con.Open();
               string qry1 = "Select * from Table1 WHERE password = @password COLLATE SQL_Latin1_General_CP1_CS_AS and Username=@Username COLLATE SQL_Latin1_General_CP1_CS_AS";
               SqlCommand com = new SqlCommand(qry1, con);
               com.Parameters.AddWithValue("@Username", this.usernamelabel.Text); // here is the login username
               com.Parameters.AddWithValue("@Password", this.textBox1.Text);
               SqlDataReader dr = com.ExecuteReader(); // here is the login password i.e old password
               while (dr.Read())
               {
                   if (dr.HasRows == true)
                   {
                       MessageBox.Show("Login Successfull");
                       string qry2 = "UPDATE Table1 SET Password =@Password WHERE username=@username";
                       SqlCommand comm = new SqlCommand(qry2,con);
                       comm.Parameters.AddWithValue("@username", this.usernamelabel.Text); // here is the username
                       comm.Parameters.AddWithValue("@Password", this.textBox2.Text); // here is the updated password textbox
                   }
               }
               if (dr.HasRows == false)
               {
                   MessageBox.Show("Access Denied \n" + "no " + textBox1.Text + "named username is present \n" + "or your passwrod " + textBox2.Text + " is incorrect", "ERROR in Loggin");
               }
           }
           catch (Exception)
           {
               MessageBox.Show("Error with the databse connection");
           }

           con.Close();
       }

Posted 27-Nov-12 23:12pm

shaikh-adil

Add a Solution

Comments

pryashrma 28-Nov-12 5:21am

does it generates any error... include exception message in catch

shaikh-adil 28-Nov-12 5:30am

how to include??? Sir i am newbie.
Can you tell me how to include exception in catch

[no name] 28-Nov-12 6:16am

catch(Exception ex)
{
MessageBox.show(ex.message);
}

shaikh-adil 28-Nov-12 8:22am

thanks sir

2 solutions

Solution 1

The actual problem is pretty simple, probably, but...
Change this bit:

SQL

while (dr.Read())
{
    if (dr.HasRows == true)
    {

To this:

SQL

if (dr.Read())
{

Your code as is checks for any rows, by doing a Read on the DataReader - which advances the row pointer to the next row. So DataReader.HasRows fails unless you have two or more users with identical names and passwords. Since this is both very very unlikely, and a silly thing to allow to happen, you code never gets to the update section. Since you only want to know if the username / password combo matches, an if check will do the job without messing you up afterwards.

But...please don't do that. Storing passwords in clear is a very poor security system, and can compromise other systems because users often use the same password for many other things. What looks like a minor flaw in your system could result in your bank account being emptied becuase they share a common password! Have a look at this: Password Storage: How to do it.[^] and please rethink your system!

Well done on using parametrized queries BTW!

Posted 28-Nov-12 0:20am

OriginalGriff

Comments

shaikh-adil 28-Nov-12 8:14am

sir your idea is awsome i know that my system is not secure,
the problem is i studied about the hashing system
but i dont know how to implement that, your example is nice sir but i dont know step by step implementation as a a newbie i dont know much about .net and implementation with hashing function or crptrography in .net
your example is for console app. and i am using winform so dont know about such conversion.
and thanks for parametrized queries it is much simple in use. and because of this i'v enhance in my project work
thanks sir
:)

OriginalGriff 28-Nov-12 8:35am

The console stuff is just to make the example simple to follow - the methods given work regardless of console, winforms, WPF, WCF or whatever. All you have to do is call the method, and send the bytes to the DB. You then read back the bytes from teh db and compare them against the calculated value.

shaikh-adil 28-Nov-12 8:20am

and sir you have told rightly. the password is not updated.
how can i do that?

OriginalGriff 28-Nov-12 8:32am

Add the line:
comm.ExecuteNonQuery();
after the line:
comm.Parameters.AddWithValue("@Password", this.textBox2.Text);

shaikh-adil 28-Nov-12 8:39am

"there is already an open datareader associated with that command which must be close first"
sir
this error is coming

OriginalGriff 28-Nov-12 8:47am

Check you had both "m"s in the name - "comm" not "com" - It's a good idea to use slightly more obviously different names!

shaikh-adil 28-Nov-12 8:56am

yes sir but still the error is coming it is comm in perfect place

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)

Zubair Alie · Accepted Answer · 2012-11-28T02:29:00

C#

private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                bool IsMatchedFound = false;
                SqlConnection con = new SqlConnection();
                con.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=E:\project\sample project\prject xample2 perfect\login\Database1.mdf;Integrated Security=True;User Instance=True";
                try
                {
                    con.Open();
                    string qry1 = "Select * from Table1 WHERE password = @password COLLATE SQL_Latin1_General_CP1_CS_AS and Username=@Username COLLATE SQL_Latin1_General_CP1_CS_AS";
                    SqlCommand com = new SqlCommand(qry1, con);
                    com.Parameters.AddWithValue("@Username", this.usernamelabel.Text); // here is the login username
                    com.Parameters.AddWithValue("@Password", this.textBox1.Text);
                    SqlDataReader dr = com.ExecuteReader(); // here is the login password i.e old password
                    while (dr.Read())
                    {
                        if (dr.HasRows == true)
                        {
                            IsMatchedFound = true;
                            break;
                            //MessageBox.Show("Login Successfull");
                            //string qry2 = "UPDATE Table1 SET Password =@Password WHERE username=@username";
                            //SqlCommand comm = new SqlCommand(qry2, con);
                            //comm.Parameters.AddWithValue("@username", this.usernamelabel.Text); // here is the username
                            //comm.Parameters.AddWithValue("@Password", this.textBox2.Text); // here is the updated password textbox
                        }
                    }

                    dr.Close();
                    if (IsMatchedFound)
                    {
                        string qry2 = "UPDATE Table1 SET Password =@Password WHERE username=@username";
                        SqlCommand comm = new SqlCommand(qry2, con);
                        comm.Parameters.AddWithValue("@username", this.usernamelabel.Text); // here is the username
                        comm.Parameters.AddWithValue("@Password", this.textBox2.Text); // here is the updated password textbox
                        comm.ExecuteNonQuery();
                    }
                    else
                    {
                        MessageBox.Show("Invalid ID or password"); //error on selection.
                    }

                }
                catch (Exception)
                {
                    MessageBox.Show("Error with the databse connection");
                }

                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }