OleDbException: no value given for parameters when

2019-09-17 07:10发布

问题:

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!

回答1:

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



标签: c# oledb