Using WHERE SQL Command in Excel using VB.Net

Question

I am having difficulty in constructing SQL Commands in VB.Net wherein my project is connected to Excel via OLEDB.

Select * From [Sheet$]

This SQL Command works but whenever I try to add another SQL Command WHERE so that I could filter the data and I only wanted to be shown. I have headers in my excel file, the Name header is the 4th column equivalent to Column letter: D.

Select * From [Sheet$] Where Name='Xander'

it doesn't work, it shows nothing. What is the correct 'WHERE' SQL Command for Excel to OLEDB Connection?

By the way this is the code I am working on

 Dim conn As OleDbConnection
 Dim adapt As OleDbDataAdapter
 Dim dts As DataSet
 Dim excel As String = My.Settings.DefaultDirectory

 conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
 conn.Open()

 adapt = New OleDbDataAdapter("Select * from [Sheet$] where NAME='Xander'", conn)
 dts = New DataSet

 adapt.Fill(dts, "[Sheet$]")
 DataGridView1.DataSource = dts
 DataGridView1.DataMember = "[Sheet$]"

 conn.Close()

What is your connectionstring and do you have headers columns? — Steve
– Steve, Commented May 12, 2018 at 13:40
@Codexer, holy groot friend! Thank you, I havent thought of adding brackets in the Header Name thank you. Kindly write the answer down so I could mark it as Solved/Answered. Thank you again. — Quest Major
– Quest Major, Commented May 12, 2018 at 14:01

Trevor · Accepted Answer · 2018-05-12 14:06:04Z

1

You need to enclose brackets around your column names to escape them. Also Name is a reserved word, so you must escape it.

 Select * from [Sheet$] Where [NAME]='Xander'

edited May 12, 2018 at 14:06

answered May 12, 2018 at 14:02

Trevor

8,0156 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Quest Major Over a year ago

follow up question, how about [DATE]='4/2/2017' I think it is working with letter characters but not with Date format.

Quest Major Over a year ago

Did I do it right? Select * from [Q2$] Where [DATE]='#3/2/2018'

Trevor Over a year ago

Select * from [Q2$] Where [DATE]=#3/2/2018# this is how you do it... you need to remove ticks and wrap date in #...

Quest Major Over a year ago

Man you're a lifesaver, I'm kinda new in Excel SQL coz I only get used to Database SQLs, but man Thank you. I think this is all for now..

Collectives™ on Stack Overflow

Using WHERE SQL Command in Excel using VB.Net

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related