Data type mismatch in criteria expression

Question

Hello,

Using cmd As New OleDbCommand("ALTER TABLE options ALTER Column Blah BYTE", connection)

Blah has Text string data, so the above command will throw OleDbException:

Data type mismatch in criteria expression.

How to discard the data and update the Blah column anyway?

Thanks.

Answer 1

Hi @StewartBW , Welcome to Microsoft Q&A,

To change the column "Blah" to "BYTE" and discard the existing string data, you need to follow a few steps because directly changing the data type from "TEXT" to "BYTE" will result in a data type mismatch error.

1. Add a new temporary column of type "BYTE".
2. If you need to keep some data, you can convert and copy it to a new column. In this case, you want to discard the data, so you can skip this step.
3. Delete the existing "TEXT" column.
4. Rename the new column to the original column name.

using (OleDbConnection connection = new OleDbConnection("your_connection_string"))
{
    connection.Open();

    // Step 1: Add the new temporary column
    using (OleDbCommand cmd = new OleDbCommand("ALTER TABLE options ADD COLUMN BlahTemp BYTE", connection))
    {
        cmd.ExecuteNonQuery();
    }

    // Step 2: Drop the old column
    using (OleDbCommand cmd = new OleDbCommand("ALTER TABLE options DROP COLUMN Blah", connection))
    {
        cmd.ExecuteNonQuery();
    }

    // Step 3: Rename the temporary column to the original column name
    using (OleDbCommand cmd = new OleDbCommand("ALTER TABLE options RENAME COLUMN BlahTemp TO Blah", connection))
    {
        cmd.ExecuteNonQuery();
    }

    connection.Close();
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.