0

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!

Ian Knight
  • 2,356
  • 2
  • 18
  • 22
  • what is the sql after all the `string.Format` etc? – Marc Gravell Aug 13 '13 at 12:29
  • If ``sundayList`` had four elements, the resulting query would be ``SELECT [E-mail Address] AS Email, Column1, Column2, Column3, Column4 FROM [DATA$O5:AV300]`` – Ian Knight Aug 13 '13 at 12:32
  • And does that work when executed directly in a query tool, rather than via your code? – Marc Gravell Aug 13 '13 at 12:34
  • Good thinking, but I'm not sure how to do that - my data source is an Excel spreadsheet as opposed to a 'proper' database. Can you suggest one that I can try? – Ian Knight Aug 13 '13 at 12:37

1 Answers1

6

This usually occurs when your column names are incorrect. Make sure they are consistent with the table you are getting the data from.

TheTerribleProgrammer
  • 536
  • 1
  • 3
  • 18