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


 


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 (1)

  1. Christian says:

    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))