INSERT does not work now that I have Identity Column

Question

I have an HTML Table that has an insert button. This used to work just fine. However, I needed my first column to be an Identity column. So after setting one of the columns, MR_ID, as an Identity, my script no longer inserts into the table and database. In the console, it says "row failed to insert" when it used to say "row inserted" before this column was set to Identity. Can someone please tell me what is going on here? I have a lot of code but I want to make sure I post what is necessary to figure out the problem.

Dialog box and table that is being looped in:

<div id="dialog-form" title="Add Vendor">
  <p class="validateTips">All form fields are required.</p>

<!-- Dialog box displayed after add row button is clicked -->
  <form>
    <fieldset>
      <label for="mr_name">Vendor</label>
      <input type="text" name="mr_name" id="mr_name" class="text ui-widget-content ui-corner-all" value="test">
      <label for="buyer_id">Buyer ID</label>
      <input type="text" name="buyer_id" id="buyer_id" class="text ui-widget-content ui-corner-all" value="99">
      <label for="poc_n">POC Name</label>
      <input type="text" name="poc_n" id="poc_n" class="text ui-widget-content ui-corner-all" value="name">
      <label for="poc_p">POC Email</label>
      <input type="text" name="poc_e" id="poc_e" class="text ui-widget-content ui-corner-all" value="test@tst.com">
      <label for="poc_p">POC Phone</label>
      <input type="text" name="poc_p" id="poc_p" class="text ui-widget-content ui-corner-all" value="5555555555">

      <!-- Allow form submission with keyboard without duplicating the dialog button -->
      <input type="submit" id="submit" tabindex="-1" style="position:absolute; top:-1000px">
    </fieldset>
  </form>
</div>



<div id="users-contain" class="ui-widget">  
<table id="html_master" class="ui-widget ui-widget-content">
<thead>
    <tr class="ui-widget-header">
    <td>ID</td>
    <td>Vendor</td>
    <td>Buyer ID</td>
    <td>POC Name</td>
    <td>POC Email</td>
    <td>POC Phone</td>
    <td>Edit</td>
    </tr>
</thead>
<tbody>


<?php
    /* Foreach loop that brings in information to populate table */
    foreach ($dbh->query($sql) as $rows){
    ?>
    <tr id="<?php echo intval ($rows['MR_ID'])?>">
        <td class="mr_id" id="<?php echo intval ($rows['MR_ID'])?>" contenteditable="false"><?php echo intval ($rows['MR_ID'])?></td>
        <td class="mr_name" id="mr_name-<?php echo intval ($rows['MR_ID'])?>" name="field" contenteditable="false"><?php echo $rows['MR_Name']?></td>
        <td class="buyer_id" id="buy_id<?php echo intval ($rows['MR_ID'])?>" contenteditable="false"><?php echo $rows['Buyer_ID']?></td>
        <td class="poc_n" id="poc_n-<?php echo intval ($rows['MR_ID'])?>" contenteditable="false"><?php echo $rows['MR_POC_N']?></td>     
        <td class="poc_e" id="poc_e-<?php echo intval ($rows['MR_ID'])?>" contenteditable="false"><?php echo $rows['MR_POC_E']?></td>
        <td class="poc_p" id="poc_p-<?php echo intval ($rows['MR_ID'])?>" contenteditable="false"><?php echo $rows['MR_POC_P']?></td>
        <td><input type="button" class="edit" name="edit" value="Edit">
    </tr>
 <?php
  }
 ?>
</tbody>
</table>

Javascript:

// ----- Dialog Box for adding a row -----

