Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Question

there's an error saying "Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF." what is the possible solution to this problem? I am using SQL server 2014 and php

<style>
	th{text-align: right; border-style: none;}
	td{
		width: 10%;
	}
	table{border-spacing:15px; width:20%;}
	.button {
    background-color: black;
    border: none;
    color: white;
    padding: 15px 32px;
    text-align: center;
    text-decoration: none;
    display: inline-block;
    font-size: 16px;
    margin: 4px 2px;
    cursor: hand;
	border-radius:12px;
	}
	.button:hover{
		background-color: grey;
		color: black;
	}
	input[type=number]{
    width:200px;
	}
	input[type=date]{
	width: 200px;
	}
	input[type=number]::-webkit-inner-spin-button, 
	input[type=number]::-webkit-outer-spin-button { 
	-webkit-appearance: none; 
	margin: 0; 
	}
	
</style>
<center>
<form action="" method="POST" enctype="multipart/form-data">
<head>
<script language="javascript" type="text/javascript">
function removeSpaces(string) {
 return string.split(' ').join('');
}
</script>
</head>
<table frame=box>
<tr><td colspan=5><b><center><font size=6>CUSTOMER PROFILE</font></center></b></td></tr>
<tr><th>REC NUMBER:</th><td><input type=number name="rec_no"></td><th>CUSTOMER ID:</th><td><input type=text name="cust_ID" size=24 required></td></tr>
<tr><th>CUSTOMER NAME:</th><td><Input type=text name="cust_name" size=24  required></td><th>SEC-REGISTERED NAME:</th><td><input type=text name="secname" size=24 required></td></tr>
<tr><th>TIN NUMBER:</th><td><input type=number name="tin"  min="100000000" max="999999999" required></td>
<td><select name="store_type">
<option value="">STORE TYPE</option>
<option value="">--</option>
<option value="">--</option></td>
</select></tr>
<tr><td colspan=2><b><u><font size=5>ORGANIZATION AND BUSINESS:</font></b></u></td></tr>
<tr><th>SIZE OF BUSINESS:</th><td><input type=text name="size_business" placeholder="(Last FY Average)"  size=24 required></td><th>SELLER ID:</th><td><input type="number" name="seller_id" min="1" max="12" required></td></tr>
<!--</td>-->
<tr><th>DATE OF ESTABLISHMENT:</th><td><input type="date" name="date_established" min="01-01-1900" required></td></tr>
<tr><th>ADDRESS(Headquarters):</th><td><input type="text" name="street" placeholder="House Number/Street" size=24 onblur="this.value=removeSpaces(this.value);" required></td><td><input type=text name="barangay" placeholder="Barangay"  size=24 required></td><td><input type=text name="city" placeholder="City / Municipality" size=24 onblur="this.value=removeSpaces(this.value);" required></td></tr>
<tr><th>&nbsp</th><td><input type=text name="province" placeholder="Province" size=24 onblur="this.value=removeSpaces(this.value);"></td><td><input type=number name="postal_code" placeholder="Postal Code" min="1000" max="9999"></td></tr>
<tr><th>TELEPHONE# / FAX:</th><td><input type=text name="tel_num" required></td><th>PAYMENT TERMS:</th><td><input type=text name="payment_terms" size=24></td></tr>
<tr><th>SHIPPING INSTRUCTIONS:</th><td colspan=3><input type=text name="shipping_inst" size=90></td></tr>
<tr><th>NUMBER OF DOORS:</th><td><input type=text name="doors_num" size=24></td><th>NUMBER OF WAREHOUSES:</th><td><input type=text name="warehouse_num" size=24></td></tr>
<tr><td colspan=2>&nbsp&nbsp&nbsp&nbsp&nbsp <b><u><font size=5>CONTACT PERSONNEL:</font></b></u></td><td>&nbsp </td><td>Birthday:</td></tr>
<tr><th>OWNER:</th><td><input type=text name="owner_name" size=24 ></td><td><input type=number name="owner_contact" size=24 placeholder="Contact Number"></td><td><input type=date name="owner_bday" placeholder="Date of Birth" size=23></td><td><input type=text name="owner_interest" placeholder="Interest"></td></tr>
<tr><th>PURCHASER/S:</th><td><input type=text name="purch_name" size=24 ></td><td><input type=number name="purch_contact" placeholder="Contact Number"></td><td><input type=date name="purch_bday" placeholder="Date of Birth" size=23 ></td><td><input type=text name="purch_interest" placeholder="Interest"></td></tr>
<tr><th>&nbsp&nbspACCOUNTING HEAD:</th><td><input type=text name="acc_name" size=24 ></td><td><input type=number name="acc_contact" placeholder="Contact Number"></td><td><input type=date name="acc_bday" placeholder="Date of Birth" size=23></td><td><input type=text name="acc_interest" placeholder="Interest"></td></tr>
<tr><th>WAREHOUSE HEAD:</th><td><input type=text name="wh_name" size=24 ></td><td><input type=number name="wh_contact" placeholder="Contact Number"></td><td><input type=date name="wh_bday" placeholder="Date of Birth" size=23></td><td><input type=text name="wh_interest" placeholder="Interest"></td></tr>
<tr><th>OTHER PERSONNEL:</th><td><input type=text name="other_name" size=24 ></td><td><input type=number name="other_contact" placeholder="Contact Number"></td><td><input type=date name="other_bday" placeholder="Date of Birth" size=23></td><td><input type=text name="other_interest" placeholder="Interest"></td></tr>

