Azure Stored Procedure does not return inserted ID to ASP.NET App
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a stored procedure defined on Azure SQL Database Server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
RETURN SCOPE_IDENTITY();
END
Called from ASP.NET Application through a data adapter.
- Command Type : Stored Procedure
- Execution Mode : NonQuery
- Modifier : Public
- Parameters :
@RETURN_VALUE, ColumnName = id, Direction = ReturnValue, SourceColumn = id
@finished, ColumnName = finished, Direction = Input, SourceColumn = finished
@createUser, ColumnName = createUser, Direction = Input, SourceColumn = createUser
If the stored procedure is called from DBMS, a correct id is being returned.
If the stored procedure is called from ASP.NET web application, return value is suddenly 0 or null.
What should I do to get the same result in ASP.NET?
c# asp.net
sql-server 
azure-sql-database edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
add a comment |
I have a stored procedure defined on Azure SQL Database Server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
RETURN SCOPE_IDENTITY();
END
Called from ASP.NET Application through a data adapter.
- Command Type : Stored Procedure
- Execution Mode : NonQuery
- Modifier : Public
- Parameters :
@RETURN_VALUE, ColumnName = id, Direction = ReturnValue, SourceColumn = id
@finished, ColumnName = finished, Direction = Input, SourceColumn = finished
@createUser, ColumnName = createUser, Direction = Input, SourceColumn = createUser
If the stored procedure is called from DBMS, a correct id is being returned.
If the stored procedure is called from ASP.NET web application, return value is suddenly 0 or null.
What should I do to get the same result in ASP.NET?
c# asp.net
sql-server azure-sql-database edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
Since your stored procedure is returning something (the newly inserted Id), you must call is usingExecuteScalar- notExecuteNonQuery....
– marc_s
Nov 24 '18 at 20:40
@marc_s thank you for your suggestion. So I changed it toExecuteScalarbut now my return value is an Object which is always null. Do you have any suggestion?
– Hugo Vrana
Nov 24 '18 at 20:54
Parameter direction ReturnValue is unrelated to whether you useExecuteScalarorExecuteNonQuerybecause it is not part of the result set. Show your code. Also consider usingOUTPUTin the query instead ofRETURNwhich is meant to communicate status, not data.
– Crowcoder
Nov 24 '18 at 21:54
add a comment |
I have a stored procedure defined on Azure SQL Database Server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
RETURN SCOPE_IDENTITY();
END
Called from ASP.NET Application through a data adapter.
- Command Type : Stored Procedure
- Execution Mode : NonQuery
- Modifier : Public
- Parameters :
@RETURN_VALUE, ColumnName = id, Direction = ReturnValue, SourceColumn = id
@finished, ColumnName = finished, Direction = Input, SourceColumn = finished
@createUser, ColumnName = createUser, Direction = Input, SourceColumn = createUser
If the stored procedure is called from DBMS, a correct id is being returned.
If the stored procedure is called from ASP.NET web application, return value is suddenly 0 or null.
What should I do to get the same result in ASP.NET?
c# asp.net
sql-server azure-sql-database edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
I have a stored procedure defined on Azure SQL Database Server.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
RETURN SCOPE_IDENTITY();
END
Called from ASP.NET Application through a data adapter.
- Command Type : Stored Procedure
- Execution Mode : NonQuery
- Modifier : Public
- Parameters :
@RETURN_VALUE, ColumnName = id, Direction = ReturnValue, SourceColumn = id
@finished, ColumnName = finished, Direction = Input, SourceColumn = finished
@createUser, ColumnName = createUser, Direction = Input, SourceColumn = createUser
If the stored procedure is called from DBMS, a correct id is being returned.
If the stored procedure is called from ASP.NET web application, return value is suddenly 0 or null.
What should I do to get the same result in ASP.NET?
c# asp.net
sql-server azure-sql-database c# asp.net
sql-server azure-sql-database edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
edited Nov 24 '18 at 23:56
David Browne - Microsoft
17.8k2827
17.8k2827
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
asked Nov 24 '18 at 20:28
Hugo VranaHugo Vrana
147
147
Since your stored procedure is returning something (the newly inserted Id), you must call is usingExecuteScalar- notExecuteNonQuery....
– marc_s
Nov 24 '18 at 20:40
@marc_s thank you for your suggestion. So I changed it toExecuteScalarbut now my return value is an Object which is always null. Do you have any suggestion?
– Hugo Vrana
Nov 24 '18 at 20:54
Parameter direction ReturnValue is unrelated to whether you useExecuteScalarorExecuteNonQuerybecause it is not part of the result set. Show your code. Also consider usingOUTPUTin the query instead ofRETURNwhich is meant to communicate status, not data.
– Crowcoder
Nov 24 '18 at 21:54
add a comment |
Since your stored procedure is returning something (the newly inserted Id), you must call is usingExecuteScalar- notExecuteNonQuery....
– marc_s
Nov 24 '18 at 20:40
@marc_s thank you for your suggestion. So I changed it toExecuteScalarbut now my return value is an Object which is always null. Do you have any suggestion?
– Hugo Vrana
Nov 24 '18 at 20:54
Parameter direction ReturnValue is unrelated to whether you useExecuteScalarorExecuteNonQuerybecause it is not part of the result set. Show your code. Also consider usingOUTPUTin the query instead ofRETURNwhich is meant to communicate status, not data.
– Crowcoder
Nov 24 '18 at 21:54
Since your stored procedure is returning something (the newly inserted Id), you must call is using
ExecuteScalar - not ExecuteNonQuery ....– marc_s
Nov 24 '18 at 20:40
Since your stored procedure is returning something (the newly inserted Id), you must call is using
ExecuteScalar - not ExecuteNonQuery ....– marc_s
Nov 24 '18 at 20:40
@marc_s thank you for your suggestion. So I changed it to
ExecuteScalar but now my return value is an Object which is always null. Do you have any suggestion?– Hugo Vrana
Nov 24 '18 at 20:54
@marc_s thank you for your suggestion. So I changed it to
ExecuteScalar but now my return value is an Object which is always null. Do you have any suggestion?– Hugo Vrana
Nov 24 '18 at 20:54
Parameter direction ReturnValue is unrelated to whether you use
ExecuteScalar or ExecuteNonQuery because it is not part of the result set. Show your code. Also consider using OUTPUT in the query instead of RETURN which is meant to communicate status, not data.– Crowcoder
Nov 24 '18 at 21:54
Parameter direction ReturnValue is unrelated to whether you use
ExecuteScalar or ExecuteNonQuery because it is not part of the result set. Show your code. Also consider using OUTPUT in the query instead of RETURN which is meant to communicate status, not data.– Crowcoder
Nov 24 '18 at 21:54
add a comment |
1 Answer
1
active
oldest
votes
Not sure without seeing the calling code, but you should never return data using the return value of a stored procedure. That's intended to indicate success or failure, and only when called from TSQL. Instead return data using an output parameter or a resultset. So
CREATE OR ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
SELECT SCOPE_IDENTITY() Id;
END
And then from client code retrieve the new ID with ExecuteScalar(), or ExecuteReader().
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462093%2fazure-stored-procedure-does-not-return-inserted-id-to-asp-net-app%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Not sure without seeing the calling code, but you should never return data using the return value of a stored procedure. That's intended to indicate success or failure, and only when called from TSQL. Instead return data using an output parameter or a resultset. So
CREATE OR ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
SELECT SCOPE_IDENTITY() Id;
END
And then from client code retrieve the new ID with ExecuteScalar(), or ExecuteReader().
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
add a comment |
Not sure without seeing the calling code, but you should never return data using the return value of a stored procedure. That's intended to indicate success or failure, and only when called from TSQL. Instead return data using an output parameter or a resultset. So
CREATE OR ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
SELECT SCOPE_IDENTITY() Id;
END
And then from client code retrieve the new ID with ExecuteScalar(), or ExecuteReader().
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
add a comment |
Not sure without seeing the calling code, but you should never return data using the return value of a stored procedure. That's intended to indicate success or failure, and only when called from TSQL. Instead return data using an output parameter or a resultset. So
CREATE OR ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
SELECT SCOPE_IDENTITY() Id;
END
And then from client code retrieve the new ID with ExecuteScalar(), or ExecuteReader().
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
Not sure without seeing the calling code, but you should never return data using the return value of a stored procedure. That's intended to indicate success or failure, and only when called from TSQL. Instead return data using an output parameter or a resultset. So
CREATE OR ALTER PROCEDURE [dbo].[InsertGameSession]
(@finished BIT,
@createUser VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[game_Session]
([finished]
,[createUser]
,[createDate]
,[changeUser]
,[changeDate])
VALUES
(@finished
,@createUser
,CURRENT_TIMESTAMP
,@createUser
,CURRENT_TIMESTAMP)
SELECT SCOPE_IDENTITY() Id;
END
And then from client code retrieve the new ID with ExecuteScalar(), or ExecuteReader().
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
answered Nov 24 '18 at 23:56
David Browne - MicrosoftDavid Browne - Microsoft
17.8k2827
17.8k2827
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462093%2fazure-stored-procedure-does-not-return-inserted-id-to-asp-net-app%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Since your stored procedure is returning something (the newly inserted Id), you must call is using
ExecuteScalar- notExecuteNonQuery....– marc_s
Nov 24 '18 at 20:40
@marc_s thank you for your suggestion. So I changed it to
ExecuteScalarbut now my return value is an Object which is always null. Do you have any suggestion?– Hugo Vrana
Nov 24 '18 at 20:54
Parameter direction ReturnValue is unrelated to whether you use
ExecuteScalarorExecuteNonQuerybecause it is not part of the result set. Show your code. Also consider usingOUTPUTin the query instead ofRETURNwhich is meant to communicate status, not data.– Crowcoder
Nov 24 '18 at 21:54