选择错误，然后以更新语句开头 [英] error in select then begining with update statement

问题描述

我是新手，我正在Windows窗体中创建一个更新密码字段，但是错误来了，
首先，用户将输入旧密码，如果旧密码正确，则将更新新密码，但我的密码未更新
我使用了我的登录代码，然后使用了更新代码.但是密码没有更新

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();
       }

推荐答案

实际的问题可能很简单，但是...
更改此位:

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

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

为此:

if (dr.Read())
{

您的代码照原样通过对DataReader进行读取来检查是否有任何行-将行指针前进到下一行.因此，除非您有两个或多个具有相同名称和密码的用户，否则DataReader.HasRows将失败.由于这是非常非常不可能的事情，而且是一件很愚蠢的事情，所以您的代码永远都不会进入更新部分.由于您只想知道用户名/密码组合是否匹配，因此if检查将完成此任务，而之后不会使您烦恼.

但是...请不要那样做.明确存储密码是一个非常差劲的安全系统，并且可能会损害其他系统，因为用户经常将相同的密码用于许多其他事情.您系统中的一个小缺陷似乎可能导致您的银行帐户被清空，因为它们共享一个通用密码！看看以下内容:密码存储:操作方法. [ ^ ]然后请重新考虑您的系统！

使用参数化查询BTW做得好！

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!

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());
            }
        }

这篇关于选择错误，然后以更新语句开头的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！