CODEWITHSUNDEEP
c#sql.netoledb
The Jonas Persson
The Jonas Persson

Reputation: 1746

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

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);

Upvotes: 0

Views: 3816

Answers (4)

VIKAS MISHRA Kec
VIKAS MISHRA Kec

Reputation: 11

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

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

Upvotes: 1

The Jonas Persson
The Jonas Persson

Reputation: 1746

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

Upvotes: 0

Shell
Shell

Reputation: 6849

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

Upvotes: 1

KruSuPhy
KruSuPhy

Reputation: 39

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.:(

Upvotes: 1

Related Questions