Correct Way/location To Use Scope_identity()

I have an auto incrementing ID called deviceID in one of my fields. I was wanting to pass this to a session in php to use later on and was planning on using scope_identity() as I u

Solution 1:

You need to fix some issues in your code:

• The INSERT statement is wrong - you have five columns, but only four values in this statement. I assume, that DeviceID is an identity column, so remove this column from the column list.
• Use parameteres in your statement. Function sqlsrv_query() does both statement preparation and statement execution, and can be used to execute parameterized queries.
• Use SET NOCOUNT ON as first line in your statement to prevent SQL Server from passing the count of rows affected as part of the result set.
• SCOPE_IDENTITY() is used correctly and it should return the expected ID. Of course, depending on the requirements, you may use IDENT_CURRENT().

The following example (based on the code in the question) is a working solution:

<?php
session_start();
include'db.php';

if (isset($_POST['submit'])) { 
    $screenWidth = $_POST['screenWidth'];
    $phoneType = $_POST['phoneName'];
    $screenHeight = $_POST['screenHeight'];
    $HandUsed = $_POST['HandUsed'];

    $params = array($screenWidth, $phoneType, $screenHeight, $HandUsed);
    $sql = "
        SET NOCOUNT ON
        INSERT INTO DeviceInfo (screenWidth, phoneType, screenHeight, HandUsed)
        VALUES (?, ?, ?, ?)
        SELECT SCOPE_IDENTITY() AS DeviceID
    ";
    $stmt = sqlsrv_query($conn, $sql, $params);
    if ($stmt === false) {
        echo"Error: " . $sql . ": " . print_r(sqlsrv_errors());
        exit;
    }

    echo"New record has been added successfully !";
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        echo$row["DeviceID"];
    }
    sqlsrv_free_stmt($stmt);

    sqlsrv_close($conn);
}
?>