如何在MSAccess中的表达式中解决未定义函数'FldVal' [英] How to get around Undefined function 'FldVal' in expression in MSAccess

问题描述

我正在尝试构建一个生成pdf文件的报告.我正在使用一个将MS Access用作数据库的控制台应用程序.在Access数据库中，我调用一个查询，该查询包含执行计算的用户功能.这就是问题所在.每次我运行控制台应用程序时，都会收到以下错误消息:

I''m trying to build a report that builds a pdf file. I''m using a console app that uses MS Access as database. Within the Access database I call a query that contains a user function that performs calculations. That where the issue comes in. Every time I run the console app I get the following error message:

Undefined function ''FldVal'' in expression

包含"FldVal"功能的查询可以在Access中完美运行，但不能从控制台应用程序中完美运行.这是爆炸的代码:

The query containing the ''FldVal'' function runs perfectly from within Access but not from the console app. Here is the code that blows-up:

public static DataSet Get941Data(string rpt, int year, string qtr)
{
    DataSet ds = new DataSet();
    string CommandText = "SELECT  * FROM  qry941PDF";

    using (OleDbConnection connection = new OleDbConnection(DigiplexPDFs.AppSettings.GetAccessPathConnectionString()))
    {
        using (OleDbCommand cmd = new OleDbCommand(CommandText, connection))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("qtr", qtr);
            cmd.Parameters.AddWithValue("rpt", rpt + year);
            cmd.Parameters.AddWithValue("year", year);

            connection.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

            adapter.Fill(ds);

            return ds;
        }
    }
}

它在adapter.Fill(ds)行上崩溃.我以某种方式无法使用功能运行此查询.反正还有吗?

更正:如果以上内容不正确，我想使用其他方法.

It crashes on the adapter.Fill(ds) line. Somehow I''m not able to run this query with a function. Is there anyway around this?

CORRECTION: If the above is incorrect I''d like a different approach. Is there a Stored Procedure type call to Access available where I can pass parameters?

推荐答案

您已在命令中定义了三个参数，但实际的SQL中没有占位符陈述?您是否应该有以下内容:

You have defined three parameters into your command but there are no placeholders in the actual SQL statement? Should you have something like:

string CommandText = "SELECT  * FROM  qry941PDF WHERE SomeField = ? AND AnotherField = ? ... ";

另外，也不建议在查询中使用*.更好的做法是列出要获取的列.

Also it''s not advisable to use * in the query. A better practice is to list the columns you want to fetch.

找到了一个解决方案.但是，不是我要找的那个.事实证明，用户定义函数[UDF]不能不属于从外部访问的查询.我的查询在Access中运行良好，但是如果通过外部连接调用，则会引发未定义的函数异常.哎呀！

我喜欢在Access中使用UDF，因为它们可以使查询更整洁并运行得很快.

因此，为了克服此限制(即功能)，我重写了一些查询以使用iif(立即If)语句，并删除了函数调用，但不是很漂亮.然后，我必须从控制台应用程序对Access进行两次调用.第一个获取密钥，第二个获取值.它既不快也不优雅，但是可以工作.

感谢Mika的帮助.

Found a solution. However, not the one I was looking for. As it turns out an User-Defined Function [UDF] can not be apart of a query being accessed externally. My query runs fine within Access but if called via an external connection an undefined function exception is thrown. Bummer!!

I like using UDFs in Access because they make for cleaner queries and run fairly fast.

So to get around this limitation, eh feature, I rewrote a couple of queries to use iif (Immediate If) statements and removed the function calls, not as pretty. Then I had to make two calls to Access from my console app. The first to get the keys and the second to calculate the values. Its not as fast nor as elegant but it works.

Thanks Mika for your help.

这篇关于如何在MSAccess中的表达式中解决未定义函数'FldVal'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！