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:
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
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?
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.
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?
Executing SQL: “GROUP BY clause is missing or invalid”
Verify SQL syntax: This command is not supported by this provider
Does this have something to do with this link where group by is not there?
If I use ODBC data provider, will it support the SQL?
Update 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?
*My reputation did not allow me including more references for citing the last information.
Advertisement
Answer
I had to change the SQL to the following code and it worked:
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.
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.