error no value given for one or more required parameters.'

When i try to get data from excel sheet i get error

no value given for one or more required parameters.'

why this happen

i work in visual studio 2010 with excelsheet 2013

my code in c# as following

public DataTable ShowWrongExcelData()
{
string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtPath.Text);
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string str = @"SELECT [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM [Sheet5$] where [CurrentMeterReading] <= 0 ";
OleDbCommand com = new OleDbCommand();
com = new OleDbCommand(str, con);
OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda = new OleDbDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "[Sheet5$]");
con.Close();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables["[Sheet5$]"];
return dt;
}

why give this error i dont use any parameters

but when i select all data from Excel sheet as following :

@"SELECT [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM [Sheet5$]";

I get data without anyh problem

so that How to solve this error please ?

Excel sheet InvoiceData.xlsx sample

http://www.mediafire.com/file/b62b5ub26mrt9t1/InvoiceData.xlsx

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
hi,

now what is the error you are geeting try below

string str = @"SELECT [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM [Sheet5$] where cast([????? ??????] as numeric(18,2)) <= 0 ";

or try to convert that field into a numeric value and try
ahmed elbarbary8y ago
Thank you for reply
i remove alias and leave original columns name as exist in excel sheet
but still error display
Rafnas T P8y ago
hi,
try change your select statement as follows

string str = @"SELECT [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM [Sheet5$] where [CurrentMeterReading] <= 0 ";

instead of [CurrentMeterReading] write original one

string str = @"SELECT [??? ?????????] as [UnitCode],[????? ??????]as[CurrentMeterReading] FROM [Sheet5$] where [????? ??????] <= 0 ";