'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!

You code is at risk of [SQL Injection](https://stackoverflow.com/questions/601300/what-is-sql-injection). You should use prepared queries with parameters. Even if you are doing it just for fun it can be useful because it gracefully handles the case where the name contains a `'`. — litelite, Aug 28 '17 at 17:31
@developer_117 error message added :) thanks for the quick reply! — djodars, Aug 28 '17 at 17:41
where in your code are you using the `LIKE` keyword and sql wild card such as `%` `%` — MethodMan, Aug 28 '17 at 17:52
@MethodMan Sorry if I wasn't clear, the validation rule is in Access directly and not in the code. — djodars, Aug 28 '17 at 17:57

score 1 · Accepted Answer · answered Aug 28 '17 at 17:38

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.

answered Aug 28 '17 at 17:38

Gord Thompson

98,607
26
164
342

Worked perfectly with ODBC, thank you! I used https://www.connectionstrings.com/microsoft-access-accdb-odbc-driver/standard-security/ – djodars Aug 28 '17 at 18:43

score 0 · Answer 2 · answered Aug 28 '17 at 17:45

Sorry that I couldn't write you a comment. There is a validation rule on your table. This means the problem is not your query but in your table architect. for example it could be, the data you are trying to insert doesn't match the data type of your fields see this for more info

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

2 Answers2