Reputation: 1724
PDO_SQLSRV returns no error, cant insert values
I want to insert values to MS SQL using PHP PDO prepared statement, I am sure I am successfully connected to SQL Server, But I have no Idea why can't I insert data on it.
This is how I connect to SQL server
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<h1 align='center'>Paycom Projects</h1>
<h5 align='center'>Paycom CRUD</h5><br/>
<?php
$serverName = "EDGEWEBMEDIA-PC\SQLEXPRESS";
/* Connect using Windows Authentication. */
try
{
$conn = new PDO( "sqlsrv:server=$serverName ; Database=paycom", "sa", "edgeweb");
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
}
catch(Exception $e)
{
die( print_r( $e->getMessage() ) );
}
insert.php
<?php
include_once('connect.php');
$stmt = $conn->prepare("INSERT INTO employee (firstname, lastname, email)
VALUES (:firstname, :lastname, :email)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
// insert a row
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
// insert another row
$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
// insert another row
$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute() or die(print_r($stmt->errorInfo(), true));
echo "New records created successfully";
}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}
$conn = null;
What is wrong with this? I already tried this using a Framework(Laravel) in the same machine, and it work flawlessly, But I want to test this using plain php(no framework) .
Tables, columns is manually added through sql management studio
UPDATE
Now I know the exact error is, as I added
ini_set('display_errors',1);
error_reporting(E_ALL | E_STRICT);
Now the error is related to null id values
[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'id',
My updated question is, do I have prevent this error?
Update
Now I am able to insert data by modifying the table, id field is set to nullabler, but I know this not acceptable since we need id for every data in table when performing query
Upvotes: 1
Views: 273
Answers (1)
Reputation: 1724
Got it
This is how I solved it
Drop the table and recreate
set id as not null, primary, and auto increment
Then in design
Under Indentity Specification, set (Is Identity)=Yes and Indentity Increment=1
That's it
Upvotes: 1