MS SQL 2005 Auto Increment Error

Question

So, I have tried to create a table inside 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 this:

id | Agent_id
0     8080
0     8081

It does not auto increment, even if I set 'id' as a Primary key, still the problem occurs, anyone knows how to solve this problem?

Here is my insert statement in php, nevermind the $conn, because it works, it 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";
    }}

Answer 1

Change this part

From

INSERT INTO [Test].[dbo].[TestTbl]
    ([id],[Agent_id]) VALUES ('','$agent')

To

INSERT INTO [Test].[dbo].[TestTbl]
    ([Agent_id]) VALUES ('$agent')

When it's auto increment, you don't' need to specify that in your INSERT statement.

Also do not SET IDENTITY_INSERT to OFF when you want to use auto increment feature of your table

SET IDENTITY_INSERT allows explicit values to be inserted into the identity column of a table.

Your complete query

 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";
}}