当“验证规则"生效时，SQL在MS Access中执行良好，但在C#中执行不佳.被设置 [英] SQL executes well in MS Access but not in C# when "Validation Rule" is set

问题描述

我遇到一个奇怪的问题.我已经在MS Access中编写了一个查询，该查询从MS Access运行时会在数据库中完美插入一个值，但是当从C#执行相同的查询时，它将显示"Validation Text"为OleDbException，并且插入命令失败. 验证规则"为" @ .*".该列保存应包含一个"@"和一个点"的电子邮件.之后.删除验证规则"并从C#执行查询没有问题.仅当设置了验证规则并且仅从C#而不是从MS Access查询执行中发生错误时，才会发生这种情况.

I am having a strange problem. I have written a query in MS Access which inserts a value perfectly in database when run from MS Access, but when same query is executed from C# then it shows the "Validation Text" as OleDbException and insert command fails. The "Validation Rule" is "@.*" The column saves emails which should have one "@" and one dot "." after it. There is no problem when "Validation Rule" is removed and query is executed from C#. It only occurs when Validation Rule is set and error occurs only from C# and not from MS Access query execution.

我已将代码和数据库上传到: http://www.2shared. com/file/XYmFD8Mg/Database_Validation.html

I have uploaded the code and database to: http://www.2shared.com/file/XYmFD8Mg/Database_Validation.html

MS Access中的SQL查询:

SQL Query in MS Access:

INSERT INTO EMail ( EMail, Company )
VALUES ('[e-mail]', 'Company');

代码是:

    try
    {
        string dbPath = null;

        if (File.Exists(Path.GetFullPath(@"..\..\..\Database.accdb")))
            dbPath = Path.GetFullPath(@"..\..\..\Database.accdb");

        dbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbPath);
        dbConnection.Open();

        if (0 >= new System.Data.OleDb.OleDbCommand("INSERT INTO EMail ( EMail, Company ) VALUES ('[e-mail]', 'Company')", dbConnection).ExecuteNonQuery())
            MessageBox.Show("Could not insert '[e-mail]'. Check if already inserted.");

        try { dbConnection.Close(); }
        catch (Exception) { }
    }
    catch (Exception Ex)
    {
        try { dbConnection.Close(); }
        catch (Exception) { }

        MessageBox.Show(Ex.Message);
    }

数据库快照:

推荐答案

是的，我可以确认行为.

You are right, I can confirm the behaviour.

我做了一些测试，很显然，如果您将验证规则更改为

I did a few tests and, apparently, if you change the validation rule to

Like "%@%.%"

它可以在C#中运行，但是在Access中将不再可用.我想原因是Access使用DAO进行数据访问，它使用*作为通配符，而OLEDB的通配符是%.

it works in C#, but it won't work in Access anymore. I guess the reason is that Access uses DAO for data access, which uses * as the wildcard character, whereas OLEDB's wildcard character is %.

我发现以下验证规则在Access和.NET中都能产生所需的结果:

I found that the following validation rule produces the desired result in both Access and .NET:

Like "*@*.*" Or Like "%@%.%"

这篇关于当“验证规则"生效时，SQL在MS Access中执行良好，但在C#中执行不佳.被设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！