[RESOLVED] Reading multiple Excel columns into VB datagrid

[PVR]

I am trying to load a datagrid from an Excel spreadsheet with data coming from multiple columns (but not necessary next to eachother)

 strsql = "SELECT distinct * FROM [ImportUOM$g:g], [ImportUOM$k:k]"
            Dim Conn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & path & ";" & _
            "Extended Properties=""Excel 8.0;IMEX=1;HDR=Yes"""
            Dim conn2 As New System.Data.OleDb.OleDbConnection(Conn1)
            Dim da As New OleDbDataAdapter(strsql, conn2)

            Dim objds As New DataSet
             da.Fill(objds, "UOMConv")

However - this gives me all combinations from the two columns, whilest I just want G1 and K1, G2 and K2, G3 and K3 etc

How can I get this to work?

[jmcilhinney]

What you're doing is equivalent to specifying two tables, which will produce a join. What you need to do is specify only the columns you want from one table only. I think that this will do the job:

SELECT F1, F5 FROM [ImportUOM$G:K]

That will select the first and fifth columns from the range specified. The default names for the columns used by the provider are F1, F2, etc.

[PVR]

Unfortunately, it gives me "No value given for one or more required parameters" when selecting F1, F2 from that range. Any idea what would be issing?

If I change F1, F2 into "*" it does work

[PVR]

What would be the way to select the columns?

[jmcilhinney]

So you're saying that:

SELECT * FROM [ImportUOM$G:K]

works? If so, what are the names of the resulting DataColumns, i.e. the ColumnName property values of the columns in the resulting DataTable?

[PVR]

Wow ... thanks for that tip! That worked a treat now, it was using the header row for column names.

[jmcilhinney]

Ah, sorry. I should have noticed that in your original connection string.