<tr><td colspan=2><b><u><font size=5>TERMS AND DISCOUNTS:</font></b></u></td></tr>
<tr><th>PAYMENT TERMS:</th><td><input type=text name="payment_terms2" size=24 ></td><th>COLLECTION SCHEDULE:</th></tr>
<tr><th>DISCOUNT:</th><td><input type=text name="discount" size=24 ></td><td colspan=2><input type=text name="coll_sched" size="50" placeholder="e.g Every Wednesday after lunch, MWF Mornings"  ></td></tr>
<tr><td colspan=2>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b><u><font size=5>BUSINESS GOALS:</font></b></u></td></tr>
<tr><th>VOLUME:</th><td><input type=text name="volume" size=24 ></td><th>CSL:</th><td><input type=text name="csl" size=23></td></tr>
<tr><th>MERCHANDISING:</th><td><input type=text name="merchandising" size=24 ></td><th>ASSORTMENT:</th><td><input type=text name="assortment" size=23></td></tr>
<tr><th>VEHICLE:</th><td><input type=text name="vehicle" size=24 ></td><th>PRICING:</th><td><input type=text name="pricing" size=23></td></tr>
<tr><th>DISTRIBUTION:</th><td><input type=text name="distribution" size=24 ></td><th>MARGIN:</th><td><input type=text name="margin" size=23 placeholder="Customer Profitability"></td></tr>
<tr><td colspan=2>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b><u><font size=5>STRATEGIES:</font></b></u></td></tr>
<tr><th>PRICE:</th><td><input type=text name="price" size=24 ></td><th>PEOPLE:</th><td><input type=text name="people" size=23></td></tr>
<tr><th>PROMOTION:</th><td><input type=text name="promotion" size=24 ></td><th>PRODUCT:</th><td><input type=text name="product" size=23></td></tr>
<tr><th>PLACE:</th><td><input type=text name="place" size=24 ></td><th>OTHERS:</th><td><input type=text name="others" size=23></td></tr>
<tr><th>CATMAN ENROLLMENT:</th><td><input type=text name="catman" size=24 ></td></tr>
<tr><td colspan=2><font size=5>&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <b><u>POLICIES:</u></b></font></td></tr>
<tr><th>REPLENISHMENT ORDERS:</th><td colspan=3><input type=text name="rep_orders" size=92></td></tr>
<tr><th>ASSORTMENT/MERCHANDISING:</th><td colspan=3><input type=text name="assort_merch" size=92></td></tr>
<tr><th>NEW PRODUCTS:</th><td colspan=3><input type=text name="new_prod" size=92></td></tr>
<tr><th>PRICING/PROMOTION:</th><td colspan=3><input type=text name="price_promote" size=92></td></tr>
<tr><th>UPLOAD PICTURE:</th><td colspan=2><input type=file name="image" size=92></td></tr>
</table>
<input type="submit" class="button" name="submit" value="Submit">
<input type="button" value="Cancel" class="button" onclick="window.location.href='page_accounting.php'">
</form>
<?php
if (isset($_POST['submit']))
{
$serverName = "QWERTY-PC\SQLEXPRESS";
$connectionInfo = array( "Database"=>"customerdb", "UID"=>"dbadmin", "PWD"=>"qwerty" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}
	
	$street = $_POST['street'];
	$barangay = $_POST['barangay'];
	$city = $_POST['city'];
	$province = $_POST['province'];

	$address = $street .'+'. $barangay .'+'.$city .'+'.$province;
	$url = "http://maps.google.com/maps/api/geocode/json?address=$address&sensor=false&region=Philippines";
	$response = file_get_contents($url);
	$response = json_decode($response, true);
	//print_r($response);
	$lat = $response['results'][0]['geometry']['location']['lat'];
	$long = $response['results'][0]['geometry']['location']['lng'];

$rec_no = $_POST['rec_no'];
$cust_ID = $_POST['cust_ID'];
$cust_name = $_POST['cust_name'];
$secname = $_POST['secname'];
$tin = $_POST['tin'];
$store_type = $_POST['store_type'];
$size_business = $_POST['size_business'];
$date_established = $_POST['date_established'];
$seller_id = $_POST['seller_id'];
$street = $_POST['street'];
$barangay = $_POST['barangay'];
$city = $_POST['city'];
$province = $_POST['province'];
$tel_num = $_POST['tel_num'];
$payment_terms = $_POST['payment_terms'];
$shipping_inst = $_POST['shipping_inst'];
$doors_num = $_POST['doors_num'];
$warehouse_num = $_POST['warehouse_num'];
$owner_name = $_POST['owner_name'];
$owner_contact = $_POST['owner_contact'];
$owner_bday = $_POST['owner_bday'];
$owner_interest = $_POST['owner_interest'];
$purch_name = $_POST['purch_name'];
$purch_contact = $_POST['purch_contact'];
$purch_bday = $_POST['purch_bday'];
$purch_interest = $_POST['purch_interest'];
$acc_name = $_POST['acc_name'];
$acc_contact = $_POST['acc_contact'];
$acc_bday = $_POST['acc_bday'];
$acc_interest = $_POST['acc_interest'];
$wh_name = $_POST['wh_name'];
$wh_contact = $_POST['wh_contact'];
$wh_bday = $_POST['wh_bday'];
$wh_interest = $_POST['wh_interest'];
$other_name = $_POST['other_name'];
$other_contact = $_POST['other_contact'];
$other_bday = $_POST['other_bday'];
$other_interest = $_POST['other_interest'];
$payment_terms2 = $_POST['payment_terms2'];
$discount = $_POST['discount'];
$coll_sched = $_POST['coll_sched'];
$volume = $_POST['volume'];
$csl = $_POST['csl'];
$merchandising = $_POST['merchandising'];
$assortment = $_POST['assortment'];
$vehicle = $_POST['vehicle'];
$pricing = $_POST['pricing'];
$distribution = $_POST['distribution'];
$margin = $_POST['margin'];
$price = $_POST['price'];
$people = $_POST['people'];
$promotion = $_POST['promotion'];
$product = $_POST['product'];
$place = $_POST['place'];
$others = $_POST['others'];
$catman = $_POST['catman'];
$rep_orders = $_POST['rep_orders'];
$assort_merch = $_POST['assort_merch'];
$new_prod = $_POST['new_prod'];
$price_promote = $_POST['price_promote'];
$name= $_FILES['image']['name'];
$image= $_FILES['image']['tmp_name'];
$imageType = $_FILES['image']['type'];
$image = file_get_contents($image);
$image = base64_encode($image);

$sql = "INSERT INTO Customer_Details (Rec_No, Cust_ID, Cust_Name, SEC_Name, Store_Type,[TIN Number],[Date of Establishment],[Size of Business],Seller_ID,Latitude, Longitude, [Telephone/Fax],[Payment Terms],[Shipping Instructions],[Number of Doors],[Number of Warehouses],Owner,[Purchaser(s)],[Accounting Head],[Warehouse Head],[Other Personnel],[Payment Terms 2],Discount,[Collection Schedule],Volume,CSL,[Marketing Vehicle],Distribution, Assortment,Merchandising, Pricing,Margin,Price,Promotion,People,Others,[Catman Enrollment],[Replenishment Orders],[Assortment/Merchandising],[New Products],[Pricing/Promotions], image, street, barangay, city, province) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$params = array($rec_no, $cust_ID, $cust_name, $secname, $store_type, $tin, $date_established, $size_business, $seller_id, $lat, $long, $tel_num, $payment_terms, $shipping_inst, $doors_num, $warehouse_num,$owner_name,$purch_name,$acc_name,$wh_name,$other_name,$payment_terms2,$discount,$coll_sched,$volume,$csl,$vehicle,$distribution,$assortment,$merchandising,$pricing,$margin,$price,$promotion,$people,$others,$catman,$rep_orders,$assort_merch,$new_prod,$price_promote, $image, $street, $barangay, $city, $province);
/*$sql2 = "INSERT INTO Customer_Master (Cust_ID, Internal_ID, Address1, Address2, City, Company_ID) VALUES (?, ?, ?, ?, ?, ?)";
$params2 = array($cust_ID, $seller_id, $street, $barangay, $city, $tin);
*/
$stmt = sqlsrv_query($conn, $sql, $params);
/*O$stmt = sqlsrv_query($conn, $sql2, $params2);*/
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}
}
?>

