0

I have this method here:

public List<CustomerQuestionsClass> updateCustomersQuestions(List<CustomerQuestionsClass> items)
{
    connection = new SqlConnection(connectionString);
    command = new SqlCommand(@"IF EXISTS(SELECT * FROM CustomerQuestions WHERE CustomerID = @CustomerID AND QuestionID = @QuestionID)
                               BEGIN
                                    UPDATE CustomerQuestions SET selected = @Selected,  ModifiedBy = @ModifiedBy, DateModified = @DateModified WHERE CustomerID = @CustomerID AND QuestionID = @QuestionID
                               END
                               ELSE
                               BEGIN
                                    INSERT INTO CustomerQuestions (CustomerID, QuestionID, selected, CreatedBy, 
                                        DateCreated, ModifiedBy, DateModified, DateCompleted, DueDate) 
                                    VALUES (@CustomerID, @QuestionID, @Selected, @CreatedBy, @DateCreated, 
                                        @ModifiedBy, @DateModified, @DateCompleted, @DueDate)
                               END");

    command.Parameters.Add("@CustomerID", System.Data.SqlDbType.Int);
    command.Parameters.Add("@QuestionID", System.Data.SqlDbType.Int);
    command.Parameters.Add("@Selected", System.Data.SqlDbType.Bit);
    command.Parameters.Add("@CreatedBy", System.Data.SqlDbType.Text);
    command.Parameters.Add("@DateCreated", System.Data.SqlDbType.DateTime);
    command.Parameters.Add("@ModifiedBy", System.Data.SqlDbType.Text);
    command.Parameters.Add("@DateModified", System.Data.SqlDbType.DateTime);
    command.Parameters.Add("@DateCompleted", System.Data.SqlDbType.DateTime);
    command.Parameters.Add("@DueDate", System.Data.SqlDbType.DateTime);

    command.Connection = connection;
    connection.Open();

    for (int i = 0; i < items.Count; i++)
    {
        command.Parameters["@CustomerID"].Value = items[i].CustomerID;
        command.Parameters["@QuestionID"].Value = items[i].QuestionID;
        command.Parameters["@Selected"].Value = items[i].selected;
        command.Parameters["@CreatedBy"].Value = items[i].Username;
        command.Parameters["@DateCreated"].Value = DateTime.Now;
        command.Parameters["@ModifiedBy"].Value = items[i].Username;
        command.Parameters["@DateModified"].Value = DateTime.Now;
        command.Parameters["@DateCompleted"].Value = items[i].DateCompleted.Equals(DateTime.MinValue) ? (DateTime?)null : items[i].DateCompleted;
        command.Parameters["@DueDate"].Value = items[i].DueDate.Equals(DateTime.MinValue) ? (DateTime?)null : items[i].DueDate;
        command.ExecuteNonQuery();
    }

    connection.Close();

    return items;

}

It's trying to do an insert. But I keep getting this error:

The parameterized query '(@CustomerID int,@QuestionID int,@Selected bit,@CreatedBy text,@' expects the parameter '@DateCompleted', which was not supplied.

When I remove everything having to do with DateCompleted the query runs and data gets inserted, but with the DateCompleted it does not work and I get the error above. The value for DateCompleted is null.

1
  • just pass DBNull.Value Commented Nov 27, 2015 at 21:49

1 Answer 1

6

You have to explicitly pass DBNull.Value as a parameter value, not a null:

command.Parameters["@DateCompleted"].Value = 
        items[i].DateCompleted.Equals(DateTime.MinValue) ? (object)DBNull.Value : items[i].DateCompleted;

The cast to object is necessary so that the type of the expression can be resolved.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.