sql异常,缺少参数化查询

本文关键字:参数 查询 异常 sql | 更新日期: 2023-09-27 18:26:07

你好,我正试图通过我正在构建的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);
    }
}

得到这个:

System.Data.dll中首次出现类型为"System.Data.SqlClient.SqlException"的异常
参数化查询"(@childID int,@firstName nvarchar(4),@lastName nvarchal(5),@pare"需要参数"@lastCheckedInTime",但未提供。

有什么想法吗?

sql异常,缺少参数化查询

我猜是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?,不是吗?