Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql exception, missing parameterized query

Tags:

c#

sql

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?

like image 627
ikathegreat Avatar asked Feb 29 '12 22:02

ikathegreat


1 Answers

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?

like image 163
Marc Gravell Avatar answered Sep 30 '22 12:09

Marc Gravell