Problem retrieving inserted id

6

I created a record with a 58 data.

As I was creating, I was testing and the data was entering the DB, and every time an insert was made, I get the id of the last set / data group entered.

But if I "refresh on the page" when I am going to make a new record, the data is no longer registered in the DB, it is as if the id of the last record was lost, because I put echo to show id created and now I do not see more.

The error that happens is:

  

Array ([0] => Array (           [0] = > IMSSP [SQLSTATE] = > IMSSP [1] = > -14           [code] = > -14           [2] = > An invalid parameter was passed to sqlsrv_next_result.          [message] = > An invalid parameter was passed to sqlsrv_next_result. )) 1

Can anyone tell me what might be happening?

$sqlProto = "INSERT INTO [RDO].[dbo].[Protocolo] (titulo, $transicaoCentralCorresp) VALUES (?,?)";

$sqlProto = "INSERT INTO [RDO].[dbo].[Protocolo] (titulo, $transicaoCentralCorresp) VALUES (?,?); SELECT SCOPE_IDENTITY() AS last_id ";

$paramsProto = array($titulo, $transicaoCentralCorresp);

$stmt = sqlsrv_query( $conn, $sqlProto, $paramsProto);

if($next_result){
   $item = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
   $uId = $item['last_id'];
   echo "Último id inserido ". $uId."<br>";
}else{
   die(print_r(sqlsrv_errors()));
}
    
asked by anonymous 16.12.2014 / 18:12

2 answers

1

For cases where the return of the automatically generated id is required, SQL Server has the OUTPUT clause .

In most cases it is recommended to use it in comparison to SCOPE_IDENTITY () .

See the example below.

Tabela: produto
idproduto - int - autoincremento
descricao - varchar(40)

When you run insert , the id will already be returned by the OUTPUT clause:

INSERT INTO produto (descricao) 
OUTPUT inserted.idproduto 
VALUES ('armário');

More information here: link

    
20.05.2016 / 19:35
0

Follow a SOen response :

$query = "INSERT INTO test (col1, col2) VALUES (?,?); SELECT SCOPE_IDENTITY()";
$resource=sqlsrv_query($conn, $query, $arrParams); 
sqlsrv_next_result($resource); 
sqlsrv_fetch($resource); 
echo sqlsrv_get_field($resource, 0); 

I do not use SQL Server with PHP, so I can not prove its effectiveness, but I believe it works correctly because it was the answer chosen by the question author.

    
02.03.2015 / 13:28