title: Inserting data from a WPF form into a MySQL database via C#
body: <p>I'm having a peculiar problem with inserting values into a MySQL database via C#. </p>
  
  <p>I have four controls that I am gathering data from in a WPF form; three are textboxes and one is a datepicker. </p>
  
  <p>After connecting to the database, one fills out the form. I have been using the following values:</p>
  
  <pre><code>txtIdNumber: 1
  dtpDateOfBirth = 7/30/2013 (today)
  txtMenarcheAge = 14
  txtSport1 = Soccer
  </code></pre>
  
  <p>Here's the code it's being washed through:</p>
  
  <pre><code>if (MessageBox.Show("Are you sure you want to submit these changes to the database? Just making sure...", "", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
              {
                  //database logic HERE.
                  DateTime birthday = (DateTime)dtpDateOfBirth.SelectedDate;
                  string birthdayString = birthday.ToString("yyyy-MM-dd");
  
                  MySqlCommand cmd = new MySqlCommand();
                  cmd.Connection = conn;
  
                  Console.WriteLine(Convert.ToString(dtpDateOfBirth.SelectedDate));
  
                  //check each box that MUST contain an entry, then submit it to the database if it's correct
                  if (txtIdNumber.Text != "" &amp;&amp; dtpDateOfBirth.SelectedDate != null)
                  {                        
                      cmd.CommandText = "INSERT INTO TestSubject VALUES(NULL, @subjectId, @subjectDOB)";
                      cmd.Prepare();
                      cmd.Parameters.AddWithValue("@subjectId", txtIdNumber.Text);
                      cmd.Parameters.AddWithValue("@subjectDOB", "'" + birthdayString + "'");
                      Console.WriteLine(birthdayString);
                      Console.WriteLine(cmd.Parameters["@subjectId"].Value);
                      Console.WriteLine(cmd.Parameters["@subjectDOB"].Value);
                      cmd.ExecuteNonQuery();
                  }
                  else if (txtIdNumber.Text != "" &amp;&amp; dtpDateOfBirth.SelectedDate != null &amp;&amp; txtMenarcheAge.Text != "")
                  {
                      cmd.CommandText = "INSERT INTO TestSubject VALUES(@subjectId, @subjectDOB, @subjectMenarche)";
                      cmd.Prepare();
                      cmd.Parameters.AddWithValue("@subjectId", txtIdNumber.Text);
                      cmd.Parameters.AddWithValue("@subjectDOB", birthdayString);
                      cmd.Parameters.AddWithValue("@subjectMenarche", txtMenarcheAge.Text);
                      cmd.ExecuteNonQuery();
                  }
              }
  </code></pre>
  
  <p>First, I convert the datetime from the datepicker into something resembling what MySQL uses. Then, I convert it to a string. </p>
  
  <p>Next, I set up the requisite <code>MySqlCommand</code> object, and set its <code>Connection</code> property to <code>conn</code>, which was established in another class.</p>
  
  <p>After that are checks to make sure the form is valid. An ID number for the test subject and a birthday <em>must</em> be provided, so they're checked first. This is where we run into trouble. I am relatively new to prepared statements in MySQL, so I used <a href="http://dev.mysql.com/doc/refman/5.6/en/connector-net-programming-prepared.html" rel="nofollow">this</a> example in order to get myself oriented. At the block</p>
  
  <pre><code>cmd.CommandText = "INSERT INTO TestSubject VALUES(NULL, @subjectId, @subjectDOB)";
  cmd.Prepare();
  cmd.Parameters.AddWithValue("@subjectId", txtIdNumber.Text);
  cmd.Parameters.AddWithValue("@subjectDOB", "'" + birthdayString + "'");
  Console.WriteLine(birthdayString);
  Console.WriteLine(cmd.Parameters["@subjectId"].Value);
  cmd.ExecuteNonQuery();
  </code></pre>
  
  <p>I encounter the following exception at runtime:</p>
  
  <pre><code>MySql.Data.MySqlClient.MySqlException was unhandled
  
  
  HResult=-2147467259
    Message=Column 'subjectId' cannot be null
    Source=MySql.Data
    ErrorCode=-2147467259
    Number=1048
    StackTrace:
         at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
         at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp; affectedRow, Int64&amp; insertedId)
         at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp; affectedRows, Int64&amp; insertedId)
         at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
         at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
         at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
         at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
         at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
         at mu_kinect_project_data_form.MainWindow.btnSubmitWholeForm_Click(Object sender, RoutedEventArgs e) in c:\Users\asm3kf.TIGERS\Documents\mu_kinect_project_data_form\mu_kinect_project_data_form\MainWindow.xaml.cs:line 315
         at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
         at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
         at System.Windows.Controls.Button.OnClick()
         at System.Windows.Controls.Primitives.ButtonBase.OnMouseLeftButtonUp(MouseButtonEventArgs e)
         at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
         at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
         at System.Windows.UIElement.ReRaiseEventAs(DependencyObject sender, RoutedEventArgs args, RoutedEvent newEvent)
         at System.Windows.RoutedEventArgs.InvokeHandler(Delegate handler, Object target)
         at System.Windows.EventRoute.InvokeHandlersImpl(Object source, RoutedEventArgs args, Boolean reRaised)
         at System.Windows.UIElement.RaiseEventImpl(DependencyObject sender, RoutedEventArgs args)
         at System.Windows.UIElement.RaiseTrustedEvent(RoutedEventArgs args)
         at System.Windows.Input.InputManager.ProcessStagingArea()
         at System.Windows.Input.InputProviderSite.ReportInput(InputReport inputReport)
         at System.Windows.Interop.HwndMouseInputProvider.ReportInput(IntPtr hwnd, InputMode mode, Int32 timestamp, RawMouseActions actions, Int32 x, Int32 y, Int32 wheel)
         at System.Windows.Interop.HwndMouseInputProvider.FilterMessage(IntPtr hwnd, WindowMessage msg, IntPtr wParam, IntPtr lParam, Boolean&amp; handled)
         at System.Windows.Interop.HwndSource.InputFilterMessage(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean&amp; handled)
         at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean&amp; handled)
         at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
         at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
         at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
         at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
         at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
         at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG&amp; msg)
         at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
         at System.Windows.Application.RunInternal(Window window)
         at System.Windows.Application.Run()
         at mu_kinect_project_data_form.App.Main() in c:\Users\asm3kf.TIGERS\Documents\mu_kinect_project_data_form\mu_kinect_project_data_form\obj\Debug\App.g.cs:line 0
         at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
         at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
         at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
         at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.ThreadHelper.ThreadStart()
    InnerException:
  </code></pre>
  
  <p>I'm not sure where it gets the idea that <code>subjectId</code> is <code>null</code>. It's receiving input alright.</p>
  
  <p>EDIT: Adding MySQL script, because that's half the puzzle pieces.</p>
  
  <pre class="lang-sql prettyprint-override"><code>SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
  
  CREATE SCHEMA IF NOT EXISTS `kinect7` DEFAULT CHARACTER SET utf8 ;
  USE `kinect7` ;
  
  -- -----------------------------------------------------
  -- Table `kinect7`.`TestSubject`
  -- -----------------------------------------------------
  CREATE  TABLE IF NOT EXISTS `kinect7`.`TestSubject` (
    `subjectId` INT(11) NOT NULL ,
    `subjectDOB` DATE NOT NULL ,
    `subjectMenarche` DATE NULL DEFAULT NULL ,
    PRIMARY KEY (`subjectId`) ,
    UNIQUE INDEX `subjectId_UNIQUE` (`subjectId` ASC) )
  ENGINE = InnoDB
  COMMENT = '     ';
  
  
  -- -----------------------------------------------------
  -- Table `kinect7`.`SubjectInjury`
  -- -----------------------------------------------------
  CREATE  TABLE IF NOT EXISTS `kinect7`.`SubjectInjury` (
    `injuryDate` DATE NOT NULL ,
    `injuryKnee` VARCHAR(5) NOT NULL ,
    `TestSubject_subjectId` INT(11) NOT NULL ,  
    `surgeryPerformed` VARCHAR(10) NULL,  
    PRIMARY KEY (`TestSubject_subjectId`, `injuryKnee`, `injuryDate`) ,
    CONSTRAINT `fk_PreviousInjury_TestSubject1`
      FOREIGN KEY (`TestSubject_subjectId` )
      REFERENCES `kinect7`.`TestSubject` (`subjectId` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
  
  
  -- -----------------------------------------------------
  -- Table `kinect7`.`SubjectJumps`
  -- -----------------------------------------------------
  CREATE  TABLE IF NOT EXISTS `kinect7`.`SubjectJumps` (
    `jumpDate` DATETIME NOT NULL ,
    `rightAnkleRatio` DECIMAL(6,3) NULL DEFAULT NULL ,
    `leftAnkleRatio` DECIMAL(6,3) NULL DEFAULT NULL ,
    `rightKneeValgus` DECIMAL(6,3) NULL DEFAULT NULL ,
    `leftKneeValgus` DECIMAL(6,3) NULL DEFAULT NULL ,
    `rightKneeFlexion` DECIMAL(6,3) NULL DEFAULT NULL ,
    `leftKneeFlexion` DECIMAL(6,3) NULL DEFAULT NULL ,
    `testSubject_subjectId` INT(11) NOT NULL ,
    PRIMARY KEY (`jumpDate`, `testSubject_subjectId`) ,
    INDEX `fk_subjectJumps_testSubject_idx` (`testSubject_subjectId` ASC) ,
    CONSTRAINT `fk_subjectJumps_testSubject`
      FOREIGN KEY (`testSubject_subjectId` )
      REFERENCES `kinect7`.`TestSubject` (`subjectId` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;
  
  
  -- -----------------------------------------------------
  -- Table `kinect7`.`SubjectSports`
  -- -----------------------------------------------------
  CREATE  TABLE IF NOT EXISTS `kinect7`.`SubjectSports` (
    `sport` VARCHAR(10) NOT NULL ,
    `TestSubject_subjectId` INT(11) NOT NULL ,
    PRIMARY KEY (`TestSubject_subjectId`, `sport`) ,
    CONSTRAINT `fk_SubjectSports_TestSubject1`
      FOREIGN KEY (`TestSubject_subjectId` )
      REFERENCES `kinect7`.`TestSubject` (`subjectId` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;   
  
  SET SQL_MODE=@OLD_SQL_MODE;
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  </code></pre>