如何在 VB.NET 中将 SUM 和 GROUP BY(SQL) 与 Visual FoxPro 一起使用？

How to use SUM and GROUP BY(SQL) with Visual FoxPro in VB.NET?

提问by codelogn

I do not have much experience with VB. I have built a VB app that can retrieve data from a table(FoxPro). The code is given below:

我对VB没有太多经验。我已经构建了一个可以从表（FoxPro）中检索数据的 VB 应用程序。代码如下：

 Dim dBaseCommand As New System.Data.OleDb.OleDbCommand("SELECT * FROM inventory", con)
    Dim dBaseDataReader As System.Data.OleDb.OleDbDataReader = dBaseCommand.ExecuteReader(CommandBehavior.SequentialAccess)

But I need to run the following SQL instead of SELECT * FROM inventory

但我需要运行以下 SQL 而不是 SELECT * FROM inventory

SELECT itemnumber, mfgr, SUM(qty) AS cqty, unitcost, description FROM inventory GROUP BY itemnumber

I replaced the SQL and it does not work. How can I implement that SQL or GROUP BY statement into my VB code?

我替换了 SQL，但它不起作用。如何在我的 VB 代码中实现该 SQL 或 GROUP BY 语句？

Update:

更新：

The SQL was a sample sql from MySQL. But I have updated the SQL that I am now actually using in my VB app. It does not show any error or does not close the app itself and it does not generate the file which is supposed to be produced by this app. If I use the sql from first sample code, it generates the file nicely from that table.

SQL 是来自 MySQL 的示例 sql。但是我已经更新了我现在在我的 VB 应用程序中实际使用的 SQL。它不会显示任何错误或不会关闭应用程序本身，也不会生成应该由该应用程序生成的文件。如果我使用第一个示例代码中的 sql，它会很好地从该表生成文件。

I went to database explorer(data sources) to run this query too. The following issues came up. Any ideas? not supported by Foxpro or any other workarounds?

我也去数据库资源管理器（数据源）运行这个查询。出现了以下问题。有任何想法吗？Foxpro 或任何其他解决方法不支持？

Executing SQL: "GROUP BY clause is missing or invalid"

执行 SQL：“GROUP BY 子句丢失或无效”

Verify SQL syntax: This command is not supported by this provider

验证 SQL 语法：此提供程序不支持此命令

Does this have something to do with this linkwhere group by is not there?

这与不存在 group by 的链接有关吗？

If I use ODBC data provider, will it support the SQL?

如果我使用 ODBC 数据提供程序，它是否支持 SQL？

Update 2:

更新 2：

It looks like ODBC driver supports GROUP BY. But Microsoft recommends to use OLEDB data provider instead of ODBC which does not support GROUP BY. In Visual basic data connections, I dont see ODBC as available data provider. It has only SQL server data provider and OLEDB data provider. Is there any way so that I can use the ODBC data provider and then use the GROUP BY statement?

看起来 ODBC 驱动程序支持 GROUP BY。但是 Microsoft 建议使用 OLEDB 数据提供程序而不是不支持 GROUP BY 的 ODBC。在 Visual Basic 数据连接中，我没有将 ODBC 视为可用的数据提供程序。它只有 SQL 服务器数据提供程序和 OLEDB 数据提供程序。有什么方法可以让我使用 ODBC 数据提供程序，然后使用 GROUP BY 语句？

*My reputation did not allow me including more references for citing the last information.

*我的声誉不允许我包含更多参考资料来引用最后的信息。

回答by codelogn

I had to change the SQL to the following code and it worked:

我不得不将 SQL 更改为以下代码并且它起作用了：

SELECT     itemnumber, SUM(qty) AS qty, description
FROM         inventory
GROUP BY itemnumber, description

From VFP 8, SELECT with GROUP BY clause can select only columns which are associated with either GROUP BY or fields with Aggregate functions. If we need to use GROUP BY clause, we need to be careful with those conditions. So if the "description" from GROUP BY clause is removed, then the SQL does not work. Similarly, if SUM function is removed from qty column, the SQL wont work.

从 VFP 8 开始，带有 GROUP BY 子句的 SELECT 只能选择与 GROUP BY 或具有聚合函数的字段关联的列。如果我们需要使用 GROUP BY 子句，我们需要小心这些条件。因此，如果删除了 GROUP BY 子句中的“描述”，则 SQL 不起作用。同样，如果从 qty 列中删除 SUM 函数，则 SQL 将不起作用。

These conditions explain why "GROUP BY clause is missing or invalid" popped up. The good thing is that we can verify the queries on Visual studio before embedding into application.

这些条件解释了为什么会弹出“ GROUP BY 子句丢失或无效”。好消息是我们可以在嵌入应用程序之前验证 Visual Studio 上的查询。

回答by Pieter Geerkens

Remove the single quotes from around Inventory, to make the statement:

删除 Inventory 周围的单引号，以进行声明：

 SELECT partnumber, SUM(quantity) FROM inventory GROUP BY partnumber

Inventoryis a SQL object, not a text literal.

Inventory是一个 SQL 对象，而不是文本文字。