title: How to use SUM and GROUP BY(SQL) with Visual FoxPro in VB.NET?
body: <p>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:</p>
  
  <pre><code> Dim dBaseCommand As New System.Data.OleDb.OleDbCommand("SELECT * FROM inventory", con)
      Dim dBaseDataReader As System.Data.OleDb.OleDbDataReader = dBaseCommand.ExecuteReader(CommandBehavior.SequentialAccess)
  </code></pre>
  
  <p>But I need to run the following SQL instead of <code>SELECT * FROM inventory</code></p>
  
  <pre><code>SELECT itemnumber, mfgr, SUM(qty) AS cqty, unitcost, description FROM inventory GROUP BY itemnumber
  </code></pre>
  
  <p>I replaced the SQL and it does not work. How can I implement that SQL or GROUP BY statement into my VB code?</p>
  
  <p><strong>Update:</strong></p>
  
  <p>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. </p>
  
  <p>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?</p>
  
  <p><strong>Executing SQL: "GROUP BY clause is missing or invalid"</strong></p>
  
  <p><strong>Verify SQL syntax: This command is not supported by this provider</strong></p>
  
  <p>Does this have something to do with <a href="http://msdn.microsoft.com/en-US/library/80x51c04%28v=vs.80%29.aspx" rel="nofollow">this link</a> where group by is not there?</p>
  
  <p>If I use ODBC data provider, will it support the SQL?</p>
  
  <p><strong>Update 2:</strong></p>
  
  <p>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?</p>
  
  <p>*My reputation did not allow me including more references for citing the last information.</p>