SQL Query for Excel file

Swisher24

I have several excel files(*.xls) that I'm extracting data from using Visual Basic and I'm having problems with my SQL statement. I would like to do a count & group by in the statement to input this data into a control sheet but I continue to get an error message when filling the dataset. Error: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in microsoft.visualbasic.dll Code Snippet:

Function groupxls(ByRef xlfile As String, ByRef xlsfile As String)

Dim ex1 As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & xlfile & ";" & "Extended Properties=Excel 8.0;"
Dim conn1 As New OleDbConnection(ex1)
Dim dtset1 = New DataSet

Dim flnme As String
y = xlsfile.LastIndexOfAny(".")
flnme = xlsfile.Remove(y, 4)
Dim col1 As String = "sc_typname" '<-----Column to group by

Dim MyCommand1 As New OleDbDataAdapter("select count(*) from flnme group by col1", conn1)

    MyCommand1.Fill(dtset1)
    DataGrid1.Refresh()
    DataGrid1.DataSource = dtset1.Tables(0)
    conn1.Close()
    conn1.Dispose()

End Function

If anyone can take the time to look at this for me please know that it will be very much appreciated! Thanks in advance! :)

Swish

aztekka

Swisher24 wrote:

Dim col1 As String = "sc_typname" '<-----Column to group by Dim MyCommand1 As New OleDbDataAdapter("select count(*) from flnme group by col1", conn1)

You are passing the name of the variable to the query string instead of the value of the variable, try the following:

Dim MyCommand1 As New OleDbDataAdapter("select count(*) from flnme group by " + col1, conn1)

Swisher24

Thanks aztekka! I tried this sql statement and I'm still getting an Unhandled Exception Error. I'm thinking that VB 2003 doesn't like the statement somehow. I will continue to work on this and any more help that can be provided will be very appreciated.

Swish