php - Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF -
there's error saying "cannot insert explicit value identity column in table when identity_insert set off." possible solution problem? using sql server 2014 , 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 , 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> </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>      <b><u><font size=5>contact personnel:</font></b></u></td><td>  </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>  accounting 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 , 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>                    <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>                                 <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>                            <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®ion=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 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 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)); } } ?>
google should've given answer.
identity type of column value increases automatically. you're attempting manually insert value it, can't done unless first enable proper setting so.
see https://msdn.microsoft.com/en-us/library/ms188059.aspx
so instance let's have table this:
create table mytable (id int identity(1,1), myvalue varchar(25)) then insert value so:
insert mytable (myvalue) values ('asdasd') and sql server automatically generate value id field starting number 1, increased 1 every subsequent insert.
in order able manually insert id value in addition myvalue, need following instead:
set identity_insert mytable on; insert mytable (id, myvalue) values (2, 'asdasd'); set identity_insert mytable off; this latter insert solution trying do, lacks identity_insert settings. hence, error. how relates php, i've no clue. should somewhere @ least regards actual sql server error.
Comments
Post a Comment