syntax error in query when running update query from c# code

Question

I am trying to run following piece of code:

private void btnUpdate_Click(object sender, EventArgs e)
{
    if (txtNewPassword.Text.Length > 4 && txtNewPassword.Text.Equals(txtConfirmPassword.Text))
    {
        try
        {
            OleDbConnection connection = new OleDbConnection(MDFConfiguration.getConnectionString());
            connection.Open();

            int updatedRecordCount = updateExistingUserRecord(connection);

            if (updatedRecordCount > 0)
            {
                MessageBox.Show("Password Changed Successfully");
            }
            else
            {
                MessageBox.Show("There was some error during updated");
            }

            connection.Close();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            MessageBox.Show("exception: " + ex.ToString());
        }
    }
    else
    {
        MessageBox.Show("New Password does not match required criteria");
    }
}

private int updateExistingUserRecord(OleDbConnection connection)
{
    string sql = "UPDATE " + MDFConfiguration.LOGIN_INFO_TABLE + " SET " +
        " password = '" + MDFUtils.CreateMD5Hash(txtNewPassword.Text) + "' WHERE " +
        " login_name = '" + cmbLoginNames.SelectedItem.ToString() + "'";

    Console.WriteLine("sql = " + sql);

    OleDbCommand command = new OleDbCommand(sql, connection);
    return command.ExecuteNonQuery();
}

When I run this code it gives me syntax error in query at runtime but when I run the same query printed by Console.WriteLine in the above piece of code directly in MS Acess it runs without any error.

Console.WriteLine prints following query:

UPDATE MDF_LOGIN_INFO SET  password = 'E206A54E97690CCE50CC872DD70EE896' WHERE  login_name = 'admin'

Exception Logs:

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at MDFData.AdminToolForm.updateExistingUserRecord(OleDbConnection connection) in c:\Users\UBAID ULLAH\Documents\Visual Studio 2012\Projects\Backup MDFData\MDFData\AdminToolForm.cs:line 114
   at MDFData.AdminToolForm.btnUpdate_Click(Object sender, EventArgs e) in c:\Users\UBAID ULLAH\Documents\Visual Studio 2012\Projects\Backup MDFData\MDFData\AdminToolForm.cs:line 79

Any Suggestions?

Answer 1

Wrap your column names in square brackets - chances are password or login_name are reserved and causing a conflict with your update statement i.e.

UPDATE MDF_LOGIN_INFO 
SET [password] = 'E206A54E97690CCE50CC872DD70EE896' 
WHERE [login_name] = 'admin'

I would also recommend you look at using SQL Parameters in your query instead of raw SQL because at the minute you are open to SQL Injection.