Share via

"No value given for one or more required parameters." / "Incorrect syntax near the keyword 'DEFAULT'."

  • Article

A follow up on the post from yesterday.

If you forget to add a parameter to a parameterized query, you will end up with the

System.Data.OleDb.OleDbException: No value given for one or more required parameters.

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)

   ...

Example:

            using (OleDbConnection con = new OleDbConnection(cs))

            {

                con.Open();

                OleDbCommand cmd = con.CreateCommand();

                // "No value given for one or more required parameters"

                cmd.CommandText = "SELECT * FROM Categories WHERE CategoryID = ?;";

                OleDbDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    Console.WriteLine("{0} {1}", rdr[0].ToString(), rdr[1].ToString());

                }

                con.Close();

         }

And if you add a parameter to the parameterized query, but forget to assign it a value, it will use the DEFAULT and you will end up with:

System.Data.OleDb.OleDbException: Incorrect syntax near the keyword 'DEFAULT'.

   at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

   at System.Data.OleDb.OleDbDataReader.NextResult()

   ...

Example:

            using (OleDbConnection con = new OleDbConnection(cs))

            {

                con.Open();

                OleDbCommand cmd = con.CreateCommand();

                // 'DEFAULT' is used

                cmd.CommandText = "SELECT * FROM Categories WHERE CategoryID = ?;";

                cmd.Parameters.Add("@id", OleDbType.Integer);

                OleDbDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    Console.WriteLine("{0} {1}", rdr[0].ToString(), rdr[1].ToString());

                }

                con.Close();

            }

This is usually very easy to spot. But if you have many parameters in your query, it may be easy to miss that you have forgot to set a value for

one of the parameters or that it is simply not added to the command.

Comments

  • Anonymous
    May 14, 2012
    what about an update statement that allows my end user to update everything in my access database? What's wrong with this update statement? UPDATE [QMTrainingRecords6] SET [EmailAddress] = ?, [LastName] = ?, [FirstName] = ?, [PeerReviewer] = ?, [MasterReviewer] = ?, [Trainer] = ?, [InstructorCoursesReviewed] = ?, [CourseReviewerHistory] = ?, [QualityMattersTrainingActivity] = ?, [Division] = ? WHERE [Faculty Id] = ? AND (([EmailAddress] = ?) OR ([EmailAddress] IS NULL AND ? IS NULL)) AND (([LastName] = ?) OR ([LastName] IS NULL AND ? IS NULL)) AND (([FirstName] = ?) OR ([FirstName] IS NULL AND ? IS NULL)) AND (([PeerReviewer] = ?) OR ([PeerReviewer] IS NULL AND ? IS NULL)) AND (([MasterReviewer] = ?) OR ([MasterReviewer] IS NULL AND ? IS NULL)) AND (([Trainer] = ?) OR ([Trainer] IS NULL AND ? IS NULL)) AND (([InstructorCoursesReviewed] = ?) OR ([InstructorCoursesReviewed] IS NULL AND ? IS NULL)) AND (([CourseReviewerHistory] = ?) OR ([CourseReviewerHistory] IS NULL AND ? IS NULL)) AND (([QualityMattersTrainingActivity] = ?) OR ([QualityMattersTrainingActivity] IS NULL AND ? IS NULL)) AND (([Division] = ?) OR ([Division] IS NULL AND ? IS NULL))