$( function() {   

    var dialog, form,

      emailRegex = /^[a-zA-Z0-9.!#$%&'*+\/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$/,
      phoneRegex = /^(?:(?:\+?1\s*(?:[.-]\s*)?)?(?:\(\s*([2-9]1[02-9]|[2-9][02-8]1|[2-9][02-8][02-9])\s*\)|([2-9]1[02-9]|[2-9][02-8]1|[2-9][02-8][02-9]))\s*(?:[.-]\s*)?)?([2-9]1[02-9]|[2-9][02-9]1|[2-9][02-9]{2})\s*(?:[.-]\s*)?([0-9]{4})(?:\s*(?:#|x\.?|ext\.?|extension)\s*(\d+))?$/,
      mr_name = $( "#mr_name" ),
      buyer_id = $( "#buyer_id" ),
      poc_n = $( "#poc_n" ),
      poc_e = $( "#poc_e" ),
      poc_p = $( "#poc_p" ),
      allFields = $( [] ).add( mr_name ).add( buyer_id ).add( poc_n ).add( poc_e ).add( poc_p ),
      tips = $( ".validateTips" );
  console.log(allFields);

    function updateTips( t ) {
      tips
        .text( t )
        .addClass( "ui-state-highlight" );
      setTimeout(function() {
        tips.removeClass( "ui-state-highlight", 1500 );
      }, 500 );
    }

    function checkRegexp( o, regexp, n ) {
      if ( !( regexp.test( o.val() ) ) ) {
        o.addClass( "ui-state-error" );
        updateTips( n );
        return false;
      } else {
        return true;
      }
    }

   function addVendor() {
      var valid = true;
      allFields.removeClass( "ui-state-error" );
// ----- Validation for each input in add row dialog box -----
      valid = valid && checkRegexp( mr_name, /^[a-z]([0-9a-z_\s])+$/i, "Please enter a valid vendor name" );
      valid = valid && checkRegexp( buyer_id, /^(0|[1-9][0-9]*)$/, "Please enter a valid Buyer ID" );
      valid = valid && checkRegexp( poc_n, /^[a-zA-Z ]*$/, "Please enter a valid name" );
      valid = valid && checkRegexp( poc_e, emailRegex, "Please enter a valid email" );
      valid = valid && checkRegexp( poc_p, phoneRegex, "Please enter a valid phone number" );
      console.log(allFields);
      if ( valid ) {
        var $tr = $( "#html_master tbody tr" ).eq(0).clone();
        var dict = {};
        var errors = "";
        $.each(allFields, function(){
          $tr.find('.' + $(this).attr('id')).html( $(this).val()+"-"+buyer_id );
          var type = $(this).attr('id');
          var value = $(this).val();
          console.log(type + " : " + value);
          // ----- Switch statement that provides validation for each table cell -----
          switch (type) {
             case "mr_id":
                dict["MR_ID"] = value;
              break;
            case "mr_name":
                dict["MR_Name"] = value;
              break;
            case "buyer_id":
                dict["Buyer_ID"] = value;
              break;
            case "poc_n":
                dict["MR_POC_N"] = value;
              break;
            case "poc_e":
                dict["MR_POC_E"] = value;
                break;
            case "poc_p":
                dict["MR_POC_P"] = value;
                break;
            }
        });
        console.log(dict);
        $tr.find('.mr_id').html( $( "#html_master tbody tr" ).length + 1 );
        $( "#html_master tbody" ).append($tr);
        dialog.dialog( "close" );


        var request = $.ajax({
          type: "POST",
          url: "insert-copy.php",
          data: dict
        });

        request.done(function (response, textStatus, jqXHR){
          if(JSON.parse(response) == true){
            console.log("row inserted");
          } else {
            console.log("row failed to insert");
            console.log(response);
          }
        });

        // Callback handler that will be called on failure
        request.fail(function (jqXHR, textStatus, errorThrown){
            console.error(
                "The following error occurred: "+
                textStatus, errorThrown
            );
        });

        // Callback handler that will be called regardless
        // if the request failed or succeeded
        request.always(function () {

        });


      }
      return valid;
    }

    var dialog = $( "#dialog-form" ).dialog({
      autoOpen: false,
      height: 400,
      width: 350,
      modal: true,
      buttons: {
        "Add Row": addVendor,
        Cancel: function() {
          dialog.dialog( "close" );
        }
      },
      close: function() {
        form[ 0 ].reset();
        allFields.removeClass( "ui-state-error" );
      }
    });

    form = dialog.find( "form" ).on( "submit", function( event ) {
      event.preventDefault();
      addVendor();
    });

    $( ".create-user" ).button().on( "click", function() {
      dialog.dialog({
          position: ['center', 'top'],
          show: 'blind',
          hide: 'blind'
      });
      dialog.dialog("open");
    });
  });

Insert into table function along with Ajax script:

 function insertIntoTable() {

    var tableName = document.getElementById("tableNameInput").value;

    var dict = { tableName: tableName, mrName: 'Temp Object' };

    request = $.ajax({
      type: "POST",
      url: "insert-copy.php",
      data: dict
    });

    request.done(function (response, textStatus, jqXHR){
      if(JSON.parse(response) == true){
        console.log("row inserted");
      } else {
        console.log("row failed to insert");
      }
    });

    // Callback handler that will be called on failure
    request.fail(function (jqXHR, textStatus, errorThrown){
        // Log the error to the console
        console.error(
            "The following error occurred: "+
            textStatus, errorThrown
        );
    });

    // Callback handler that will be called regardless
    // if the request failed or succeeded
    request.always(function () {

    });

  }



<?php

  $MR_ID = $_POST['MR_ID'];
  $MR_Name = $_POST['MR_Name'];
  $Buyer_ID = $_POST['Buyer_ID'];
  $MR_POC_N = $_POST['MR_POC_N'];
  $MR_POC_E = $_POST['MR_POC_E'];
  $MR_POC_P = $_POST['MR_POC_P'];

  $host="xxxxxxxxxxx"; 
  $dbName="xxxxxx"; 
  $dbUser="xxxxxxxxxxxxxx"; 
  $dbPass="xxxxxxxxx";

  $pdo = new PDO("sqlsrv:server=".$host.";Database=".$dbName, $dbUser, $dbPass);

  $sql = "INSERT INTO Stage_Rebate_Master (MR_ID, MR_Name, Buyer_ID, MR_POC_N, MR_POC_E, MR_POC_P) VALUES (?, ?, ?, ?, ?, ?)";
  $stmt = $pdo->prepare($sql);
  $result = $stmt->execute(array($MR_ID, $MR_Name, $Buyer_ID, $MR_POC_N, $MR_POC_E, $MR_POC_P));
  echo json_encode($result);

?>

Answer 1

Gathering up the comments:

• You have a not null constraint on your MR_ID column, so you cannot insert null values.
• If you don't want the user to provide the ID, you (PHP) or the database must generate the values for the non-null column.
• If you have an auto-increment column, the value will be automatically generated by the database and you must not insert into that column. (Except when identity insert is on, but you should not do that.)
• If your column is not auto-increment, then you must make sure that in every insert you include a non-null integer value.
• If your column is also unique, make sure you only try to insert new values.