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

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

  • Comments 1

 

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.

 

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • 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))

Page 1 of 1 (1 items)