0

I am trying to fetch the last inserted ID using the below query in PHP with the help of sqlsrv_query(), but it returns me the error:

The active result for the query contains no fields.

Here's my code:

$query = "
    insert into saccess(NAME,LOGINID,PASSWORD,USERTYPE,CREATEDON,CREATEDBY) 
    values('name','user','PASSWORD','USERTYPE',GETDATE(),3); 
    SELECT SCOPE_IDENTITY() AS LASTID;
";

$result = sqlsrv_query($conn,$query);
echo "Rows affected: ".sqlsrv_rows_affected($result)."<br>";

$next_result = sqlsrv_next_result($result);
echo "NEXT RESULT: ".$next_result."<br>";

if( $next_result ) {
   echo "inside next result.<br>";
   print_r(sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC));
} 
elseif( is_null($next_result)) {
   echo "No more results.<br />";
}
print_r(sqlsrv_errors());

The above query works fine in MSSQL, but sqlsrv_query() seems to be only executing the first.

Here's the output of the code:

Rows affected: 1
NEXT RESULT: 1
inside next result.
Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -28 [code] => -28 [2] => The active result for the query contains no fields. [message] => The active result for the query contains no fields. ) )

Please help me out in pointing out what is wrong.

Update:

Definition:

description Data

Data:

Description

Zhorov
  • 28,486
  • 6
  • 27
  • 52
Steve
  • 522
  • 3
  • 12
  • 28
  • *Hm....*, would the semi-colon `GETDATE(),3);` < there be at fault? It's an end of statement character in PHP, far as I know. I could be wrong though. Have you seen [this question?](https://stackoverflow.com/q/1920558/1415724) – Funk Forty Niner Oct 20 '20 at 13:33
  • @FunkFortyNiner, I have tried without the `semicolon`, but still the same result. Yes I have seen that question and it doesn't help me out. – Steve Oct 20 '20 at 13:39
  • 3
    `SET NOCOUNT ON ` is a possible solution. As similar [question](https://stackoverflow.com/questions/61174842/correct-way-location-to-use-scope-identity/61202479#61202479). – Zhorov Oct 20 '20 at 13:42
  • 1
    It looks like you have 2 statements in 1 query. Separate them out and run them separately. Most of the SQL APIs don't like multiple statements in one query. – aynber Oct 20 '20 at 13:45
  • @aynber Funny you should mention the multi query; I thought of that originally earlier but didn't mention that; fair point. – Funk Forty Niner Oct 20 '20 at 13:47
  • Thought the same but then read https://www.php.net/sqlsrv_next_result which suggests it's okay. – Jonnix Oct 20 '20 at 13:54
  • 1
    @Zhorov, Thanks, works fine. I have upvoted your answer. – Steve Oct 20 '20 at 14:04
  • But @Zhorov, what if I want to get number of affected rows, because on SO, I have seen many questions where people have done the same above stuff without using `SET NOCOUNT ON`, and with using other functions like `sqlsrv_next_result` ,`sqlsrv_fetch`. If possible, can you provide some explanation.? – Steve Oct 20 '20 at 14:14
  • @Zhorov Like this question https://stackoverflow.com/questions/6850375/retrieve-the-id-of-an-inserted-record-php-ms-sql-server – Steve Oct 20 '20 at 14:15
  • @Steve To be honest, your code seems to be correct and it follows the examples in the [documentation](https://learn.microsoft.com/en-us/sql/connect/php/sqlsrv-next-result?view=sql-server-ver15). I've made a test script , based on your example, and it works as expected (returns the count of the affected rows and last inserted id). Can you post the table definition and some real data? Thanks. – Zhorov Oct 20 '20 at 14:42
  • @Zhorov, I have uploaded image for definition and data of the table I am using. – Steve Oct 20 '20 at 15:33
  • @Steve I tested again and the code is correct. It's probably another set of data, but you need try to make a test and execute this code after the `sqlsrv_query()` call: `$data = array(); do {echo "Another result set"."
    "; while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {$data[] = $row;}} while (sqlsrv_next_result($result)); echo print_r($data);`.
    – Zhorov Oct 21 '20 at 06:54
  • @Steve A known [issue](https://github.com/Microsoft/msphpsql/issues/581), which looks similar. – Zhorov Oct 21 '20 at 17:34

0 Answers0