locked
"Procedure or function expects parameter which was not supplied." when Passing nullable DateTime RRS feed

  • Question

  • Can someone explain why I'm getting the error:

    "Procedure or function 'prc_AddUpdateCampaign' expects parameter '@startDate', which was not supplied." for this code:

    using (SqlCommand cmd = new SqlCommand())
    {
      cmd.Connection = conn;
      cmd.Transaction = transaction;
      cmd.CommandType = System.Data.CommandType.StoredProcedure;
      cmd.Parameters.Add("@facilityID", SqlDbType.UniqueIdentifier).Value = systemInfo.FacilityID;
      cmd.Parameters.Add("@campaignID", SqlDbType.Int);
      cmd.Parameters.Add("@enabled", SqlDbType.Bit);
      cmd.Parameters.Add("@startDate", SqlDbType.DateTime);
    
      ...
    
      cmd.CommandText = "prc_AddUpdateFacilityCampaign";
      cmd.Parameters["@campaignID"].Value = campaign.CampaignID;
      cmd.Parameters["@enabled"].Value = campaign.Enabled;
      cmd.Parameters["@startDate"].Value = null;
    
      try
      {
        cmd.ExecuteNonQuery();
      }
      catch (SqlException ex)
      {
        ...
      }
      ...
    }
    I'm trying to set @startDate to the value of a nullable DateTime which works when it has a value but not when its null. To demonstrate the issue more clearly I just swapped it out for null above. Can you not pass null for a SQL DateTime parameter or should I declare my parameter as a different type maybe?

    Wednesday, May 12, 2010 2:59 AM

Answers

  • "When you send a null parameter value to the server, the user must specify DBNull, not null.
    The null value in the system is an empty object that has no value. DBNull is used to represent null values."

    From:
    "SqlParameter.Value Property"
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.value.aspx

    "DBNull Class"
    http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

    So, the following will fail:

            cmd.CommandText = "SELECT * FROM Customers WHERE CustomerId = @cid";
            cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar).Value = null;

    This will work:

            cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar);
            cmd.Parameters[0].IsNullable = true;
            cmd.Parameters[0].Value = DBNull.Value;

    HTH
    Michael


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by _xr280xr_ Wednesday, May 12, 2010 3:15 PM
    Wednesday, May 12, 2010 7:09 AM

All replies

  • "When you send a null parameter value to the server, the user must specify DBNull, not null.
    The null value in the system is an empty object that has no value. DBNull is used to represent null values."

    From:
    "SqlParameter.Value Property"
    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.value.aspx

    "DBNull Class"
    http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

    So, the following will fail:

            cmd.CommandText = "SELECT * FROM Customers WHERE CustomerId = @cid";
            cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar).Value = null;

    This will work:

            cmd.Parameters.Add("@cid", System.Data.SqlDbType.NVarChar);
            cmd.Parameters[0].IsNullable = true;
            cmd.Parameters[0].Value = DBNull.Value;

    HTH
    Michael


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by _xr280xr_ Wednesday, May 12, 2010 3:15 PM
    Wednesday, May 12, 2010 7:09 AM
  • That seems like it should've been pretty obvious. At the same time I don't remember ever doing that in the past. Here's what I ended up using:

     

    cmd.Parameters["@startDate"].Value = campaign.StartDate.HasValue ? (campaign.StartDate == DateTime.MinValue ? (object)SqlDateTime.MinValue.Value :
                                                           (object)campaign.StartDate) :
                                      (object)DBNull.Value;

    Thanks Michael!

    Wednesday, May 12, 2010 3:15 PM