MS SQL 2005 auto increment error
So I tried to create a table in my database "Test",
CREATE TABLE TestTbl(
id INT IDENTITY(1,1),
Agent_id VARCHAR(255) NOT NULL
)
After it was created, I tried to add 2 values for the agent via php, but the result is the following:
id | Agent_id
0 8080
0 8081
It does not grow automatically, even if I set "id" as the primary key, the problem still occurs, does anyone know how to fix this?
Here is my insert statement in php, it doesn't matter, $conn
because it works, this is for my SQL connection
if(isset($_POST['agentid'])){
$agent = $_POST['agentid'];
$query = "SELECT * FROM [Test].[dbo].[TestTbl] WHERE [Agent_id] = '$agent'";
$result = sqlsrv_query($conn,$query);
if(sqlsrv_has_rows($result) !=0){
echo "ID EXISTS";
}else{
$sql = "SET INDENTITY_INSERT TestTbl ON
INSERT INTO [Test].[dbo].[TestTbl]
([id],[Agent_id]) VALUES ('','$agent')
SET IDENTITY_INSERT TestTbl OFF";
echo "Added";
}}
source to share
Change this part
Of
INSERT INTO [Test].[dbo].[TestTbl]
([id],[Agent_id]) VALUES ('','$agent')
For
INSERT INTO [Test].[dbo].[TestTbl]
([Agent_id]) VALUES ('$agent')
When it grows automatically, you don't need to specify it in the instructions INSERT
.
Also do not follow from SET IDENTITY_INSERT
to OFF
if you want to use the auto-increment feature in the table
SET IDENTITY_INSERT
allows you to insert explicit values into a table id column.
Your complete inquiry
if(isset($_POST['agentid'])){
$agent = $_POST['agentid'];
$query = "SELECT * FROM [Test].[dbo].[TestTbl] WHERE [Agent_id] = '$agent'";
$result = sqlsrv_query($conn,$query);
if(sqlsrv_has_rows($result) !=0){
echo "ID EXISTS";
}else{
$sql = "INSERT INTO [Test].[dbo].[TestTbl]
([Agent_id]) VALUES ('$agent')";
echo "Added";
}}
source to share