sql server connection to db

Question

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

Do you actually have a ConnectionString or you are passing @" " in the code? — malkassem, Dec 26 '12 at 19:28
Are you using `TransactionScope` anywhere? Or transactions in general anywhere? These might be getting rolled back. — Oded, Dec 26 '12 at 19:29
Why do you have an `@oldPwd` parameter if you are not using it in the query? And what's with the `ExecuteReader`? That will not return anything. — Oded, Dec 26 '12 at 19:31
I don't see where in your Update statement you are using the "@oldPwd" value, maybe this is causing an error to occur? — Mark Kram, Dec 26 '12 at 19:35
The MessageBox will always show unless an exception is thrown. Use try/catch. — 001, Dec 26 '12 at 19:36
Also have you tried using SQL Profiler to see exactly what is being sent down the pipe? — Mark Kram, Dec 26 '12 at 19:36
Please don't post the details of your question in a separate question. The original is at http://stackoverflow.com/questions/14034351/creating-a-sql-connection-in-c-sharp. You could also use a better title. How many different question on this site could use the same title? — John Saunders, Dec 26 '12 at 20:04
@Oded, not to my knowledge. Malkassem yes the connection string is actually there. i just removed it. John, i tried to post this in a comment but it was too much characters so i was advised to post a new question. — GerryD, Dec 26 '12 at 20:44
If you need to add detail, it is best to _edit_ the existing question rather than post a new one. — Oded, Dec 26 '12 at 20:45

Mark Kram · Answer 1 · 2012-12-28T15:43:38.177

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

i tried it and it didnt work. I kept clicking the 'change password' button but nothing worked.. I also changed the comboEmpCode.Text in my code to comboEmpCode.SelectedItem because its a combobox but i got a error msg abt parameterized queries. Also, the combo box is not showing the employee codes altho i bounded them to it. Another thing too i got error msgs about my 'private void' functions after i inserted your code. — GerryD, Dec 26 '12 at 21:01
I would try hard coding the variables to see where the code is breaking and then work from there. — Mark Kram, Dec 27 '12 at 20:11

sql server connection to db

1 Answers1