No value given for one or more required parameters in Oledb query

Question

I'm trying to query a CSV file. It works when I do a simple select, but as soon as I try to add a where clause, I run into No value given for one or more required parameters.

Obviously, it sounds like it's not getting the supplied parameter, but I've tried to pass it in a number of ways. See below for some code samples

DateTime lastRunDate = Convert.ToDateTime(ConfigurationManager.AppSettings["LastRunDate"]);

OleDbConnection conn = new OleDbConnection(
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + base.applicationRoot + ";" +
            "Extended Properties=\"text;HDR=Yes;FMT=CSVDelimited\"");

// This works just fine
//OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("select * from {0}", 

// This gives the error
OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("select top 100 * from [{0}] where {0}.sale_date = @sDate", base.csvFileName), conn);
//adapter.SelectCommand.Parameters.Add("@sDate", OleDbType.DBDate).Value = lastRunDate;
adapter.SelectCommand.Parameters.AddWithValue("@sDate", lastRunDate);

// This also gives the same error as above
//OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("select top 100 * from {0} where sale_date = '{1}'", base.csvFileName, lastRunDate), conn);
base.csvFileName, lastRunDate.ToShortDateString()), conn);

DataTable dt = new DataTable();
adapter.Fill(dt);

KruSuPhy · Accepted Answer · 2014-03-14 03:14:49Z

1

I don't know anything about C#, and I'm still decently new to SQL, but perhaps it's the SELECT TOP part of your query. I know that SELECT TOP isn't really accepted on all db systems, and that it's included in both of your queries that are giving you problems. Have you tried removing that and using LIMIT instead?

"select top 100 * from [{0}] where {0}.sale_date = @sDate"

to

"select * from [{0}] where {0}.sale_date = @sDate LIMIT 100"

I would have added this as a comment as it's not a concrete answer, but I have not the required rep yet.:(

answered Mar 14, 2014 at 3:14

KruSuPhy

397 bronze badges

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

1 Comment

The Jonas Persson Over a year ago

Thank you for your input, but I have tried without the top 100 as well.

Shell · Accepted Answer · 2014-03-15 05:27:00Z

1

Remove this line. You have added parameter twice.

adapter.SelectCommand.Parameters.AddWithValue("@sDate", lastRunDate);

and make sure the value is present in lastRunDate variable. it should not be null.

EDITED:

Remove table name from the where condtion, Use like this

select top 100 * from [{0}] where sale_date=@sDate

edited Mar 15, 2014 at 5:27

answered Mar 14, 2014 at 4:24

Shell

6,86911 gold badges43 silver badges75 bronze badges

4 Comments

The Jonas Persson Over a year ago

Thanks for your input, but that was a typo on my part. It is just one of the many things I've tried. I commented out one of the adding of parameters

Shell Over a year ago

@TheJonasPersson I have checked your code and reproduced the bug, I have edited my answer please check.

The Jonas Persson Over a year ago

Sorry, that still didn't do it for me. But, I've found out what my problem is. The where statement doesn't recognize the column names in the first row. If I change 'sale_date' to 'AI' (the actual column in Excel), it works. So, new question. How do I get the column names? I thought HDR=Yes would do the trick.

Shell Over a year ago

@TheJonasPersson Your new question required more information like your csv data format, sql query and what is your requirement. So, I think you should ask a new question separately. So, you can get more answers and replies. Don't forget to point out this question.

gsamaras · Accepted Answer · 2015-05-04 08:45:27Z

1

Column Names in the Excel file and in the Query are not Same.

Either column name is missing.
Column Name not existing in the Excel File.

edited May 4, 2015 at 8:45

gsamaras

73.7k50 gold badges210 silver badges330 bronze badges

answered May 4, 2015 at 8:27

VIKAS MISHRA Kec

111 bronze badge

Comments

The Jonas Persson · Accepted Answer · 2014-04-11 16:02:36Z

0

I found he issue with this. The query simply didn't understand the column names.

I thought that setting HDR=Yes meant that the oledb would read the first row headers, hence know them. But it wasn't until I added a schema.ini file that I managed to query in this way.

Here's some more about schema.ini files

answered Apr 11, 2014 at 16:02

The Jonas Persson

1,7461 gold badge17 silver badges38 bronze badges

1 Comment

callisto Over a year ago

I ran into this issue today. The schema.ini file exists. I am importing files with different names each time. I simply forgot to update the schema file with the new filename each time. Check your schema file kids!

Collectives™ on Stack Overflow

No value given for one or more required parameters in Oledb query

4 Answers 4

1 Comment

4 Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

Comments

1 Comment

Related