Problem retrieving inserted id

Asked

Viewed 335 times

6

I created a register with some 58 data.

As I was creating, I was testing and the data was entering the BD, and every time an insertion was made, I took the id of the last data set/group inserted.

Only if I "give one refresh on the page" when I will make a new registration, the data are no longer registered in the BD, it is as if the id of the last record was lost because I put a echo to show the id created and now see no more.

The mistake 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 going on?

$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()));
}
php sql-server

  • Tip: Is there by any chance Rigger in this process? This can influence the return, and confirm that for the return is used the command @id_Transacao = SCOPE_IDENTITY(), who takes the last insert of the main selection, not of another section.

  • But you do the SELECT before to get the last ID inserted ? I only see INSERT in your code. But you can follow the suggestion of the Kaduamaral.

2 answers

1

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

In most cases its use is more recommended compared to SCOPE_IDENTITY().

See example below.

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

When executing the Insert, id will already be returned by clause OUTPUT:

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

More information here: https://msdn.microsoft.com/pt-br/library/ms177564.aspx

0

Follows a reply from Soen:

$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 don’t use SQL Server with PHP, so I can’t prove its effectiveness, but I believe it works correctly, because it was the answer chosen by the author of the question.

Browser other questions tagged php sql-server

You are not signed in. Login or sign up in order to post.