CODEWITHSUNDEEP
sqlvb.netexcel
ken
ken

Reputation: 399

Failing to read data from an excel sheet with where clause in select statement

I am trying to read data from an excel sheet but there is an exception saying that no value given for one or more required parameters which I don't understand. Here is my code upto where the error is pointing to:

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection _
    ("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\Staff Contracts.xlsx; Extended Properties=""Excel 12.0;HDR=NO""")
MyCommand = New System.Data.OleDb.OleDbDataAdapter _
    ("select * from [Staff Contracts$A4:K14] where I=176", MyConnection)
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)

The exception points to that last line MyCommand.Fill(DtSet) which shows me that there is a problem with my select statement. Previously my code reads the data and displays it without the where clause in the select but when I added the where statement so that it can only read data from rows where the column I has a value equal to 176, it instead gives an exception that shows that some required parameter is missing a value

Upvotes: 0

Views: 3414

Answers (1)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

In case of HDR = NO, I column will become F9 because oledb will name the column F1.. Fn for it's internal reference.

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Staff Contracts$A4:K14] where F9=176", MyConnection);

In this case the OLEDB driver will create columnnames for each column in the selection (F1 to Fn). so if you change the range "A4:K14" to "B4:K14" then column F9 will be F8.

Upvotes: 2

Related Questions