C# No value given for 1 or more required parameters, but I can't see why

Question

I have posted the code I have below I am trying to get the data from an Access 2002-2003 database

If I take out everything after the WHERE clause and just use "SELECT * FROM [{0}] then it takes all the data from the table with no problems. I have double checked the field names, they are definitely correct. I have more than 1 table with the same field names, so I thought maybe I would need to include the table name before the field name, but with or without the table I still get the same exception. I have tried moving the position of the square brackets, again with no success...

Even if I include only one of the WHERE clauses, the code no longer works, and I can't for the life of me work out why.. I have spent hours looking at numerous posts here and on other sites related to this error, but none of the suggestions have helped me..

The Destination field is a 'memo' field in Access. The Next Collection fields are date fields, GVars.currentDate is set earlier in the code to be today's date (with the time portion set to 00:00:00). GVars.thisFY is also set programatically as a string prior to this.

Any tips would be appreciated.

string sql;
OleDbDataAdapter adapter;

sql = string.Format(
    "SELECT * FROM [{0}] WHERE {0}.[Destination] = @Destination AND {0}.[Next Collection] BETWEEN @NextCollectionA AND @NextCollectionB"
    , GVars.thisFY);

// Create the command object
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;

// Add values to the fields
cmd.Parameters.AddWithValue("@Destination", "Henwood");
cmd.Parameters.AddWithValue("@NextCollectionA", GVars.currentDate);
cmd.Parameters.AddWithValue("@NextCollectionB", GVars.currentDate.AddDays(1));

adapter = new OleDbDataAdapter(cmd.CommandText, conn);

System.Diagnostics.Debug.Print(cmd.CommandText);
try
{
     adapter.Fill(ds);

     GVars.bLblLastUpdate = DateTime.Now.ToString("HH:mm:ss");
}
catch (Exception ex)
{
}

EDIT: Thanks Vladislav for the answer, corrected code posted below:

string sql;
OleDbDataAdapter adapter;


sql = string.Format(
            "SELECT * FROM [{0}] WHERE [{0}].[Destination] = @Destination AND [{0}].[Next Collection] BETWEEN @NextCollectionA AND @NextCollectionB"
            , GVars.thisFY);

// Create the command object
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Connection = conn;

// Add values to the fields
cmd.Parameters.Add("@Destination", OleDbType.Char).Value = "Henwood";
cmd.Parameters.Add("@NextCollectionA", OleDbType.DBDate).Value = GVars.currentDate;
cmd.Parameters.Add("@NextCollectionB", OleDbType.DBDate).Value = GVars.currentDate.AddDays(1);

adapter = new OleDbDataAdapter(cmd);

try
{
    adapter.Fill(ds);

    GVars.bLblLastUpdate = DateTime.Now.ToString("HH:mm:ss");
}

Answer 1

Try to specify types for the parameters you add.

Another thing I notice is that to your adapter you are passing only the CommandText. You should pass the whole command object.