c# - sql异常，缺少参数化查询

Question

你好，我正在尝试通过我正在构建的 C# WinForm 将一些值写入我的 SQL。我遇到无法解决的错误。

我这样称呼:

SQL.insertIntoChildTable(Convert.ToInt32(child_IDTextBox.Text), 
                         child_FirstNameTextBox.Text, 
                         child_LastNameTextBox.Text, 
                         Convert.ToInt32(parent1IDTextBox.Text), 
                         Convert.ToInt32(parent2IDTextBox.Text), 
                         birthdayDateTimePicker.Value.ToShortDateString(), 
                         age.ToString(), 
                         null, null, null, null, null, 
                         child_disciplineTextBox.Text, child_NotesTextBox.Text);

来自这里:

public static void insertIntoChildTable(int childID, string firstName, string lastName, int parent1ID, int parent2ID, string birthdate, string age, string lastCheckedInTime, string lastCheckedInBy, string lastCheckOutTime, string lastCheckedOutBy, string ageGroup, string disciplineNotes, string otherNotes)
{
   try
   {
       using (SqlConnection connection = new SqlConnection(Global.connectionString))
       using (SqlCommand command = connection.CreateCommand())
       {
          command.CommandText = "INSERT INTO ProjectList (ChildID, FirstName, LastName, Parent1ID, Parent2ID, Birthdate, Age, LastCheckedInTime, LastCheckedInBy, LastCheckOutTime, LastCheckedOutBy, AgeGroup, DisciplineNotes, OtherNotes) VALUES (@childID, @firstName, @lastName, @parent1ID, @parent2ID, @birthdate, @age, @lastCheckedInTime, @lastCheckedInBy, @lastCheckOutTime, @lastCheckedOutBy, @ageGroup, @disciplineNotes, @otherNotes)";

          command.Parameters.AddWithValue("@childID", childID);
          command.Parameters.AddWithValue("@firstName", firstName);
          command.Parameters.AddWithValue("@lastName", lastName);
          command.Parameters.AddWithValue("@parent1ID", parent1ID);
          command.Parameters.AddWithValue("@parent2ID", parent2ID);
          command.Parameters.AddWithValue("@birthdate", birthdate);
          command.Parameters.AddWithValue("@age", age);
          command.Parameters.AddWithValue("@lastCheckedInTime", lastCheckedInTime);
          command.Parameters.AddWithValue("@lastCheckedInBy", lastCheckedInBy);
          command.Parameters.AddWithValue("@lastCheckOutTime", lastCheckOutTime);
          command.Parameters.AddWithValue("@lastCheckedOutBy", lastCheckedOutBy);
          command.Parameters.AddWithValue("@ageGroup", ageGroup);
          command.Parameters.AddWithValue("@disciplineNotes", disciplineNotes);
          command.Parameters.AddWithValue("@otherNotes", otherNotes);

          connection.Open();
          command.ExecuteNonQuery();
       }
    }
    catch (SqlException ex)
    {
       Console.WriteLine(ex.Message);
    }
}

得到这个:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
The parameterized query '(@childID int,@firstName nvarchar(4),@lastName nvarchar(5),@pare' expects the parameter '@lastCheckedInTime', which was not supplied.

有什么想法吗？

Answer 1

我猜它是 null。不添加 null 的参数。它需要是 DBNull.Value。您可以通过添加 来做到这一点？ DBNull.Value 到每个参数。愚蠢，我知道:

command.Parameters.AddWithValue("@lastCheckInTime",
      lastCheckInTime ?? DBNull.Value);

对于每个参数；或之后遍历它们，将任何 null 修复为 DBNull.Value:

foreach(var param in command.Parameters) {
    if(param.Value == null) param.Value = DBNull.Value;
}

作为附带的东西 - 将它们全部作为 string 听起来不太可能；应该是 DateTime 还是 DateTime？，不是吗？