oledbexception is thrown for input that's accepted by access

Question

I'm building a small phonebook application and I'm storing phones in an access database in table called phones whose fields are : FullName,Job,Phone1,Phone2,Notes and SN primary key.

I've set fullname and phone1 to required and set validation rule for fullname to Len([FullName])>3 and validation rule for both phone1 and phone2 to Like "[0-9]*", I also set validation messages for the validation rules.

In a wpf application I've added the access database using visual studio to generate the dataset and tableadapter code, this is MainWindow.xaml:

<Window x:Class="MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="253" Width="685">
<Grid Height="206" Width="658">
    <Label Content="FullName" Height="28" HorizontalAlignment="Left" Margin="12,12,0,0" Name="Label1" VerticalAlignment="Top" />
    <TextBox Height="23" HorizontalAlignment="Left" Margin="78,16,0,0" Name="FullName" VerticalAlignment="Top" Width="185" />
    <Label Content="Job" Height="28" HorizontalAlignment="Left" Margin="29,46,0,0" Name="Label2" VerticalAlignment="Top" />
    <TextBox Height="23" HorizontalAlignment="Left" Margin="78,46,0,0" Name="Job" VerticalAlignment="Top" Width="185" />
    <Label Content="Phone1" Height="28" HorizontalAlignment="Left" Margin="22,75,0,0" Name="Label3" VerticalAlignment="Top" />
    <Label Content="Phone2" Height="28" HorizontalAlignment="Left" Margin="269,16,0,0" Name="Label4" VerticalAlignment="Top" />
    <TextBox Height="23" HorizontalAlignment="Left" Margin="78,75,0,0" Name="Phone1" VerticalAlignment="Top" Width="110" />
    <TextBox Height="23" HorizontalAlignment="Left" Margin="325,21,0,0" Name="Phone2" VerticalAlignment="Top" Width="110" />
    <Label Content="Notes" Height="28" HorizontalAlignment="Left" Margin="274,56,0,0" Name="Label5" VerticalAlignment="Top" />
    <TextBox Height="95" HorizontalAlignment="Left" Margin="325,60,0,0" Name="Notes" VerticalAlignment="Top" Width="328" AcceptsReturn="True" AcceptsTab="True" />
    <Button Content="Save" Height="37" HorizontalAlignment="Left" Margin="274,161,0,0" Name="Button2" VerticalAlignment="Top" Width="135" />
</Grid>

this is save click handler :

private button2_Click(object sender , RoutedEventArgs e)
{
    try
    {
        PhonesDataSetPhonesTableAdapter.Insert(FullName.Text, Job.Text, Phone1.Text, Phone2.Text, Notes.Text);
    } catch(OleDbException ex) {
        MessageBox.Show(ex.Message);
    }        
}

the PhonesDataSetPhonesTableAdapter is defined like this :

Global.projectName.PhonesDataSetTableAdapters.PhonesTableAdapter PhonesDataSetPhonesTableAdapter = new Global.projectName.PhonesDataSetTableAdapters.PhonesTableAdapter();

When I launch application and put John as fullname and programmer as job and 2137976 as phone1 number an OleDbException is thrown with the validation message for phone1 which is

your phone number contains letters, it should only contain numbers

but it doesn't contain any letter and when I try the same input through access it accepts it, what's going on here ? and how do I make it work.

Note that your expression only evaluates the first character of the phone number. Any other characters can be letters. — Erik A, Dec 24 '17 at 14:32

score 1 · Accepted Answer · answered Dec 24 '17 at 11:06

The OleDb driver requires a different wildcard character in its Like statement. Instead of * it now wants a % (which is ANSI-92).

If I change in MS Access the validation rule to Like "[0-9]%" I can insert rows with a phonenumber "123" by calling insert on that tableadapter.

The downside of this is that in Access you can no longer insert values as Access now expects the literal character % after a single digit.

If you want both your application and access to work, using the Odbc driver would work.

Here is some background on the issue: Microsoft Jet wildcards: asterisk or percentage sign?

oledbexception is thrown for input that's accepted by access

1 Answers1