Errr, turn it ON or don't insert into that field??? The error message is quite explicit... — MatBailie

Kahn Kahn · Accepted Answer · 2016-04-25T06:27:03

Google should've given you an answer.

IDENTITY is a type of column where the value increases automatically. You're attempting to manually insert a value into it, which can't be done unless you first enable the proper setting to do so.

See https://msdn.microsoft.com/en-us/library/ms188059.aspx

So for instance let's say you have a table like this:

CREATE TABLE MYTABLE (ID INT IDENTITY(1,1), MYVALUE VARCHAR(25))

Then normally you would insert a value like so:

INSERT INTO MYTABLE (MYVALUE) VALUES ('ASDASD')

And SQL Server would automatically generate the value for the ID field starting from number 1, increased by 1 for every subsequent insert.

In order to be able to manually insert that ID value in addition to MYVALUE, you would need to do the following instead:

SET IDENTITY_INSERT MYTABLE ON;

INSERT INTO MYTABLE (ID, MYVALUE) VALUES (2, 'ASDASD');

SET IDENTITY_INSERT MYTABLE OFF;

This latter insert is basically what your solution is trying to do, but it lacks the IDENTITY_INSERT settings. Hence, you get the error. How this relates to PHP, I've no clue. But this should help you get somewhere at least with regards to the actual SQL Server error.

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

1 Answers