Error no value given for one or more required parameters.' select from excelsheet

Question

0.00/5 (No votes)

See more:

.NET4.6

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

	<pre lang="c#">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 don't 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 any problem

so that How to solve this error please ?

Excel sheet InvoiceData.xlsx sample

[^]

What I have tried:

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

Posted 20-Apr-18 23:31pm

ahmed_sa

Updated 20-Apr-18 23:55pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2018-04-20T23:56:00

If I understand your question correctly you don't have a column named CurrentMeterReading in your Excel sheet, that's just an alias that you give for a column when selecting the data. The column name in WHERE clause should refer the column names in the sheet.

Have a try with something like

C#

string str = @"SELECT  [رقم الاستمارة] as [UnitCode],[قراءة العداد]as[CurrentMeterReading] 
FROM  [Sheet5$] 
WHERE [قراءة العداد] <= 0 ";