OleDb Update command throws data type mismatch when clearing Byte[] value in DataTable

Question

I have an existing method of updating an MS Access database via OleDb connection. I've used this method to update data in the past, but I'm having a type mismatch error now that I've added in an image field to the access table (OLE Object stored as long binary data). To retrieve the data from the access database, I fill a DataTable from an OleDbdataAdapter, standard stuff. I can pull the image from the database and have it show up perfect, so I know it's being stored correctly.

For updating the values from the DataTable into the file database, I dynamically build the update command from the DataTable fields, and include parameters for each field. To map the field types from a system type to OleDbType I use a simple switch method that returns the required type.

Everything works the way it's supposed to, except when I want to clear the image field from a particular row in the DataTable, and then update the database. The DataTable shows the correct empty value, but when the update command is invoked it throws this error. I've tried setting the field's value to string.empty, null, and DbNull.Value, all to no avail.

Do I have to create a completely separate method in the format of SQL 'DELETE FROM'? It seems like this would defeat the purpose of linking it to a DataTable in the first place, but I'm certainly no authority on the subject. Thanks in advance for any advice.

Here are the methods I'm using for reference:

Updating the Db Table:

    public static bool UpdateDbTable(string dbPath, string rowFilter, DataTable inputTable)
    {
        // Create the connection
        using (var connection = GetConnection(dbPath))
        {
            try
            {
                // Open the connection
                connection.Open();

                // Create the update command
                var updateCommand = GetUpdateCommand(inputTable, rowFilter, connection);

                // Link the new data adapter
                var adapter = new OleDbDataAdapter { UpdateCommand = updateCommand };

                // Update the MDB table
                adapter.Update(inputTable);

            } // Return false on error
            catch (OleDbException)
            {
                return false;
            }
        }

        // Return true on success
        return true;
    }

Build the update command:

    private static OleDbCommand GetUpdateCommand(DataTable inputTable, string rowFilter, OleDbConnection connection)
    {
        // Create the return command
        var retCommand = connection.CreateCommand();

        // Build the command string
        var sb = new StringBuilder(string.Format("UPDATE {0} SET ", inputTable.TableName));

        foreach (DataColumn col in inputTable.Columns)
        {
            // Append the command text
            sb.AppendFormat("{0} = ?, ", col.ColumnName);

            // Create the column parameter
            var par = new OleDbParameter
                {
                    ParameterName = col.ColumnName,
                    OleDbType = GetOleDbType(col.DataType),
                    Size = col.MaxLength,
                    SourceColumn = col.ColumnName
                };

            // Set any null values to DBNull
            if (par.Value == null) par.Value = DBNull.Value;

            // Add the parameter to the return command
            retCommand.Parameters.Add(par);
        }

        // Remove the last comma
        sb.Remove(sb.ToString().LastIndexOf(','), 1);

        // Add a where clause if a rowfilter was provided
        if (rowFilter != string.Empty)
            sb.AppendFormat("WHERE {0}", rowFilter);

        // Set the command text
        retCommand.CommandText = sb.ToString();

        // Return the command
        return retCommand;
    }

Map the correct data type:

    private static OleDbType GetOleDbType(Type inputType)
    {
        switch (inputType.FullName)
        {
            // Return the appropriate type
            case "System.Boolean":
                return OleDbType.Boolean;
            case "System.Int32":
                return OleDbType.Integer;
            case "System.Single":
                return OleDbType.Single;
            case "System.Double":
                return OleDbType.Double;
            case "System.Decimal":
                return OleDbType.Decimal;
            case "System.String":
                return OleDbType.Char;
            case "System.Char":
                return OleDbType.Char;
            case "System.Byte[]":
                return OleDbType.Binary;
            default:
                return OleDbType.Variant;
        }
    }

Answer 1

Figured out my problem this morning. The 'rowFilter' string I was passing to the GetUpdateCommand was causing the problem. I had copied an earlier format like this:

var rowFilter = string.Format("tag_unique_id = '{0}'", curTag);

I had used this type of filter in the past looking for text values. Therefore single quotes surrounding the field value was causing the adapter to interpret it as a string in the WHERE clause. Since the tag_unique_id field is an int32, it of course threw a type mismatch error at runtime. The solution was to just strip the quotes away like this:

var rowFilter = string.Format("tag_unique_id = {0}", curTag);

Lesson learned.