c# oledb update

Question

I am trying to update a single value into my access .accdb database via a c# winform interface. my SQL statement is:

    updateString("UPDATE Password_Table SET Password = '" + confirmnewpasswordTextBox.Text + "' WHERE Password_ID = 'user'");

field-wise it should be correct but whenever i execute the updateString function it only returns zero. may I know what I am doing wrongly in the following example?

    public static bool updateString(string SQL)
    {
        using (var connection = new OleDbConnection(connectionString))
        using (var command = connection.CreateCommand())
        {
            connection.Open();

            command.CommandText = SQL;
            command.CommandType = CommandType.Text;

            try
            {
                return command.ExecuteNonQuery();
            }
            catch
            {
                return -1;//for error
            }
        }
    }

Thank you!!

update:

    System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.

hmmm, i still cant figure out what is wrong, my table is Password_Table, and I am trying to update a column called Password where the Password_ID is "user".

update: found the error! turns out that Password is like a restricted keyword and i had to cover it in [ ] before it could work..

Answer 1

There are serious issues with your code. It is vulnerable to SQL injection. You should always use parametrized queries to avoid that. For example:

public static string UpdatePassword(string user, string password)
{
    using (var connection = new OleDbConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        connection.Open();

        command.CommandText = "UPDATE Password_Table SET Password = @pwd WHERE Password_ID = @user";
        command.Parameters.AddWithValue("@pwd", password);
        command.Parameters.AddWithValue("@user", user);

        try
        {
            return command.ExecuteNonQuery();
        }
        catch
        {
            return -1;//for error
        }
    }
}

And then invoke like this:

int rowsAffetected = UpdatePassword("user", confirmnewpasswordTextBox.Text);

Now, if this returns 0 it means that there is no record in your database which matches the Password_ID = user condition and there is nothing to update.