CODEWITHSUNDEEP
c#sql
ikathegreat
ikathegreat

Reputation: 2321

sql exception, missing parameterized query

Hello I'm trying to write some values to my SQL through the C# WinForm I'm building. I'm getting an error I can't resolve.

I am calling this:

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);

From this:

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);
    }
}

And getting this:

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.

Any ideas?

Upvotes: 6

Views: 4297

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062780

I'm guessing it is null. Parameters that are null are not added. It needs to be DBNull.Value. You can do this by adding ?? DBNull.Value to each parameter. Stupid, I know:

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

for every parameter; or loop over them afterwards, fixing any null to DBNull.Value:

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

As a side thing - having those all as string sounds very unlikely; should be DateTime or DateTime?, no?

Upvotes: 13

Related Questions