Oledb update-command with parameters not working on access-db

Question

I have a problem in my c#-application. I try to update an access-database via oledb-connection.

Here is the schema of the table:

Here is the function i am using:

public override int Update(string tableName, Action<ISqlParameterizer> actionSqlParameterizerUpdate, Action<ISqlParameterizer> actionSqlParameterizerWhere)
{
    var commandText = $"update {tableName} set @update where @where";

    var whereBuilder = new StringBuilder();
    var updateBuilder = new StringBuilder();
    var whereParameterizer = SqlParameterizer.CreateFrom(this);
    var updateParameterizer = SqlParameterizer.CreateFrom(this);

    actionSqlParameterizerWhere?.Invoke(whereParameterizer);
    actionSqlParameterizerUpdate?.Invoke(updateParameterizer);

    List<IDbDataParameter> parameterList = new List<IDbDataParameter>();

    foreach (var whereParameter in whereParameterizer.GetParameters())
    {
        whereBuilder.Append($" and {whereParameter.ParameterName} = @{whereParameter.ParameterName}");
        parameterList.Add(whereParameter);
    }
    foreach (var updateParameter in updateParameterizer.GetParameters())
    {
        updateBuilder.Append($", {updateParameter.ParameterName} = @{updateParameter.ParameterName}");
        parameterList.Add(updateParameter);
    }

    commandText = commandText.Replace("@where", whereBuilder.ToString().Substring(4));
    commandText = commandText.Replace("@update", updateBuilder.ToString().Substring(1));

    return base.ExecuteNonQuery(commandText, parameterList.ToArray());
}

The commandtext-property looks like this:

update MY_TABLE set MY_COLUMN = @MY_VALUE where SOME_COLUMN = @SOME_VALUE

The datatype of SOME_COLUMN is numeric, the datatype of MY_COLUMN is Memo.

Both parameters are oledbtype integer. I tried to add the parameter for MY_COLUMN as an string, so the parameter is VarWChar, but that makes no difference.

After running the command nothing happens, no rows get updated. When i enter the commandtext in Access itself and execute the statement. Access askes for the values of my parameters. I enter my values and the rows get updated.

Why does this not work with my oledb-connection?

---

The problem seems to be located at the SqlContext-class i am using.

Using a normal OleDbConnection:

using (var connection = new OleDbConnection(CONNECTION_STRING_ACCESS))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = $"UPDATE MY_TABLE SET MY_COLUMN = @MY_VALUE WHERE SOME_COLUMN = @SOME_VALUE";
        command.Parameters.Add(new OleDbParameter("MY_COLUMN", "MY_VALUE"));
        command.Parameters.Add(new OleDbParameter("SOME_COLUMN", 1));
        command.ExecuteNonQuery();
    }
}

works just fine...

Using my class:

using (var accessContext = SqlContext.Create(CONNECTION_STRING_ACCESS, SqlProvider.AccessSql))
{
    accessContext.Update("MY_TABLE", update => {
        update.Add("MY_COLUMN", "MY_VALUE");
    }, where => {
        where.Add("SOME_COLUMN", 1);
    });
}

doesn't work...

I have compared the commandtext-objekt and the parameter-objects of both versions and they are the same (except for the value of "MY_COLUMN", otherwise i could not recognize if the row gets updated). (i can not add the screenshots, because stack overflow says "the format is not supported", the funny thing is, that i was able to add my first screenshot (.bmp) without problem, when i try to add the second screenshot (.bmp) the error is shown ).

Anny suggestions what is causing the problem?

Answer 1

Your parameters are not in the correct order. These 2 loops should be switched in the order they appear in the code.

This matters, the OleDbCommand does not use named parameters. They are ordinal based on the position they occur in the sql statement.

Documentation - OleDbCommand.Parameters

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

...

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text

---

Your code:

// should occur 2nd
foreach (var whereParameter in whereParameterizer.GetParameters())
{
    whereBuilder.Append($" and {whereParameter.ParameterName} = @{whereParameter.ParameterName}");
    parameterList.Add(whereParameter);
}

// should occur 1st
foreach (var updateParameter in updateParameterizer.GetParameters())
{
    updateBuilder.Append($", {updateParameter.ParameterName} = @{updateParameter.ParameterName}");
    parameterList.Add(updateParameter);
}