I'm encountering an OleDbException whilst running tests on an application I'm developing. The error message given is, 'No value given for one or more required parameters.'
I know this question appears to have been asked several other times on this site, but in each of the cases the problem arises whilst executing an UPDATE
command. My errors are being thrown in my SELECT
command; to add to the confusion, I'm not even trying to use parameters, so I'm not sure what parameters it's expecting. Code for the method which raises the exception is below:
// Array of column letters, used in constructing the select query.
private readonly string[] COLUMN_LABELS = new string[] { "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB",
"BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL",
"BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV",
"BW", "BX", "BY", "BZ" };
public AvailabilityReader(string spreadsheet, List<ServiceType> sundayList)
{
// Set up a connection to the spreadsheet.
this.spreadsheet = spreadsheet;
Connection = new OleDbConnection(string.Format((Spreadsheet.EndsWith(".xlsx") ?
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;" :
"Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;"), Spreadsheet));
// Set up an adapter for the spreadsheet.
ColumnNames = new List<string>();
var columnString = "[E-mail Address] AS Email";
for (int i = 1; i <= sundayList.Count; i++)
{
columnString += string.Format(", Column{0}", i);
ColumnNames.Add(string.Format("Column{0}", i));
}
var selectCommand = new OleDbCommand(string.Format("SELECT {0} FROM [DATA$O5:{1}300]", columnString, COLUMN_LABELS[sundayList.Count - 1]), Connection);
Adapter = new OleDbDataAdapter(selectCommand);
CommandBuilder = new OleDbCommandBuilder(Adapter);
CommandBuilder.QuotePrefix = "[";
CommandBuilder.QuoteSuffix = "]";
// Obtain the table.
var data = new DataSet();
Adapter.Fill(data, "DATA");
table = data.Tables["DATA"];
}
The exception is raised when Adapter.Fill(data, "DATA")
is called (Adapter being a private property of the AvailabilityReader
class).
I honestly have no idea what's going on, but I fully expect to have missed something incredibly simple. Thanks in advance!