[RESOLVED] Reading multiple Excel columns into VB datagrid

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

Code:

---

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?

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:

Code:

---

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.

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

What would be the way to select the columns?

So you're saying that:

Code:

---

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?

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

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