'Like' operator in field validation rule not working for OLEDB inserts

Question

First of all, I'm no programmer, I'm doing this for fun for a small project at work (I'm team lead in customer service).

I created an Access database with the following validation rule: Like "T######" in order to only accepts our employee IDs (e.g. T123456) It works fine in Access.

However, in C#, I have the following code on my submit button:

private void btnSubmit_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "insert into Employees (ID, LastName, FirstName) values ('" + txtTID.Text + "','" + txtLastName.Text + "','" + txtFirstName.Text + "')";

            command.ExecuteNonQuery();
            connection.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("" + ex);
            connection.Close();
        }

    }

Whenever I enter something like the example above, I get an Error Message saying it couldn't insert the data because of validation rule.

I tried with a different wildcard (*) and it still doesn't work. Is there anything obvious I'm missing here?

Thank you so much for the help, have yourselves a great day!

Answer 1

You have hit upon a limitation of the Access OLEDB provider. Your code should work as expected if you switch to using System.Data.Odbc and the Access ODBC driver.