sql exception, missing parameterized query

Question

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?

Think you want to specify the command.CommandType = CommandType.Text to be explicit (not necessarily the cause of the problem but good practice). — kaj
– kaj, Commented Feb 29, 2012 at 22:24

Marc Gravell · Accepted Answer · 2012-02-29 22:08:30Z

13

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?

answered Feb 29, 2012 at 22:08

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

1 Comment

ikathegreat Over a year ago

yup null works different in SQL. thanks. and i'm converting everything to text because I'm just trying to get this thing up and running. Casting the values to the correct type WILL come, I just need more experience. thanks again.

Collectives™ on Stack Overflow

sql exception, missing parameterized query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related