1

Trying to execute the following query on northwind access database throws an exception:

No value given for one or more required parameters

My simplified query is 

SELECT * FROM (SELECT [Orders].[OrderDate] FROM [Orders]) t 
WHERE [Orders].[CustomerID]=?

The exception is only thrown if nested SELECT is used.

Question: is there a way to use nested SELECT and parameters?

My code is:

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _databasePath);
conn.Open();

OleDbCommand com = new OleDbCommand(@"SELECT * 
           FROM (SELECT [Orders].[OrderDate] FROM [Orders]) t 
           WHERE [Orders].[CustomerID]=?", conn);

com.Parameters.Add("Bla", OleDbType.WChar);
com.ExecuteReader(CommandBehavior.CloseConnection);
VladL
  • 12,769
  • 10
  • 63
  • 83
  • 2
    I think you need to select also `CustomerID` in the nested query, and then change the `where` clause to `WHERE t.CustomerID = ?` – SomethingSomething May 14 '14 at 13:36
  • you are right, but I've just oversimplified the query for here. Taking the where part inside of the nested select did the job. But thank you anyway! – VladL May 14 '14 at 13:59

1 Answers1

3

You have a couple problems. One, you haven't selected the CustomerID field in your subquery so you can't filter on it from the outer query. Second, you are referencing the wrong table alias in the outer query. Try this instead:

SELECT * 
FROM (SELECT [Orders].[OrderDate], [Orders].[CustomerID] FROM [Orders]) t 
WHERE t.[CustomerID]=?

Alternatively, you could not select the CustomerID and put your parameter in your subquery:

SELECT * 
FROM (SELECT [Orders].[OrderDate] FROM [Orders] WHERE [Orders].[CustomerID]=?) t
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Thanks, I've just simplified it to much, but I'll try your edit/alternative query – VladL May 14 '14 at 13:42
  • Thank you, the first approach doesn't work, looks like a kind of OleDB limitatation, but the second one did the trick. – VladL May 14 '14 at 13:56