如何从C＃调用ms-access函数？ [英] How to call ms-access function from C#?

问题描述

我在一个名为'StripLead'的模块中写了一个ms访问函数。我做了一个叫做'CallFunction'的单行程序。现在，当我从ms-access调用这个过程时，它工作正常。但是当我从C＃中调用这个过程时，我得到错误：

lockquote

在表达式中未定义的函数'Striplead'。

Ms访问模块代码：

 公共函数StripLead（pstrPhrase As String）As String 
 Dim strFirstWord As String 
 Dim strReturn As String 
 Dim intPos As Integer 
 $ b $ strReturn = pstrPhrase 
 intPos = InStr（pstrPhrase， ）
如果intPos> 0然后
 strFirstWord = Left $（pstrPhrase，intPos  -  1）
选择案例strFirstWord 
案例A，An，
 strReturn = Right $（pstrPrase ，Len（pstrPhrase） -  intPos）
 End Select 
 End If 
 
 StripLead = strReturn 
 End Function 
  

$ b

Ms访问过程CallFunction（sql view）：

  SELECT Customers.ID，Striplead（[ContactName]）作为FROM客户; 
  

C＃code：

  public void CallStoredProcedure（string NewQry）
 {
 try 
 {
 DataTable tbl = new DataTable（）; 
 using（OleDbConnection connection = new OleDbConnection（ConnectionString））
 {
 OleDbCommand cmd = new OleDbCommand（）; 
 cmd.CommandText =CallFunction; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Connection =连接; 
 
 using（OleDbDataAdapter adp = new OleDbDataAdapter（cmd））
 {
 adp.Fill（tbl）; 
 
 
 
 $ b catch（例外ex）
 {
 throw; 
} 
} 
  

请建议该方法是否不正确。否则，如果有一种方法可以调用来自C＃的ms-access函数来获取数据。
我的目标是从ms-access中获取修改过的数据并填充到C＃中的数据网格中。

解决方案

Access提供了能够从SQL查询中调用用户定义的VBA函数，但该功能仅限于由Microsoft Access应用程序本身内的制作的查询。这是因为VBA函数需要执行Access应用程序的基础结构（特别是VBA表达式服务）。

在你的情况下，你可能能够运行在你的C＃代码中使用 Microsoft.Office.Interop.Access 实现 Access 查询，但这会给你一个 DAO Recordset 对象，摆弄这个与 OleDbDataAdapter 一起工作的东西可能比它的价值更麻烦（如果可能的话）。

您可能最好在C＃中重新创建VBA函数逻辑。

I have written an ms-access function in a module called ‘StripLead’. I made a single line procedure called ‘CallFunction’. Now when I call this procedure from ms-access it works fine. But when I call this procedure from C# I get error:

Undefined function 'Striplead' in expression.

Ms-access module code:

Public Function StripLead(pstrPhrase As String) As String
  Dim strFirstWord As String
  Dim strReturn As String
  Dim intPos As Integer

  strReturn = pstrPhrase
  intPos = InStr(pstrPhrase, " ")
  If intPos > 0 Then
    strFirstWord = Left$(pstrPhrase, intPos - 1)
    Select Case strFirstWord
      Case "A", "An", "The"
      strReturn = Right$(pstrPhrase, Len(pstrPhrase) - intPos)
    End Select
  End If

StripLead = strReturn
End Function

Ms-access procedure "CallFunction"(sql view):

SELECT Customers.ID, Striplead([ContactName]) AS a FROM Customers;

C# code:

   public void CallStoredProcedure(string NewQry)
       {
           try
           {
            DataTable tbl = new DataTable();
            using (OleDbConnection connection = new OleDbConnection(ConnectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandText = "CallFunction";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = connection;

                using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                {
                    adp.Fill(tbl);
                }
            }

         }
        catch(Exception ex)
        {
            throw;   
        }
    }

Please suggest whether the approach is incorrect. Or else, if there is a way to make call to ms-access function from C# to fetch data. My aim is to fetch modified data from ms-access and fill the datagrid in C#.

解决方案

Access offers the ability to call user-defined VBA functions from SQL queries, but that capability is restricted to queries that are made from within the Microsoft Access application itself. That is because the VBA functions require the Access application's infrastructure (specifically, the VBA "Expression Service") to execute.

In your case you might be able to run a "real" Access query from within your C# code using Microsoft.Office.Interop.Access but that would give you a DAO Recordset object, and fiddling around with that to work with an OleDbDataAdapter could well be more trouble than it's worth (if it is possible at all).

You'll probably just be better off to re-create the VBA function logic in C#.

这篇关于如何从C＃调用ms-access函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！