"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.