SQL statement error

[brd24gor]

This one has me baffled. I have the following code for resetting a password:

			using(PasswordReset resetter = new PasswordReset())
			{
				if(resetter.ShowDialog() == DialogResult.OK)
				{
					string sql = "UPDATE Users SET Password = '" + resetter.Password + "' WHERE " +
						"User_ID = '" + userList.SelectedItems[userList.SelectedItems.Count-1] + "'";
					OleDbCommand passwordCommand = new OleDbCommand(sql, aLib.SQLConnection);
					passwordCommand.ExecuteNonQuery();
					passwordCommand.Dispose();
					MessageBox.Show("Password updated successfully!");
				}

resetter is just a form with two textboxes that get a password and a confirm password. When I call my ExecuteNonQuery(), I get an error stating I have a syntax error in my UPDATE statement. I have copied and pasted the manufactured string into Access (the database I'm using), and it runs fine.

Any ideas?
--Brad

 

[obislavu]

I think you should add ToString() for the pwd/user fields since there are returned "object" objects:

                   string sql = "UPDATE Users SET Password = '" + resetter.Password.ToString() + "' WHERE " +
                        "User_ID = '" + userList.SelectedItems[userList.SelectedItems.Count-1].ToString() + "'";

obislavu

 

[brd24gor]

No dice. Still get the same error. Any other ideas?

--Brad

 

[brd24gor]

Found the problem. 'Password' is a reserved SQL word so is has to be in [ ] brackets to be used.

string sql = "UPDATE Users SET [Password] = '" + resetter.Password.ToString() + "' WHERE " + "User_ID = '" + userList.SelectedItems[userList.SelectedItems.Count-1].ToString() + "'";

Thanks anyway,
--Brad

 

[chmohan]

if(resetter.ShowDialog() == DialogResult.OK)
{
string sUserId =userList.SelectedItems[userList.SelectedItems.Count-1].ToString() ;


string sql = "UPDATE Users SET Password = '" + resetter.Password + "' WHERE User_ID ='" +
+ sUserId + "'";

OleDbCommand passwordCommand = new OleDbCommand(sql, aLib.SQLConnection);
passwordCommand.ExecuteNonQuery();
passwordCommand.Dispose();
MessageBox.Show("Password updated successfully!");
}

 

[brd24gor]

chmohan,

That wouldn't have made any difference. As posted above, it was the 'Password' field in SET part of the SQL that was causing the problem. When a keyword is the same name as the field, it needs to be put into [square brackets]. I can legally put the string you have stored in sUserId into my SQL as I did above.

Thanks for the input,
--Brad