OleDbException: no value given for parameters when no parameters are defined

Question

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!

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
– Ian Knight, Commented Aug 13, 2013 at 12:32
And does that work when executed directly in a query tool, rather than via your code? — Marc Gravell
– Marc Gravell, Commented Aug 13, 2013 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
– Ian Knight, Commented Aug 13, 2013 at 12:37

TheTerribleProgrammer · Accepted Answer · 2013-08-13 12:29:36Z

6

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

answered Aug 13, 2013 at 12:29

TheTerribleProgrammer

5461 gold badge3 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

OleDbException: no value given for parameters when no parameters are defined

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related