CODEWITHSUNDEEP
c#sql-server
GerryD
GerryD

Reputation: 11

sql server connection to db

I did some more research and came up with something else. However, this time the message box shows that the pwd was changed but when i refreshed the page in the db it was not changed. Here's the code:

SqlConnection sqlconn = new SqlConnection();

sqlconn.ConnectionString = @" ";          
sqlconn.Open();
string empCode = comboEmpCode.Text;
string oldPwd = txtOldPwd.Text;
string newPwd = txtNewPwd.Text;
string confirmPwd = txtConNewPwd.Text;
string sqlquery = "UPDATE [Employee] SET Pwd=@newpass where EmployeeCode=@empcode";
SqlCommand cmd = new SqlCommand(sqlquery, sqlconn);
cmd.Parameters.AddWithValue("@newpass", txtNewPwd.Text);
cmd.Parameters.AddWithValue("@empcode", comboEmpCode.Text);
cmd.Parameters.AddWithValue("@oldPwd", txtOldPwd.Text);
cmd.Connection = sqlconn;
cmd.ExecuteNonQuery();
SqlDataReader reader = null;
reader = cmd.ExecuteReader();
while (reader.Read())
{
    if ((txtNewPwd.Text == reader["newPwd"].ToString()) & (txtConNewPwd.Text == (reader["confirmPwd"].ToString()))) { }
}
MessageBox.Show("Password was changed Successfully!", "Password Change", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();

Upvotes: 0

Views: 345

Answers (1)

Mark Kram
Mark Kram

Reputation: 5832

Take a look at this method it returns a true if the update was successful and a false if it wasn't successful, I added the message boxes to provide a little clarity during troubleshooting. Additionally, I wrapped your SQLConnection and SQLCommand objects in Using statements which should properly dispose of these objects nicely when you are done which them.

public bool ChangePassword(string empCode, string newPassword, string oldPassword)
{
    string connectionString = "@<Enter your Connection String Here>";

    string sql = "UPDATE [Employee] SET Pwd=@newpass where EmployeeCode=@empcode";

    if (oldPassword != newPassword)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    cmd.Parameters.AddWithValue("@newpass", newPassword);
                    cmd.Parameters.AddWithValue("@empcode", empCode);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(string.Format("{0}-{1}", ex.Message, ex.InnerException));
            return false;
        }
        return true;
    }
    else
    {
        MessageBox.Show(string.Format("Your New password {0}, can not be the same as the old password {1}. Please try again.", newPassword, oldPassword));
        return false;
    }
}

Upvotes: 2

Related Questions